After upgrading to 6.0.6, our calls to MS SQL 2008 stored procedures are now being wrapped in a transaction but not correctly. Not sure if this is DevForce or Entity Framework issue.
The SQL profile shows this kind of info when we call an SP from DevForce:
BEGIN TRANSACTION
EXEC p_any_procedure
ROLLBACK
That gets a SQL error (same thing actually happens if you did this in SQL management studio):
Msg 3930, Level 16, State 1, Procedure p_any_procedure, Line xx
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The correct way to code that is to use a TRY CATCH block and either COMMIT or ROLLBACK if you're going to wrap transactions around these calls (this example works in SQL management):
BEGIN TRANSACTION
BEGIN TRY
EXEC p_any_procedure
END TRY
BEGIN CATCH
-- at this time transaction is not committable
IF XACT_STATE() = -1
ROLLBACK
ELSE
COMMIT
END CATCH
Per your release notes, if we use the work-around to revert to the 6.05 behavior, it doesn't wrap a transaction/rollback and obviously works.
Note
that there are issues that Microsoft has documented regarding „incorrect‟ enlistment occurring in SQL Server 2005 (and fixed in SQL Server 2008). If this is occurring in your application, please contact IdeaBlade support.
Alternatively, you can also use the following line early in your application to revert to the DevForce v6.0.5 behavior. This will turn off any use of both .NET‟s TransactionScope and the MSDTC. This is not recommended except as a temporary workaround.
TransactionSettings.Default = new TransactionSettings(System.Transactions.IsolationLevel.ReadCommitted, new TimeSpan(0, 1, 0), false);