Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to insert from SQL server to Oracle server.

Author  Topic 

dophuong_cs
Starting Member

15 Posts

Posted - 2012-12-14 : 03:52:34
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

52326 Posts

Posted - 2012-12-14 : 04:01:15
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

15 Posts

Posted - 2012-12-14 : 04:06:35
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

52326 Posts

Posted - 2012-12-14 : 04:13:00
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

15 Posts

Posted - 2012-12-14 : 04:27:37
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-14 : 04:42:09
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

15 Posts

Posted - 2012-12-15 : 00:01:56
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

15 Posts

Posted - 2012-12-16 : 01:17:58
Nobody know about this problem.
Please help me to solve it.

Phuong
Go to Top of Page

TajKazi
Posting Yak Master

101 Posts

Posted - 2016-08-29 : 06:50:42
hi dophuong_cs have you got answer
Go to Top of Page
   

- Advertisement -