Insert Fails - Detailed Information

Question

Hi,

I am loading some data into a temp table. I then use a cursor to loop through the temp table, minipulate the data, and insert it into another table. The identity of the newly inserted record is then captured in a variable and inserted to another table to allow a look up of the newly inserted data.

    DECLARE c1 CURSOR READ_ONLY
FOR
SELECT NEWClientIndex, ClientGroup, ClientAccount, SKAccount, SKDesc, SKBase, SKBranch, ClientType, SKStatus, GFCID, GFPID, Account_Open_Date, Account_Update, 
                      SKType
FROM @ClientsAccounts 

OPEN c1


FETCH NEXT FROM c1
INTO @NEWClientIndex, @ClientGroup, @ClientAccount, @SKAccount, @SKDesc, 
    @SKBase, @SKBranch, @ClientType, @SKStatus, @GFCID, @GFPID, @Account_Open_Date,
    @Account_Update, @SKType

WHILE @@FETCH_STATUS = 0
    BEGIN
PRINT 'Processing Account'

        Print 'Inserting Account Information'
        -- We now need to loop through the data inserting account information 
        -- and maintaining the Account link table
        -- Each itteration of the cursor should result in one insert
        -- as each account should be linked to one client

        INSERT INTO CitiClientsAccounts (SKBranch, SKAccount, SKName, SKBase, SyncStatus,
            GFCID, GFPID, SyncInput, SyncUpdate, Deleted, Branch_Account, LastUpdatedBy, AccountTypeID)
            VALUES(convert(varchar(2), @SKBranch), convert(varchar(12), @SKAccount), convert(varchar(255),@SKDesc), 
            convert(varchar(16),@SKBase), convert(varchar(50),@SKStatus), convert(varchar(10),@GFCID), convert(varchar(10),@GFPID),
            @Account_Open_Date, @Account_Update, 0, convert(varchar(16),@ClientAccount), 'Admin', convert(int, @SKType))


            Declare @NEWID int
            Select @NEWID = SCOPE_IDENTITY()
            IF @NEWID is NULL
                BEGIN
                    Print 'Account Insert Failed'
                END
            ELSE
                BEGIN

                    --Match up account to the Client ID
                    Declare @ClientID int
                    Select @ClientID = LocalPrimaryKey
                        from dbo.CitiClients_LIBRARY
                    Where AccessPimaryKey = @NEWClientIndex

                    Print 'Updating Library'
                    Insert into dbo.ClientAccountLink 
                        (AccountID, ClientID, LastUpdatedBy) 
                    Values
                        (@NEWID, @ClientID, 'Admin')

            END
            Print 'End Insert Account Information'
        -- Move to Next Row
        FETCH NEXT FROM c1
        INTO @NEWClientIndex, @ClientGroup, @ClientAccount, @SKAccount, @SKDesc, 
            @SKBase, @SKBranch, @ClientType, @SKStatus, @GFCID, @GFPID, @Account_Open_Date,
            @Account_Update, @SKType
    END
CLOSE c1
DEALLOCATE c1

At times the insert fails, I have no idea why the insert does fail. I am just wondering how I can display more detailed information on why the insert is actually failing?

Answer 1

try using a catch block with more some PRINTs:

DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int,@CatchMessage varchar(500)


DECLARE c1 CURSOR READ_ONLY
FOR
SELECT NEWClientIndex, ClientGroup, ClientAccount, SKAccount, SKDesc, SKBase, SKBranch, ClientType, SKStatus, GFCID, GFPID, Account_Open_Date, Account_Update, 
                      SKType
FROM @ClientsAccounts 

OPEN c1


FETCH NEXT FROM c1
INTO @NEWClientIndex, @ClientGroup, @ClientAccount, @SKAccount, @SKDesc, 
    @SKBase, @SKBranch, @ClientType, @SKStatus, @GFCID, @GFPID, @Account_Open_Date,
    @Account_Update, @SKType

WHILE @@FETCH_STATUS = 0
    BEGIN
PRINT 'Processing Account'

        Print 'Inserting Account Information'
        -- We now need to loop through the data inserting account information 
        -- and maintaining the Account link table
        -- Each itteration of the cursor should result in one insert
        -- as each account should be linked to one client
        BEGIN TRY
        SET @CatchMessage='INSERT INTO CitiClientsAccounts'
        INSERT INTO CitiClientsAccounts (SKBranch, SKAccount, SKName, SKBase, SyncStatus,
            GFCID, GFPID, SyncInput, SyncUpdate, Deleted, Branch_Account, LastUpdatedBy, AccountTypeID)
            VALUES(convert(varchar(2), @SKBranch), convert(varchar(12), @SKAccount), convert(varchar(255),@SKDesc), 
            convert(varchar(16),@SKBase), convert(varchar(50),@SKStatus), convert(varchar(10),@GFCID), convert(varchar(10),@GFPID),
            @Account_Open_Date, @Account_Update, 0, convert(varchar(16),@ClientAccount), 'Admin', convert(int, @SKType))

            Declare @NEWID int
            Select @NEWID = SCOPE_IDENTITY(),@CatchMessage=null
            IF @NEWID is NULL
                BEGIN
                    Print 'Account Insert Failed'
                END
            ELSE
                BEGIN

                    --Match up account to the Client ID
                    Declare @ClientID int
                    SET @CatchMessage='SELECT dbo.CitiClients_LIBRARY'
                    Select @ClientID = LocalPrimaryKey
                        from dbo.CitiClients_LIBRARY
                    Where AccessPimaryKey = @NEWClientIndex

                    SET @CatchMessage='Insert into dbo.ClientAccountLink '
                    Print 'Updating Library'
                    Insert into dbo.ClientAccountLink 
                        (AccountID, ClientID, LastUpdatedBy) 
                    Values
                        (@NEWID, @ClientID, 'Admin')

            END
            Print 'End Insert Account Information'

        -- Move to Next Row
        SET @CatchMessage='FETCH NEXT FROM c1'
        FETCH NEXT FROM c1
        INTO @NEWClientIndex, @ClientGroup, @ClientAccount, @SKAccount, @SKDesc, 
            @SKBase, @SKBranch, @ClientType, @SKStatus, @GFCID, @GFPID, @Account_Open_Date,
            @Account_Update, @SKType
        END TRY
        BEGIN CATCH

            IF XACT_STATE()!=0
            BEGIN
                ROLLBACK TRANSACTION
            END

            --will echo back the complete original error message
            SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

            PRINT 'ERROR WHEN PROCESSING: '+ISNULL(@CatchMessage,'')
            PRINT '    @NEWClientIndex   ='+ISNULL(''''+CONVERT(varchar(max),@NEWClientIndex   )+'''','null')
            PRINT '    @ClientGroup      ='+ISNULL(''''+CONVERT(varchar(max),@ClientGroup      )+'''','null')
            PRINT '    @ClientAccount    ='+ISNULL(''''+CONVERT(varchar(max),@ClientAccount    )+'''','null')
            PRINT '    @SKAccount        ='+ISNULL(''''+CONVERT(varchar(max),@SKAccount        )+'''','null')
            PRINT '    @SKDesc           ='+ISNULL(''''+CONVERT(varchar(max),@SKDesc           )+'''','null')
            PRINT '    @SKBase           ='+ISNULL(''''+CONVERT(varchar(max),@SKBase           )+'''','null')
            PRINT '    @SKBranch         ='+ISNULL(''''+CONVERT(varchar(max),@SKBranch         )+'''','null')
            PRINT '    @ClientType       ='+ISNULL(''''+CONVERT(varchar(max),@ClientType       )+'''','null')
            PRINT '    @SKStatus         ='+ISNULL(''''+CONVERT(varchar(max),@SKStatus         )+'''','null')
            PRINT '    @GFCID            ='+ISNULL(''''+CONVERT(varchar(max),@GFCID            )+'''','null')
            PRINT '    @GFPID            ='+ISNULL(''''+CONVERT(varchar(max),@GFPID            )+'''','null')
            PRINT '    @Account_Open_Date='+ISNULL(''''+CONVERT(varchar(max),@Account_Open_Date)+'''','null')
            PRINT '    @Account_Update   ='+ISNULL(''''+CONVERT(varchar(max),@Account_Update   )+'''','null')
            PRINT '    @SKType           ='+ISNULL(''''+CONVERT(varchar(max),@SKType           )+'''','null')
            PRINT '    @NEWID            ='+ISNULL(''''+CONVERT(varchar(max),@NEWID            )+'''','null')
            PRINT '    @ClientID         ='+ISNULL(''''+CONVERT(varchar(max),@ClientID         )+'''','null')

            --pick one, your way to terminate:
            EXIT --exit loop
            RETURN --exit procedure
            GOTO TheEnd --jump to label

        END CATCH
    END
CLOSE c1
DEALLOCATE c1

TheEnd: