Does anyone have any advice on using the SQL Send adapter and handling transactions inside your SQL 2005 stored procedures?
I've executing a stored procedure using TRY / CATCH blocks with BEGIN TRANSACTION, COMMIT TRANSACTION and ROLLBACK TRANSACTION statements in it. The stored procedure seems to work fine provided it reaches the commit. However one one occasion an exception occurred and the rollback was issues. This caused an error my application event log…
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
I now seem to get this error every time any stored procedure with transaction handling in it executes.
I've searched on the internet for this error and have found a few forum entries which seem to recommend removing the transaction handling from the stored procedures, making sure they fail with an error serverity of above 10 in the event of an exception and leave the rest to BizTalk. Is this the best way? If so, why does BizTalk to this. Is BizTalk 2006 not designed to work with SQL Server?