August 22, 2005 at 10:58 am
(using VB.NET to connect to SQL Server 2000)
i begin a transaction.
run a query that times out.
the code then tries to rollback the transaction.
upon doing so, SQL Server throws the following exception:
"This SqlTransaction has completed; it is no longer usable."
if the query does not time out, it will commit successfully.
our code been working perfectly up to this point. and nowhere do we commit or rollback the transaction between the timeout and the rollback. any ideas?
Thanks for the help!
There are ll types of people: those who understand Roman Numerals, and those who do not.
August 22, 2005 at 5:03 pm
solution (?): it looks as if when a timeout occurs, SQL Server 2000 will automatically rollback any open transaction. so when the code tried to rollback the transaction, it produced that error...interesting little quirk
There are ll types of people: those who understand Roman Numerals, and those who do not.
August 23, 2005 at 9:10 am
SQL server does NOT rollback a transaction if it's timeout.
It's client side responsiblity to roll it back if time is expired.
Check your code, it may be rolled back or committed somewhere.
Or check the ado.net connection property. I don't know it has a property like "automatically rollback transactions when time out" or not.
August 23, 2005 at 9:15 am
Found it.
If you set XACT_ABORT ON, when time out, the trasaction is rolled back.
If you set XACT_ABORT OFF, when time out, the trasaction is not rolled back.
When transactions are controlled by BEGIN/COMMIT/ROLLBACK TRAN, it's not best practice to set XACT_ABORT ON
August 23, 2005 at 4:42 pm
thanks for the replies guys. how/where do i find out if "XACT_ABORT" is ON or OFF?
There are ll types of people: those who understand Roman Numerals, and those who do not.
August 24, 2005 at 6:46 am
XACT_ABORT is a run time setting. Check the SP you are calling whether it has SET XACT_ABORT ON.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply