I have a SQL Server 2005 procedure that needs to be modified to call a procedure that's on a linked server. My local procedure is called by several other procedures, always within a transaction.
If I add the call to the linked server, then at run time I get a message saying that the OLE DB provider was unable to begin a distributed transaction.
That's fine with me: as far as I know, the remote procedure doesn't do any database changes. I don't need it to be inside the transaction and therefore don't need a distributed transaction.
But I suspect that I don't have that option, that there's no way to turn off the extension of the transaction to the linked server. Is this correct?
(I could get around the problem by refactoring these procedures so that the call to the linked server takes place in a parent procedure, outside all transactions. But logically it belongs within this child procedure, so I'd like to keep it there.)
Thanks, all.
Best Answer
It turns out that, if you are on SQL Server 2008 or above, you have a server option that controls this:
(See sp_serveroption (Transact-SQL)).
But I was on SQL Server 2005, which doesn't have that option. So I investigated further and discovered that MSDTC had network transactions disabled on the machine I was using, which was why the remote transaction was failing. I was able to get this enabled by a DBA.