Geek Answers Handbook

Call procedure on a linked server without a distributed transaction

I have a SQL Server 2005 procedure that needs to be modified to call a procedure on a linked server. My local procedure is called by several other procedures, always as part of a transaction.

If I add a call to the linked server, then at runtime I get a message that the OLE DB provider was unable to start a distributed transaction.

This is fine with me: as far as I know, a remote procedure does not make any changes to the database. I do not need it to be inside the transaction and therefore do not need a distributed transaction.

But I suspect that I do not have such an option, that there is no way to disable the transaction extension on the linked server. Is it correct?

(I could solve this problem by refactoring these procedures so that the linked server call is executed in the parent procedure outside of all transactions. But logically, it refers to this child procedure, so I would like to save it there.)

Thank you all.

+4
source share
1 answer

It turns out that if you are on SQL Server 2008 or higher, you have a server option that controls this:

EXEC sp_serveroption 'servername', 'remote proc transaction promotion', 'false'

(see sp_serveroption (Transact-SQL) ).

SQL Server 2005, . , MSDTC , , . .

+8

More articles:


All Articles