Help with transaction count error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with transaction count error

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-07-04 : 12:06:41

When executing the below proc with I get the error:

quote:
Msg 266, Level 16, State 2, Procedure spFies, Line 2
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Can anyone help me figure out why I get this error?

CREATE PROC dbo.spFies
    @UpdateType AS char(6),
    @FileID AS int = NULL,
    @FilePath AS varchar(250),
    @Name AS varchar(50)
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION

        --Remove existing file entries.
        IF (@UpdateType = 'Update' OR @UpdateType = 'Delete')
        BEGIN
            DELETE FROM dbo.tblFiles
            WHERE FileID = @FileID;
        END

        --Process a new file.
        IF (@UpdateType = 'Insert' OR @UpdateType = 'Update')
        BEGIN
            INSERT INTO dbo.tblFiles (FilePath, Name)
            VALUES (@FilePath, @Name);

            --Get the FileID and return it to the calling proc.
            SELECT @FileID = SCOPE_IDENTITY();
            RETURN @FileID;
        END
    
    COMMIT
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState);

    IF ((XACT_STATE()) = -1)
    BEGIN
        ROLLBACK;
    END
    ELSE IF ((XACT_STATE()) = 1)
    BEGIN
        COMMIT;
    END

END CATCH

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 12:25:45

You should either add a commit statement before the line indicated below in red, or move that to after the commit statement you already have.

....
        --Process a new file.
        IF (@UpdateType = 'Insert' OR @UpdateType = 'Update')
        BEGIN
            INSERT INTO dbo.tblFiles (FilePath, Name)
            VALUES (@FilePath, @Name);

            --Get the FileID and return it to the calling proc.
            SELECT @FileID = SCOPE_IDENTITY();
            COMMIT
            RETURN @FileID;
        END
....

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-07-04 : 16:10:12

You can use this code to verify that you commit (or rollback) all open transactions:

while @@trancount > 0 commit

Example:

begin transaction
begin transaction
begin transaction
begin transaction
select BeforeCommit =@@trancount
while @@trancount > 0 commit
select AfterCommit =@@trancount

begin transaction
begin transaction
begin transaction
begin transaction
select BeforeRollback = @@trancount
while @@trancount > 0 rollback
select AfterRollback = @@trancount

Results:

BeforeCommit
------------
4

(1 row(s) affected)

AfterCommit
-----------
0

(1 row(s) affected)

BeforeRollback
--------------
4

(1 row(s) affected)

AfterRollback
-------------
0

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources