Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pootle_flump
1064 Posts |
 Posted - 2009-08-21 : 03:13:48
|
HiSQL Server 2008 (But there is no ".NET Inside SQL Server (2008)" forum...)Visual Studio 2008C#First time calling a CLR sproc from a T-SQL sproc. Briefly, my T-SQL sproc begins a transaction, makes some changes to a table and calls the CLR sproc. I get the below .NET error as the CLR sproc attempts to open a connection:quote:
Transaction context in use by another session
Based on some reading(http://blogs.msdn.com/asiatech/archive/2009/08/10/system-transaction-may-fail-in-multiple-thread-environment.aspxhttp://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/966b6a4d-fafc-407b-8d31-f1731ff6edf8)As I read it, can't have two threads sharing the same transaction context.I have added this to my CLR connection string:...Enlist=false; This just opens up a new transaction context for the CLR. Or I can move the call to the CLR sproc outside of the T-SQL transaction. The code no longer fails, but my tasks are no longer atomic.The solutions are a hack - anyone know another method, ideally keeping everything in a single transaction?A better option to the two above I think is to shift the T-SQL data manipulation in to the CLR sproc - I just liked having the CLR sproc scoped only to the stuff it could do better than T-SQL.Many thanks for reading & hopefully helping me out peeps  EDIT - BTW I know T-SQL is better for 99.999% of data manipulation. This is one of those 0.001% of circumstances. |
|
|
pootle_flump
1064 Posts |
Posted - 2009-08-21 : 11:26:02
|
| Solved this, and a few other problems, using context connection:http://msdn.microsoft.com/en-us/library/ms345135(SQL.90).aspx#mandataac_topic5Wish I could use MARS with it, but one can't have it all. |
 |
|
|
|
|
|
Calling CLR Proc from within T-SQL Transaction