Preventing automatic rollback of transactions

[(O)enone]

I am experiencing a problem with transaction handling in my SQL Server-based
application. To demonstrate the problem, here are some simple T-SQL
statements, which can be executed one by one in SQL Server Management
Studio:


\\\
-- Get the current transaction count, which is of courser zero
select @@trancount

-- Begin a transaction named "Test"
begin transaction Test

-- Get the transaction count -- this time it's 1, as expected
select @@trancount

-- Perform a SELECT statement that fails. This returns an error because it
can't cast 'Hello' as an integer.
select 'Hello'+1

-- Get the transaction count for a file time. This now returns zero.
select @@trancount
///


The SELECT statement is causing SQL Server to automatically roll back the
transaction. This is causing problems in my application, as the program
doesn't expect the transaction to have rolled back at this point. Upon
handling this error, the program then attempts to explicitly roll back the
transaction, which causes SQL Server to error for a second time, stating
that the ROLLBACK has no corresponding BEGIN TRANSACTION. I therefore lose
sight of the original error that actually occurred.

Is there a way to stop SQL Server from automatically rolling back
transactions? I really want the only thing that causes the transaction to
roll back to be an explicit ROLLBACK command.

I've tried SET ARITHABORT OFF and SET XACT_ABORT OFF, but neither made any
difference to the results of the steps above.

Can anyone enlighten me as to how I can get this to do what I want?

(Running against SQL Server 2000, but the same results are experienced in
SQL Server 2005).

Many thanks,

[unknown]

To my knowledge, compile errors such as syntax errors will always
automatically rollback any transaction and this behavior is unaffected by
the setting of SET XACT_ABORT. This is exactly what's happening with your
select statement « select 'Hello'+1 ».

If you are talking about other types of error - such as a constraint
violation - then you should make your test/example/post with such a type.

In your case, you should always test for @@trancount every time you want to
make a commit or a rollback.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by (O)enone
I am experiencing a problem with transaction handling in my SQL
Server-based application. To demonstrate the problem, here are some simple
T-SQL statements, which can be executed one by one in SQL Server Management
\\\
-- Get the current transaction count, which is of courser zero
-- Begin a transaction named "Test"
begin transaction Test
-- Get the transaction count -- this time it's 1, as expected
-- Perform a SELECT statement that fails. This returns an error because it
can't cast 'Hello' as an integer.
select 'Hello'+1
-- Get the transaction count for a file time. This now returns zero.
///
The SELECT statement is causing SQL Server to automatically roll back the
transaction. This is causing problems in my application, as the program
doesn't expect the transaction to have rolled back at this point. Upon
handling this error, the program then attempts to explicitly roll back the
transaction, which causes SQL Server to error for a second time, stating
that the ROLLBACK has no corresponding BEGIN TRANSACTION. I therefore lose
sight of the original error that actually occurred.
Is there a way to stop SQL Server from automatically rolling back
transactions? I really want the only thing that causes the transaction to
roll back to be an explicit ROLLBACK command.
I've tried SET ARITHABORT OFF and SET XACT_ABORT OFF, but neither made any
difference to the results of the steps above.
Can anyone enlighten me as to how I can get this to do what I want?
(Running against SQL Server 2000, but the same results are experienced in
SQL Server 2005).
Many thanks,
--
(O)enone

[Russell Fields]

(O)enone,

If you track your through the execution more closely you will see that the
last "select @@trancount" is never executed. Although the severity 16
error is at execution time instead of compile time, it still terminates the
code, in this case a batch, that is running.

Of course, it is not the SELECT that raises the problem, but the attempt to
cast 'Hello' as a number. If that reflects the problem that you are
actually facing, you might find this helpful:
http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

And, as Sylvain said, always test your @@trancount before doing a commit or
rollback. This is a good practice because BEGIN TRAN and COMMIT TRAN are
symetric, but ROLLBACK is not.
http://www.codeproject.com/database/sqlservertransactions.asp

And, if your are using SQL Server 2005, you can look into the TRY/CATCH
blocks to capture this error and handle it. The Books Online have a good
explanation.

RLF

Post by (O)enone
I am experiencing a problem with transaction handling in my SQL
Server-based application. To demonstrate the problem, here are some simple
T-SQL statements, which can be executed one by one in SQL Server Management
\\\
-- Get the current transaction count, which is of courser zero
-- Begin a transaction named "Test"
begin transaction Test
-- Get the transaction count -- this time it's 1, as expected
-- Perform a SELECT statement that fails. This returns an error because it
can't cast 'Hello' as an integer.
select 'Hello'+1
-- Get the transaction count for a file time. This now returns zero.
///
The SELECT statement is causing SQL Server to automatically roll back the
transaction. This is causing problems in my application, as the program
doesn't expect the transaction to have rolled back at this point. Upon
handling this error, the program then attempts to explicitly roll back the
transaction, which causes SQL Server to error for a second time, stating
that the ROLLBACK has no corresponding BEGIN TRANSACTION. I therefore lose
sight of the original error that actually occurred.
Is there a way to stop SQL Server from automatically rolling back
transactions? I really want the only thing that causes the transaction to
roll back to be an explicit ROLLBACK command.
I've tried SET ARITHABORT OFF and SET XACT_ABORT OFF, but neither made any
difference to the results of the steps above.
Can anyone enlighten me as to how I can get this to do what I want?
(Running against SQL Server 2000, but the same results are experienced in
SQL Server 2005).
Many thanks,
--
(O)enone

