RAISERROR does not cause SQL task to fail - why?

Greetings,

I have a stored procedure with a TRY / CATCH block. In the catch block I capture information about the error. I then use RAISERROR to "rethrow" the exception so that it will be available to SSIS.

I execute the stored procedure through a SQL task. I observe that SSIS reports the SQL task succeeds (the task box turns green) when RAISERROR is invoked. If I comment the catch block with RAISERROR then SSIS reports the task failed. (I created a simple procedure that does a divide by zero to force an error.) The expected error message is displayed when the sproc is run from the SQL Server Management Studio command line so I believe that the stored procedure is doing what I intended.

I would like to handle an error within my stored procedure without destroying SSIS's ability to detect a task failure. Is this possible? Is there an alternative to using RAISERROR?

Thanks,

BCB

But is it a message or an output? That is, which tab in SSMS does the results of the procedure end up on? "Results" or "Messages"?|||Can you please post code for both versions of the stored procedure?|||

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

This is the sproc with no CATCH:

Code Snippet

-- This is the stripped procedure with no CATCH block. The OUTPUT parms do nothing.
-- This sproc will cause an SQL task to fail.
ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN

SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END

This is the sproc as I really want it. It has the RAISERROR in the CATCH block.

Code Snippet

-- This is the desired procedure with the CATCH block. The error information is being returned
-- to the SQL task through the OUTPUT parms.
-- This sproc will allow an SQL task to succeed.

ALTER PROCEDURE [dbo].[sp_ThrowException]
@.ERROR_NUMBER INT OUTPUT,
@.ERROR_MESSAGE NVARCHAR(4000) OUTPUT,
@.ERROR_SEVERITY INT OUTPUT,
@.ERROR_STATE INT OUTPUT,
@.ERROR_PROCEDURE NVARCHAR(126) OUTPUT,
@.ERROR_LINE INT OUTPUT,
@.FormattedMessage NVARCHAR(4000) OUTPUT
AS

BEGIN TRY

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- this forces a "divide by zero" exception
SELECT 1 / 0 AS 'TRY' FROM view_OilRigs_REPORT R

END TRY

BEGIN CATCH

SELECT
@.ERROR_NUMBER = ERROR_NUMBER(),
@.ERROR_MESSAGE = ERROR_MESSAGE(),
@.ERROR_SEVERITY = ERROR_SEVERITY(),
@.ERROR_STATE = ERROR_STATE(),
@.ERROR_PROCEDURE = ERROR_PROCEDURE(),
@.ERROR_LINE = ERROR_LINE(),
-- this renders the error message in command line format
@.FormattedMessage = 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR(20)) + ', ' +
'Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR(20)) + ', ' +
'State ' + CAST(ERROR_STATE() AS NVARCHAR(20)) + ', ' +
'Procedure ' + ERROR_PROCEDURE() + ', ' +
'Line ' + CAST(ERROR_LINE() AS NVARCHAR(20)) + ', ' +
'Message: ' + ERROR_MESSAGE()

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR
(
@.ERROR_MESSAGE, -- Message text
@.ERROR_SEVERITY, -- Severity
@.ERROR_STATE -- State
)

END CATCH

I use this code to run the sproc in Management Studio.

Code Snippet

-- I use this to run the sproc within Management Studio.

SET NOCOUNT ON;

DECLARE @.local_ERROR_NUMBER INT
DECLARE @.local_ERROR_MESSAGE NVARCHAR(4000)
DECLARE @.local_ERROR_SEVERITY INT
DECLARE @.local_ERROR_STATE INT
DECLARE @.local_ERROR_PROCEDURE NVARCHAR(126)
DECLARE @.local_ERROR_LINE INT
DECLARE @.local_FormattedMessage NVARCHAR(4000)

EXEC [dbo].[sp_ThrowException]
@.ERROR_NUMBER = @.local_ERROR_NUMBER OUTPUT,
@.ERROR_MESSAGE = @.local_ERROR_MESSAGE OUTPUT,
@.ERROR_SEVERITY = @.local_ERROR_SEVERITY OUTPUT,
@.ERROR_STATE = @.local_ERROR_STATE OUTPUT,
@.ERROR_PROCEDURE = @.local_ERROR_PROCEDURE OUTPUT,
@.ERROR_LINE = @.local_ERROR_LINE OUTPUT,
@.FormattedMessage = @.local_FormattedMessage OUTPUT
SELECT
@.local_ERROR_NUMBER AS ERROR_NUMBER,
@.local_ERROR_MESSAGE AS ERROR_MESSAGE,
@.local_ERROR_SEVERITY AS ERROR_SEVERITY,
@.local_ERROR_STATE AS ERROR_STATE,
@.local_ERROR_PROCEDURE AS ERROR_PROCEDURE,
@.local_ERROR_LINE AS ERROR_LINE,
@.local_FormattedMessage AS FormattedMessage

Thanks for looking at this.

BCB

|||As long as it's posted to the messages tab, to my knowledge, you cannot capture that inside SSIS.|||

BlackCatBone wrote:

Thanks for the replies.

Phil, to answer your question, the error message is displayed on the Messages tab in Management Studio.

I'm including two versions of the sproc as well as the invocation code I used to run it within Management Studio. I'm successfuly capturing the error info in the CATCH block and passing it back to SSIS. The point of having the RAISERROR at the end of the CATCH is to make SSIS fail the task.

I don't think you can have it both ways. If the task fails, you're not going to be able to use it to capture the output parameters.

I'm personally surprised that SSIS does not fail the task when you use RAISERROR in your procedure code. I would have assumed that explicitly raising an error would cause the task to fail, but it does not. It's far too late for me to dig into this. Perhaps someone else can shed some light on the "why" of this behavior.

With that said, this is the approach that I would recommend if I needed to capture the error information from the stored procedure and have the package respond to the error as well:

Remove the RAISERROR from the procedure. Leave the TRY CATCH block in the procedure. Have the Execute SQL task call the procedure and capture the output parameters. Have precedence constraints below the Execute SQL task that follow a "success" path or an "error" path based on the values of the output parameters - likely the error number.