0

I apologize is this is a repeat. I couldn't find a question that was answered that fit what I was asking. (this is similar How can i create out parameters with RAISERROR in stored procedure?)

I have a very simple job in SQL Server Agent with one step. The package only executes a stored procedure. The stored procedure has a try / catch block and when I try to cause an exception it is caught but doesn't propagate up and cause the job to report an error. The job succeeds. I log the error into a local table and commit it so I know I am indeed entering the catch block. I have tried raising error with severity levels 16 and 20 and some other things that were probably not worth mentioning.

Is this a package setting that would cause it to not propagate or is a problem with my stored procedure? Thanks.

-- dumbed down structure
BEGIN

    SET NOCOUNT ON;

    -- buncha vars
    BEGIN TRY
    BEGIN TRANSACTION

      set @StepNumber = 10;

      select 1/0;

    COMMIT TRANSACTION;
    RETURN 0;
    END TRY
    BEGIN CATCH

        -- get the error
        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        -- rollback the transaction
            ROLLBACK TRANSACTION;

        -- log the caught exception into the local log
        set @LogTimestamp = GETUTCDATE(); 
        set @LogMessage = @SessionGUID + ' - ' + CAST(@StepNumber AS NVARCHAR) + ' - ' + ' ERROR - ENTERED CATCH BLOCK - ERROR_MESSAGE()=' + @ErrorMessage + ' ERROR_SEVERITY()=' + cast(@ErrorSeverity as nvarchar) + ' ERROR_STATE()=' + CAST(@ErrorState as nvarchar) ;
        insert into @LocalLog(TimestampDT, SourceVch, MessageVch) values (@LogTimestamp, @LogSource, @LogMessage);

        -- transfer local log to master log table
        BEGIN TRANSACTION;
            --insert into RealTable selecting from @LocalLog;
        COMMIT TRANSACTION;

        -- slightly modify the error message before raising it to parent process
        SET @ErrorMessage = @LogSource + ' - error with @StepNumber=[' +CAST(@StepNumber as nvarchar)+ '] ERROR_MESSAGE()=' + @ErrorMessage;

        -- rethrow the error to the calling process
        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

        -- do we need this return?
        RETURN @StepNumber;
    END CATCH
END

The log table I get tells me I caught the error at least (From my log table). "6a3e80fd-f480-459c-a42f-25fd3d5a42a8 - 125 - ERROR - ENTERED CATCH BLOCK - ERROR_MESSAGE()=Divide by zero error encountered. ERROR_SEVERITY()=16 ERROR_STATE()=1"

Community
  • 1
  • 1
TEEKAY
  • 1,156
  • 1
  • 10
  • 25
  • Does SSIS notice the error and take its exception path? – RBarryYoung Apr 17 '13 at 20:59
  • Running the package in debugger does not fail either (I'm doing this over someones shoulder so I haven't been able to investigate it completely though). Produces the same log message from the stored proc. PackageStart -> Beginning of package execution. -> OnProgress -> Executing query -> PackageEnd -> PackageEnd. – TEEKAY Apr 18 '13 at 12:31
  • I suspect that this has to do with how the ExecuteSQL task (or its connection manager) is set up in your SSIS package, but it's been a long time for me ... – RBarryYoung Apr 18 '13 at 12:47
  • I appreciate your continued response. I punted on trying to get the error to propagate via RAISERROR in the handler of the stored proc. I commented out the RAISERROR statement and am returning @StepNumber. I then added another workflow piece in the package and scripted if = success else failure and branch those to a success email or failure email inside of the package. In SQL Server Agent -> Jobs when I start and step and run it. It produces an error on the step (which I wanted in the first place). I'm not sure what I was doing wrong yet but this works. Thanks again. – TEEKAY Apr 18 '13 at 14:59
  • Apparently ADO.Net drivers work with RAISEERROR better than OLE DB drivers, but I have not had a chance to test – Nick.Mc May 02 '14 at 05:44

0 Answers0