4

My Linked server is setup correctly , I am able to perform below query.

INSERT INTO [RemoteServer].[Table] SELECT * FROM [LocalServer].[Table]

However when I do the same thing within transaction

BEGIN TRAN INSERT INTO [RemoteServer].[Table] SELECT * FROM [LocalServer].[Table] COMMIT TRAN

I get Errors like

OLE DB provider "SQLNCLI" for linked server "66.70.123.202" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 3 The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "66.70.123.202" was unable to begin a distributed transaction.

Zeus
  • 3,091
  • 6
  • 47
  • 60
  • See: http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0 – OMG Ponies Jun 13 '10 at 20:49
  • According this they are saying I should be able to PING Both servers from each other...but how can i do that ? They are on different domain. – Zeus Jun 13 '10 at 21:35
  • So at the end I am not doing my process in transaction. I am recovering my data inside try and catch block. – Zeus Jun 18 '10 at 20:17

1 Answers1

3

Check if MS DTC service is running on both servers and it is configured correctly to allow distribured queries.

Sergey Olontsev
  • 1,084
  • 7
  • 19
  • Yes MS DTC runs fine. I am able to query and insert data into remote server without begin transaction. I have "no auth. required" set and allowing all in and out traffic from MS DTC. – Zeus Jun 13 '10 at 21:04
  • Are your firewall configured to allow MS DTC? – Sergey Olontsev Jun 14 '10 at 08:52