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.