I have an unmanaged C++ application that uses the COM ADOConnection object. The application makes use of the BeginTrans/CommitTrans methods on the object to control transaction behavior.
I recently added a CLR stored procedure to do some Regex stuff (and got some wicked fast processing, much better than pulling the data back to C++, processing, and then pushing back to the database). Testing the new proc in the SQL management studio worked great.
When I put the call to the new proc into the C++ app I discovered some very odd behavior. It appeared as though the new procedure wasn’t being executed at all.
I debugged application and would see the stored procedure being executed, both in the debugger and in a database trace.
I then found that if I turned off the calls to BeginTrans and CommitTrans the stored procedure executed properly.
A database trace with the ADOConnection transaction code turned on is as follows:
set implicit_transactions on
exec DoStuff
IF @@TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off
As a workaround I have left the ADOConnection transaction code turned off and I am performing the transaction manually.
I tried wrapping the CLR stored procedure in a non-CLR procedure but the error still occurred.
There are many other calls in the application to non-CLR stored procedures as well as dynamic SQL and they all work great.
Why don’t CLR stored procedures and ADOConnect play nice?