Menu
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.
212
Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.
Here is an example of how to call a stored procedure inside another stored procedure. This is also known as nested stored procedures in SQL Server. Step 1: Create two simple stored procedure to insert some data into two different tables. both accept four parameters to insert the data.
In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.
On the server where the trigger resides, you need to turn the MSDTC service on. You can this by clicking START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES. Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start. Save this answer.
It turns out that, if you are on SQL Server 2008 or above, you have a server option that controls this:
EXEC sp_serveroption 'servername', 'remote proc transaction promotion', 'false'
(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.
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With