[(O)enone]

Post by Russell Fields
If you track your through the execution more closely you will see
severity 16 error is at execution time instead of compile time, it
still terminates the code, in this case a batch, that is running.

Hi Russell,

Sorry, I should have stressed in my original post, I'm running each of the
statements that I listed individually rather than as a single batch. The
statements are simulating what is going on in my application (which is
running using ADO.NET rather than in SQL Server Management Studio, but which
experiences exactly the same problem).

The casting 'Hello' as a number is a contrived example to demonstrate the
point. The actual errors my application is experiencing are much more
subtle, but ultimately still result in an arithmetic error of some
description. We've tried the same thing with lots of other execution errors
instead of the cast error (primary key violations, foreign key violations,
putting in textual or numeric values that are too large for the field to
contain them, storing out-of-range date values, null values in non-nullable
fields) and whilst all of these operations generate execution errors, none
of them roll back the open transaction. But updating a field with a value
that cannot be converted for that field rolls it back every time.

If there's no way to handle this in SQL Server 2000 then I guess I'll just
have to put up with it for now, but I'm surprised there's no way to control
this.

Do you know whether the TRY/CATCH structure in SQL Server 2005 would allow
me to catch only this type of error, and allow the other errors (those
listed above, for example) to be unaffected? And would catching this error
enable me to handle it without the transaction being rolled back?

[Uri Dimant]

Hi
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html\

Post by (O)enone

Post by Russell Fields
If you track your through the execution more closely you will see
severity 16 error is at execution time instead of compile time, it
still terminates the code, in this case a batch, that is running.

Hi Russell,
Sorry, I should have stressed in my original post, I'm running each of the
statements that I listed individually rather than as a single batch. The
statements are simulating what is going on in my application (which is
running using ADO.NET rather than in SQL Server Management Studio, but
which experiences exactly the same problem).
The casting 'Hello' as a number is a contrived example to demonstrate the
point. The actual errors my application is experiencing are much more
subtle, but ultimately still result in an arithmetic error of some
description. We've tried the same thing with lots of other execution
errors instead of the cast error (primary key violations, foreign key
violations, putting in textual or numeric values that are too large for
the field to contain them, storing out-of-range date values, null values
in non-nullable fields) and whilst all of these operations generate
execution errors, none of them roll back the open transaction. But
updating a field with a value that cannot be converted for that field
rolls it back every time.
If there's no way to handle this in SQL Server 2000 then I guess I'll just
have to put up with it for now, but I'm surprised there's no way to
control this.
Do you know whether the TRY/CATCH structure in SQL Server 2005 would allow
me to catch only this type of error, and allow the other errors (those
listed above, for example) to be unaffected? And would catching this error
enable me to handle it without the transaction being rolled back?
--
(O)enone

[(O)enone]

Post by Uri Dimant
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html\

This in particular has been very useful. Not the answer I wanted, but at
least an answer. :-/

Thanks to all for your help,

[Russell Fields]

(O)enone,

The transaction is already doomed because the error is of "batch abortion"
severity. I tested and you can CATCH, but it is pointless in this case
unless you simply want to disguise the message that is issued.

RLF

Post by (O)enone

Post by Russell Fields
If you track your through the execution more closely you will see
severity 16 error is at execution time instead of compile time, it
still terminates the code, in this case a batch, that is running.

Hi Russell,
Sorry, I should have stressed in my original post, I'm running each of the
statements that I listed individually rather than as a single batch. The
statements are simulating what is going on in my application (which is
running using ADO.NET rather than in SQL Server Management Studio, but
which experiences exactly the same problem).
The casting 'Hello' as a number is a contrived example to demonstrate the
point. The actual errors my application is experiencing are much more
subtle, but ultimately still result in an arithmetic error of some
description. We've tried the same thing with lots of other execution
errors instead of the cast error (primary key violations, foreign key
violations, putting in textual or numeric values that are too large for
the field to contain them, storing out-of-range date values, null values
in non-nullable fields) and whilst all of these operations generate
execution errors, none of them roll back the open transaction. But
updating a field with a value that cannot be converted for that field
rolls it back every time.
If there's no way to handle this in SQL Server 2000 then I guess I'll just
have to put up with it for now, but I'm surprised there's no way to
control this.
Do you know whether the TRY/CATCH structure in SQL Server 2005 would allow
me to catch only this type of error, and allow the other errors (those
listed above, for example) to be unaffected? And would catching this error
enable me to handle it without the transaction being rolled back?
--
(O)enone