Home » SQL & PL/SQL » Client Tools » How to insert data from Sql server to oracle server. (Oracle database 10g)
How to insert data from Sql server to oracle server. [message #572606] Thu, 13 December 2012 21:35 Go to next message
dophuong_cs
Messages: 90
Registered: May 2011
Location: Viet Nam
Member

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.

However when i insert data from SQl server to Oracle Server not success.
select * from OPENQUERY (QVHKTEST, 'SELECT * FROM QVSYSTEM')

After i run above script, result is OK
With: "QVHKTEST" is alias of Link server from SQL to Oracle server
: "QVSYSTEM" is a table on Oracle database, that table we want to get through Linkserver on SQL server

Both Server Database contain same name table is "QVSYSTEM"


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

If i run script above directly in SQL Window query can insert OK.
This is code in trigger at table on SQL server:
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 QVSYSTEM')
VALUES (@body_no,@merchandise,@model_name,@line_name,@date_entry);	  
  

END


But when i make trigger after insert on table in SQL Server to insert data From SQL server to Oracle server, however not success and SQL server 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.


I don't know how to configure them, please help me to solve this problem.
Thank you very much.

[Updated on: Thu, 13 December 2012 21:39]

Report message to a moderator

Re: How to insert data from Sql server to oracle server. [message #572607 is a reply to message #572606] Thu, 13 December 2012 21:49 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
>Make Linkserver from SQL server to Oracle database
I have know nothing about Linkserver, but your posted code does not appear reasonable to me.

below would be valid SQL for an Oracle only statement

insert into table qvsystem@remote_db SELECT BODY_NO,MERCHANDISE, MODEL_NAME,LINE_NAME, DATE_ENTRY FROM QVSYSTEM;
above "pushes" data from local system to remote system
below "pulls" data from remote system to the local system
insert into table qvsystem SELECT BODY_NO,MERCHANDISE, MODEL_NAME,LINE_NAME, DATE_ENTRY FROM QVSYSTEM@RMOTE_DB;

INSERT on Oracle never uses both VALUES & SELECT on the same INSERT statement
Re: How to insert data from Sql server to oracle server. [message #572614 is a reply to message #572607] Fri, 14 December 2012 00:33 Go to previous messageGo to next message
dophuong_cs
Messages: 90
Registered: May 2011
Location: Viet Nam
Member

Thanks BlackSwan for your comment.
I have SQL server is Local Server, and Oracle Server is Remote Server.
Your code use to insert data through Oracle_database_link from Oracle Server Remote to other Oracle server .
However I From SQl server and want to pushes data from local server to remote server.
So syntax insert data from SQL to ORacle is difference with your code.

[Updated on: Fri, 14 December 2012 00:40]

Report message to a moderator

Re: How to insert data from Sql server to oracle server. [message #572616 is a reply to message #572614] Fri, 14 December 2012 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is more a SQL Server question and you should post it in a SQL Server forum.

Regards
Michel
Re: How to insert data from Sql server to oracle server. [message #572622 is a reply to message #572616] Fri, 14 December 2012 01:45 Go to previous messageGo to next message
dophuong_cs
Messages: 90
Registered: May 2011
Location: Viet Nam
Member

Thank for your comment, however i think that, this problem can related with make link from SQL server to Oracle server, so I posted this problem to this topic.
Please help me.
Re: How to insert data from Sql server to oracle server. [message #572624 is a reply to message #572622] Fri, 14 December 2012 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As it is FROM SQL Server TO Oracle, you have to do something IN SQL Server, NOT in Oracle.

Regards
Michel
Re: How to insert data from Sql server to oracle server. [message #572627 is a reply to message #572624] Fri, 14 December 2012 02:03 Go to previous messageGo to next message
dophuong_cs
Messages: 90
Registered: May 2011
Location: Viet Nam
Member

Yes, i can do something at SQL server, however i want to receive support from others member have a lot of experience about SQL server or Oracle server, maybe they already face this problem, so they can support me.
I can insert success if i write code in store procedure, however with trigger then fail.
Re: How to insert data from Sql server to oracle server. [message #572629 is a reply to message #572627] Fri, 14 December 2012 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can insert success if i write code in store procedure, however with trigger then fail.


But this is SQL Server code and we are an ORACLE forum.
We can give you the code in PL/SQL if you want.

Regards
Michel
Re: How to insert data from Sql server to oracle server. [message #573108 is a reply to message #572629] Thu, 20 December 2012 20:15 Go to previous message
Flyby
Messages: 143
Registered: March 2011
Location: Belgium
Senior Member
It's a fair question as it involves oracle somehow. Good that you can already use select on the linked server.
For updates
Is the Oracle MTS-service running on your windows sql server?
Have you checked the Microsoft Distributed Transaction configuration and MDT config 2
edit: Found some oracle documentation on the MTS-service

[Updated on: Thu, 20 December 2012 20:40]

Report message to a moderator

Previous Topic: Exporting Result to Text file through Query
Next Topic: migration from windows-mssql to linux-oracle.
Goto Forum:
  


Current Time: Tue Jul 29 20:49:33 CDT 2014

Total time taken to generate the page: 0.07768 seconds