SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to insert from SQL server to Oracle server.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dophuong_cs
Starting Member

Vietnam
10 Posts

Posted - 12/14/2012 :  03:52:34  Show Profile  Reply with Quote
Dear all,
I have problem: I have 1 sql server already setup SQL Server 2012 Express and 1 Oracle Database server 10g. Now i want to insert data from SQL server to Oracle database through link server.
Some step i already make:
1. Setup oracle database 10g and configure listener (Finished)
2. Setup Sql server 2012 express on Windows 7 (Finished)
3. Setup ODTwithODAC1020221 on PC already setup SQL server (Finished)
4. Make Linkserver from SQL server to Oracle database (Finished), and can select data from Oracle Database on SQL server through Linkserver.

I have one table QVYSTEM on SQL Server and table Test on Oracle server.Now, i want to insert data from table QVSYSTEM to table TEST, but not success.

select * from OPENQUERY (QVHKTEST, 'SELECT * FROM Test)


After i run above script, result is OK


INSERT OPENQUERY (QVHKTEST, 'SELECT     BODY_NO,
                                    MERCHANDISE,
			          MODEL_NAME,
				  LINE_NAME,
				  DATE_ENTRY
			    FROM Test)
values('VNF4619829','3227B002CA','L1068','01','2012/09/26 03:18:11');


If i run script above directly in SQL Window query can insert OK.

However i create trigger after insert on table QVSYSTEM to Insert data to table Test when 1 record inserted on table QVSYSTEM.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author Name: Phuong Do Minh >
-- Create date: <Create Date: 10/12/2012>
-- Description:	<Description: After data insert into table qvsystem on SQL server
--							  This trigger will fire and insert that data into table qvsystem
--							  On Oracle Server	>
-- =============================================
ALTER TRIGGER [dbo].[TRI_INSERT_QVSYSTEM_AT_ORACLE]
   ON  [dbo].[qvsystem] 
   AFTER  INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--SET XACT_ABORT ON;

	  declare @body_no varchar(100);
	  declare @merchandise varchar(100);
	  declare @model_name varchar(100);
	  declare @line_name varchar(100);
	  declare @date_entry varchar(100);
	  ------------------------------
	  -- Lay du lieu cua dong vua moi insert duoc trong bang qvsystem

	  select @body_no=i.body_no from  inserted i;
	  select @merchandise=i.merchandise from  inserted i;
	  select @model_name=i.model_name from  inserted i;
	  select @line_name=i.line_name from  inserted i;
	  select @date_entry=i.date_entry from  inserted i;
	  ---------------------------------
INSERT OPENQUERY (QVHKTEST, 'SELECT     BODY_NO,
                                    MERCHANDISE,
			          MODEL_NAME,
				  LINE_NAME,
				  DATE_ENTRY
			    FROM Test)
VALUES (@body_no,@merchandise,@model_name,@line_name,@date_entry);	  
  

END



But script trigger above run not success and raise error below:

OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure Insert_data, Line 16
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" was unable to begin a distributed transaction.


please help me to solve this problem.
Thank you very much.

Phuong

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/14/2012 :  04:01:15  Show Profile  Reply with Quote
why should you do this from trigger? does it have to be syncronous? why not add this as an automated job in sql server do insertion as per fixed regular schedule?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dophuong_cs
Starting Member

Vietnam
10 Posts

Posted - 12/14/2012 :  04:06:35  Show Profile  Reply with Quote
Thank visakh16 for your comment.
However i use Sql Express 2012, so not support make schedule to run procedure automatically, therefor i must write code in trigger after insert.

Phuong
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/14/2012 :  04:13:00  Show Profile  Reply with Quote
quote:
Originally posted by dophuong_cs

Thank visakh16 for your comment.
However i use Sql Express 2012, so not support make schedule to run procedure automatically, therefor i must write code in trigger after insert.

Phuong


Your trigger has an existing issue as its not written for batch operations.
Please be aware that inserted may contain multiple records in case of batch operation so better to write trigger like below for batch inserts



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author Name: Phuong Do Minh >
-- Create date: <Create Date: 10/12/2012>
-- Description:	<Description: After data insert into table qvsystem on SQL server
--							  This trigger will fire and insert that data into table qvsystem
--							  On Oracle Server	>
-- =============================================
ALTER TRIGGER [dbo].[TRI_INSERT_QVSYSTEM_AT_ORACLE]
   ON  [dbo].[qvsystem] 
   AFTER  INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	--SET XACT_ABORT ON;
  	  
INSERT OPENQUERY (QVHKTEST, 'SELECT     BODY_NO,
                                    MERCHANDISE,
			          MODEL_NAME,
				  LINE_NAME,
				  DATE_ENTRY
			    FROM Test)
select i.body_no,
                 i.merchandise,
                 i.model_name, 
                 i.line_name,
                 i.date_entry 
          from  inserted i;  

END





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dophuong_cs
Starting Member

Vietnam
10 Posts

Posted - 12/14/2012 :  04:27:37  Show Profile  Reply with Quote
Thanks, your way is very good.
However problem still appear, and how to solve this problem.


OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure TRI_INSERT_QVSYSTEM_AT_ORACLE, Line 34
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "QVHKTEST" was unable to begin a distributed transaction.

Phuong

Edited by - dophuong_cs on 12/14/2012 04:29:50
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/14/2012 :  04:42:09  Show Profile  Reply with Quote
see
http://msdn.microsoft.com/en-us/library/windows/desktop/ms686976(v=vs.85).aspx


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dophuong_cs
Starting Member

Vietnam
10 Posts

Posted - 12/15/2012 :  00:01:56  Show Profile  Reply with Quote
Thank visakh16 for kindly support me.
I already try your way and configure registry, how ever trouble still happened.
Please help me to solve it.

Phuong
Go to Top of Page

dophuong_cs
Starting Member

Vietnam
10 Posts

Posted - 12/16/2012 :  01:17:58  Show Profile  Reply with Quote
Nobody know about this problem.
Please help me to solve it.

Phuong
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000