Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sharing a transaction between connections Expand / Collapse
Author
Message
Posted Tuesday, August 24, 2010 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 9:24 AM
Points: 6, Visits: 60
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.
Post #974175
Posted Friday, October 25, 2013 2:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 26, 2013 2:56 AM
Points: 17, Visits: 36
This is an old thread, but I am wondering if you ever came up with a solution? Did you end up using sp_bindsession?


Post #1508597
Posted Friday, October 25, 2013 3:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 29, 2013 9:24 AM
Points: 6, Visits: 60
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.
Post #1508612
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse