August 24, 2010 at 8:40 am
From T-SQL, I am calling a CLR stored procedure that is just a wrapper for calling a web service. This web service makes connections back to the database to do some processing. My problem is that if the original connection has an open transaction and the web services accesses data locked by that transaction, it hangs. I would like a way for the web service to be able to use the same transaction as the original connection.
My research led me to sp_getbindtoken and sp_bindsession, which looked to be exactly what I was after. Unfortunately, since the original connection is what is initiating the other connection, it is still in the runnable state and the web service cannot bind to that session because the session is still in use ("Transaction context in use by another session."). Is there any way around this?
BOL also says that sp_bindsession is deprecated and I should use MARS or DT instead, but I don't think either of those apply (please correct me if I'm wrong). MARS is about multiple queries from the same connection (I need multiple connections on the same transaction), and the DT docs explicitly say they won't do what I'm after:
The sessions involved in Transact-SQL distributed transactions do not get a transaction object they can pass to another session for it to explicitly enlist in the distributed transaction. The only way for a remote server to enlist in the transaction is to be the target of a distributed query or a remote stored procedure call.
Any suggestions would be appreciated.
October 25, 2013 at 2:48 pm
This is an old thread, but I am wondering if you ever came up with a solution? Did you end up using sp_bindsession?
October 25, 2013 at 3:22 pm
Afraid not. My recollection is I just worked around it somehow.
If anything else comes to mind, I'll update, but I wouldn't hold your breath. Even though I'm doing substantially less SQL work these days, I'd be interested if there is a solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply