Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing

Question

I am migrating a ASP.Net Web Application Page that currently uses Oracle Database for posting a Transaction to ASP.Net and SQL Serve. This Page perfectly works in the Old Application using Oracle Database.

The Transaction basically comprises of a Master Child component (let us call this Company and Representative)

Both Company and Representative needs a Identity Key (I am not using here the Identity or Auto Increment column for specific reasons) and this is held in a Table t_Sequences that has two rows one for Company and another for Representative with a column inddicating the last Identity Value used which I increment and update. This design may sound old but I feel is the best approach to migrate Oracle Suence objects to SQL Server.

Both Company and Representative have a associated Address which too has a Identity key and so this too has  a Row in the Table t_Sequences.

In the ASP.Net page I set BeginTransaction before calling the SQL Server Procedure SP1 passing the Owner Details along with Address. In the finally section of the try I have a Connection.Commit ad in the catch have a Connection.Rollback.

Steps In Procedure SP1

1. EXECUTE leaguegames.getNextSeq 'ADDRESS', @pAddKey  to get Key for populating Address Table
2. Insert into t_Address
3. EXECUTE leaguegames.getNextSeq 'COMPANY', @pCompKey  to get Key for populating Company Table
4. ...

The is no COMMIT in any of the SQL Server Procedures

Procedure getNextSeq take two parameters - pSequenceName (Input Param) and a pNewKey (Output Param). Within the Procedure  getNextSeq I Update the Row in t_Sequence Table where the SequenceName = pSequenceName and assign the new value to getNextSeqwhich is a OUTPUT parameter.

However when the ASP.Net page calls the SQL Server Procedure SP1 soon after updating the row in t_Squences table for the SequenceName I experience the following error:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. Cannot insert the value NULL into column 'Address_ID', table leaguegames.t_Addresses

Could someone advise me why SQL Server doesnt handle a Transaction in the same was as Oracle does and what is the best alternative...

My requirement dictates that the ASP.Net Page should either post Owner and Representaive details together or none...

Thanks for your replies and suggestions in Advance.

Answer 1

Seems like your commit should be inside the try not in the finally.  With your code if you hit an error you rollback in the catch and then try to commit in the finally which is going to cause a problem.

---

Chuck

Answer 2

Normal way would be to only be using the finally to be closing the connection not doing commits

try{

begin tran

do stuff

commit tran

}

catch

{

roll back tran

}

finally{

if connection open then close connection

}

---

Chuck

Answer 3

The is no COMMIT in any of the SQL Server Procedures

Is there a ROLLBACK in the stored procedure?  Since you are handling transactions on the client side, it is important that the T-SQL code have no BEGIN TRAN, ROLLBACK or COMMIT.

Although probably not related to your problem, I don't think the commit belongs in the finally block since it will be executed even after an exception.  I think the commit should be done at the end of the try block.  The rollback should be in the catch block and enclosed in a nested try/catch.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Following link may be useful for you.

1. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18439
2. http://www.sqlservercentral.com/Forums/Topic721835-338-1.aspx
3. http://www.eggheadcafe.com/community/sql-server/13/10158145/total-rows-affected--trasactions.aspx

Answer 5

It would have helped to see your SQL code, but here are some general observations about error handling in SQL Server. The first and most important observation is that this is a very messy area...

When you get a run-time error in SQL Server, different things can happen depending on the type of error:

1) The batch may be aborted and the transaction rolled back.
2) The batch may be aborted but the transaction is not rolled back (new in SQL 2012)
3) The current scope is aborted. The transaction is not rolled back, and execution continues in the calling scope (if there is any).
4) The current statement is aborted and rolledback, but the transaction is not aborted. Execution continues with the next statement.

The error message includes a NOT NULL violation. This is an error of type 4. There is a setting in SQL Server SET XACT_ABORT ON. When this setting is in effect, most errors follow the first pattern.

Now, since you get this message, you get the error about trancount mismatch, execution was not aborted, but you had an error of type 3 or 4, and we have seen the NOT NULL error.

Commonly in a stored procedure you use TRY-CATCH, and the CATCH-block includes the line "IF @@trancount > 0 ROLLBACK TRANSACTION", which will result in error 266. (Which is the number for the message in the subject line.) This is a by-product of what is the proper handling: the procedure should roll back when an error occurs, because it is not able to fulfil its contract. This applies also when the procedure did not start the transaction itself.

The error message itself is noise, and you could write your exception handler in ASP .Net, so that error 266 is ignore, unless it's the only error. Because, if 266 is the only error the procedure has some proper transaction handling.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

It would have helped to see your SQL code, but here are some general observations about error handling in SQL Server. The first and most important observation is that this is a very messy area...

When you get a run-time error in SQL Server, different things can happen depending on the type of error:

1) The batch may be aborted and the transaction rolled back.
2) The batch may be aborted but the transaction is not rolled back (new in SQL 2012)
3) The current scope is aborted. The transaction is not rolled back, and execution continues in the calling scope (if there is any).
4) The current statement is aborted and rolledback, but the transaction is not aborted. Execution continues with the next statement.

The error message includes a NOT NULL violation. This is an error of type 4. There is a setting in SQL Server SET XACT_ABORT ON. When this setting is in effect, most errors follow the first pattern.

Now, since you get this message, you get the error about trancount mismatch, execution was not aborted, but you had an error of type 3 or 4, and we have seen the NOT NULL error.

Commonly in a stored procedure you use TRY-CATCH, and the CATCH-block includes the line "IF @@trancount > 0 ROLLBACK TRANSACTION", which will result in error 266. (Which is the number for the message in the subject line.) This is a by-product of what is the proper handling: the procedure should roll back when an error occurs, because it is not able to fulfil its contract. This applies also when the procedure did not start the transaction itself.

The error message itself is noise, and you could write your exception handler in ASP .Net, so that error 266 is ignore, unless it's the only error. Because, if 266 is the only error the procedure has some proper transaction handling.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

He said that the transaction logic was in the ASP.net app

---

Chuck

Answer 7

He said that the transaction logic was in the ASP.net app

People say a lot of things in these forums. :-)

But to make it clear: the root problem is a NOT NULL violation. And it appears that the stored procedure includes some logic to the rollback transaction in case of an error.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

He said that the transaction logic was in the ASP.net app

People say a lot of things in these forums. :-)

But to make it clear: the root problem is a NOT NULL violation. And it appears that the stored procedure includes some logic to the rollback transaction in case of an error.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

I'll half agree.  The root problem is a NOT NULL violation.  The rest of the problem is that on error his ASP.NET code rolls back the transaction and then tries to commit it.

---

Chuck

Answer 9

The rest of the problem is that on error his ASP.NET code rolls back the transaction and then tries to commit it.

No. That would be a different error message.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 10

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. Cannot insert the value NULL into column 'Address_ID', table leaguegames.t_Addresses

SQL Server T-SQL error messages have numerical ID-s. It is a good idea to post the entire error message including the numerical ID. Sample:

SELECT 1/0
/* Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
*/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 11

SQL Server T-SQL error messages have numerical ID-s. It is sufficient to give them as reference.

Only of the message does not take any parameters. For instance, for message 266, the one which is the topic for this thread, it is essential to know the value for previous and current count.

I'd rather take the message in verbatim than the message number. If I want to know the number, I can always look it up in sys.messages.

---

Erland Sommarskog, SQL Server MVP, [e-mail]