New Posts New Posts RSS Feed: 6.0.6 fix for UseDTC/Transaction Scope and calling stored procedures
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

6.0.6 fix for UseDTC/Transaction Scope and calling stored procedures
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Topic: 6.0.6 fix for UseDTC/Transaction Scope and calling stored procedures
    Posted: 21-Oct-2010 at 11:54am

I just wanted to add that DevForce 2010 doesn't itself write any SQL at all, so the SQL generated for the transaction is coming either from EF or from the database provider.   Glad that you found the problem.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 21-Oct-2010 at 11:27am
It appears that we're only getting errors on Stored Procedures that themselves have some kind of error but those errors don't show up when executing without the added BEGIN TRANS..COMMIT.
 
My guess is the SPs are somehow obscuring the error (the SP calls an SP which calls several other SPs so it's not obvious where it happens) but when wrapped inside an extra transaction, the last COMMIT is just failing as there must be a ROLLBACK taking place at some point in the chain and now it doesn't have a corresponding BEGIN TRANS.
 
So this is not a DevForce/EF issue.  SPs just need to work :-) and I guess we'll start testing them with a BEGIN TRANS..COMMIT around them to be sure they'll work when called from DevForce.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Posted: 20-Oct-2010 at 6:47pm
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);

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down