SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sharing a transaction between connections


Sharing a transaction between connections

Author
Message
Andrew Flerchinger
Andrew Flerchinger
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
commhorizons
commhorizons
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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?



Andrew Flerchinger
Andrew Flerchinger
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search