Unanswered: How to read data from remote server inside a transaction?
I have a local transaction,
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:
Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
It looks like remote server is not available inside the local transaction. How to handle that?
1) make sure MSDTC is running, u can see that from SQl Service Manager.
2) add this code before 'begin tran' , SET XACT_ABORT ON
3) instead of 'begin tran' begin distributed tran'
4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
SET XACT_ABORT ON
begin distributed tran
insert into sometable select * from remoteserver.database.dbo..foo
SET XACT_ABORT OFF