CODEWITHSUNDEEP
sqlsql-servertransactions
chamara
chamara

Reputation: 12711

SQL Server transaction handling

I'm running the following stored procedure and I'm receiving the error

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

what am I missing here?

CREATE PROCEDURE spImportData
AS

BEGIN TRANSACTION

BEGIN TRY 
   SET IDENTITY_INSERT PINCDOCControlNew..tblActionType ON
END TRY
BEGIN CATCH
   PRINT 'IDENTITY_INSERT IS ON'
END CATCH
GO

BEGIN TRY
   INSERT INTO PINCDOCControlNew..tblActionType(ActionTypeID,ActionType,ActionTypeDescription)
      SELECT ActionTypeID,ActionType,ActionTypeDescription 
      FROM PINCDOCControlOld..tblActionType

   SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF
END TRY
BEGIN CATCH
   SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF
   EXECUTE usp_GetErrorInfo
END CATCH

BEGIN TRY
    SET IDENTITY_INSERT PINCDOCControlNew..tblArea ON
END TRY
BEGIN CATCH
    PRINT 'IDENTITY_INSERT IS ON'
END CATCH

GO

BEGIN TRY
    INSERT INTO PINCDOCControlNew..tblArea(AreaID,AreaDescription,AreaNo)
        SELECT AreaNo,AreaDescription,Area 
        FROM PINCDOCControlOld..tblArea

    SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF
END TRY
BEGIN CATCH
    SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF
    EXECUTE usp_GetErrorInfo
END CATCH

IF @@ERROR <> 0
BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error in inserting.', 16, 1)
    RETURN
 END
 COMMIT

Upvotes: 3

Views: 6441

Answers (2)

marc_s
marc_s

Reputation: 754220

I would try to have just a single BEGIN TRY .... END TRY block, in which you have all your logic that you want to execute. If anything goes wrong - anywhere in your logic - you'll be thrown into the BEGIN CATCH.... END CATCH block.

Start your transaction before your BEGIN TRY, and have the only COMMIT as the last statement in your TRY block - and in your CATCH block, have a rollback.

Something like this:

CREATE PROCEDURE dbo.spImportData
AS
    BEGIN TRANSACTION
    BEGIN TRY 
        SET IDENTITY_INSERT PINCDOCControlNew..tblActionType ON

        INSERT INTO 
            PINCDOCControlNew..tblActionType(ActionTypeID, ActionType, ActionTypeDescription)
            SELECT 
               ActionTypeID, ActionType, ActionTypeDescription 
            FROM 
              PINCDOCControlOld..tblActionType

        SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF

        -- tblArea
        SET IDENTITY_INSERT PINCDOCControlNew..tblArea ON

        INSERT INTO 
             PINCDOCControlNew..tblArea(AreaID, AreaDescription, AreaNo)
           SELECT 
               AreaNo, AreaDescription, Area 
           FROM 
               PINCDOCControlOld..tblArea

        SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
       ROLLBACK TRANSACTION

       SET IDENTITY_INSERT PINCDOCControlNew..tblActionType OFF
       SET IDENTITY_INSERT PINCDOCControlNew..tblArea OFF

       EXECUTE usp_GetErrorInfo

       RAISERROR ('Error in inserting.', 16, 1)
    END CATCH

With this approach, you have exactly ONE BEGIN TRANSACTION, and either one single corresponding COMMIT TRANSACTION, or a single corresponding ROLLBACK TRANSACTION

I typically also add this SELECT statement to my CATCH block to get the error message and error code of what went wrong:

SELECT 
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 452947

what am i missing here?

You probably have a surplus rather than a deficit. You need to remove the GO statements.

The only sensible interpretation of your script is that it is all intended to be part of the spImportData stored procedure definition.

This ends at the first GO statement (used by client tools to delimit batches) and the remaining batches are executed immediately in auto commit transactions as no explicit BEGIN TRAN has been run. When the COMMIT statement is reached there is nothing to commit hence the error.

Upvotes: 7

Related Questions