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 transaction between two connections? Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 4:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, October 26, 2013 2:56 AM
Points: 17, Visits: 36
This is a bit complicated so I will try to simplify it as much as possible.

On SQL Server 2008 I have created a CLR UDF (C#) that opens a connection via a Connection String and writes to a table.

However, I want the write generated by the UDF to be rolled back if the Transaction invoking the UDF gets Rolled Back.

I can't use "context connection=true;" as the UDF connection string because it needs a different connection string than the transaction that invoked it.

This will work using sp_getbindtoken/sp_bindsession. However, these apparently have been deprecated (although they are still in SQL Server 2012).

I also read up on MARS, but it seems like it would not work in this situation: As far as I can tell, MARS is for multiple batches on a single connection. I have two connections, each of which may have a different connection string, which need to share the same "transaction space".

Any suggestions would be greatly appreciated.



Post #1508621
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse