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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 INSERTAS 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 16The 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