Question

VJ_ANAND on Mon, 06 May 2013 09:52:53


Hi,

I have two DBs namely MS SQL 2008 and ORACLE 11g. I need to transfer data from MS SQL to ORACLE using the linked server. I did the following, 

I have a stored procedure in ORACLE as call_linked_oracle_procedure which needs to be called from the MS SQL server to transfer the data.

So the steps I did to achieve this is provided as a sample as follows,

/*  Creating a sample table  */

create table source_tab3 
(
id numeric,
name varchar(MAX)
)
GO
---------------------------------------------------------
/*  Creating a insert trigger for the sample table  which calls the oracle through the linked server*/


IF OBJECT_ID ('dbo.source_tab3Trigger','TR') IS NOT NULL
   DROP TRIGGER dbo.source_tab3Trigger;
GO
CREATE TRIGGER source_tab3Trigger ON source_tab3
AFTER INSERT
AS
DECLARE @output varchar(600)
EXEC [dbo].[call_linked_oracle_procedure] @parameter1 =N'value1', @parameter2 =N'value2'
select @output
GO
-----------------------------------------------------
/*  Insert a sample data  */

insert into source_tab3 (id,name) (1,'vijai');
GO

Now when I insert the data, It give the following error 

_____________________________________________________________________________________

OLE DB provider "OraOLEDB.Oracle" for linked server "LK_SERVER_NAME" returned message "New transaction cannot enlist in the specified transaction coordinator. ".

Msg 7391, Level 16, State 2, Procedure source_tab3Trigger, Line 11
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LK_ORACLE" was unable to begin a distributed transaction.

_____________________________________________________________________________________

The things I did to solve this were,

1.     Enabled the MSDTC services (Distributed Transaction Coordinator is started ).

2.     Enabled the Network DTC access from the control panel, component services to 'Allow InBound' etc. 

3.      My 'OracleMTSRecoveryService' is running in my services list.

Also, I have done all these where my machine where MS SQL 2008 is installed. Dono what needs to be done in the ORACLE server.

Also to note, that I am able to query the tables/procedure via linked server individually, but not able to do that within the triggers.

Any tip is much appreciated.

regards,

Vijai


Replies

VJ_ANAND on Tue, 07 May 2013 06:22:03


Hi,

I tried a few and found this working, please let me know the reason behind this even though it works,

IF OBJECT_ID ('dbo.source_tab3Trigger','TR') IS NOT NULL

   DROP TRIGGER dbo.source_tab3Trigger;

GO

CREATE TRIGGER source_tab3Trigger ON source_tab3

AFTER INSERT, UPDATE

AS

      commit transaction;

      set implicit_transactions on

      DECLARE     @output varchar(600)

      DECLARE     @p_1 varchar(600) = 'EASYLINK51_EBS_MECH'

      DECLARE     @p_2 varchar(600) = 'standard'

      DECLARE     @p_3 varchar(600) = 'EBS'

      EXEC [dbo].[call_linked_oracle_procedure] @parameter1 =N'value1', @parameter2 =N'value2'

      select @output

      set implicit_transactions off

      begin transaction;

     

GO

regards,

Vijai

Vien Lee on Fri, 21 Oct 2016 04:41:32


I have server A. I want insert data into server B by linked server.

I execute the following:

ALTER TRIGGER [dbo].[My_Trigger] 
   ON  [dbo].[MyTable]                      (table in server A)
   AFTER  INSERT,UPDATE,Delete
AS 

begin

     --some command

                      COMMIT TRANSACTION  

---insert data to server B via linked server.

                           EXEC dbo.my_procedure @para1,@para2;
                         BEGIN TRANSACTION

end

I test it in Microsoft sql manager may trigger work. but when i insert data in my application error:

 System.Data.SqlClient.SqlException: A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues) at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) 

My Application use entity framework to insert data. 

Please help me. Thanks.