Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by gv » Sat, 26 Apr 2008 05:10:58


Hi All,

I keep getting the following error messages when the Stored procedure is
executed below?


Nonqualified transactions are being rolled back. Estimated rollback
completion: 100%.
0
Server: Msg 3903, Level 16, State 1, Line 202
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
0

use MyDatabase


CREATE PROCEDURE MYPROCEDURE

AS

DECLARE @myERROR int -- Local @@ERROR
, @myRowCount int -- Local @@ROWCOUNT

BEGIN TRAN

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE Table_Catalog =
'MyDatabase' AND
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'MYTBLE')
BEGIN DROP TABLE MYTBLE END

CREATE TABLE MYTBLE
(...)

CREATE CLUSTERED INDEX
(...)

INSERT INTO MYTABLE
(...)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR <> 0 GOTO HANDLE_ERROR

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE Table_Catalog =
'MyDatabase' AND
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'MYTBLE2')
BEGIN DROP TABLE MYTBLE2 END

CREATE TABLE MYTBLE2
(...)

CREATE CLUSTERED INDEX
(...)

INSERT INTO MYTABLE2
(...)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR <> 0 GOTO HANDLE_ERROR

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE Table_Catalog =
'MyDatabase' AND
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'MYTBLE3')
BEGIN DROP TABLE MYTBLE3 END

CREATE TABLE MYTBLE3
(...)

CREATE CLUSTERED INDEX
(...)

INSERT INTO MYTABLE3
(...)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR <> 0 GOTO HANDLE_ERROR

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE Table_Catalog =
'MyDatabase' AND
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'MYTBLE4')
BEGIN DROP TABLE MYTBLE4 END

CREATE TABLE MYTBLE4
(...)

CREATE CLUSTERED INDEX
(...)

INSERT INTO MYTABLE4
(...)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR <> 0 GOTO HANDLE_ERROR


UPDATE MYTABLE4
(...)

SELECT @myERROR = @@ERROR, @myRowCount = @@ROWCOUNT
IF @myERROR <> 0 GOTO HANDLE_ERROR

COMMIT TRAN

ALTER DATABASE MYDATABASE
SET MULTI_USER

RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN

ALTER DATABASE MYDATABASE
SET MULTI_USER

RETURN @myERROR
GO


thanks
gv
 
 
 

Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by Russell Fi » Sat, 26 Apr 2008 05:59:55

v,

I cannot tell exactly what happened to cause "Nonqualified transactions" to
roll back. It could be an alter of the database state that is causing this
to happen. It is indicated in some cases, as are other changes that might
have a major impact on database state. Such a change makes the transaction
currently in process unable to continue (it no longer qualifies as a
transaction) so it rolls back to a known state.

However, once the transactions have rolled back, your code is seeing an
error and going to HANDLE_ERROR which is attempting to do a ROLLBACK which
has already happened. Before doing a ROLLBACK or COMMIT you should always
check the transaction count. E.g.

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION


RLF

"gv" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



 
 
 

Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Post by gv » Sat, 26 Apr 2008 06:42:02

Thanks for your help
gv