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 reportsthe SQL task succeeds (the task box turns green) when RAISERROR is invoked. If Icommentthe catch block with RAISERROR thenSSIS reportsthe 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 runfrom 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
May 31st, 2007 12:31am

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"?
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2007 12:35am

Can you please post code for both versions of the stored procedure?
May 31st, 2007 12:35am

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 pointof 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_NUMBERINTOUTPUT,@ERROR_MESSAGENVARCHAR(4000)OUTPUT, @ERROR_SEVERITYINTOUTPUT, @ERROR_STATEINTOUTPUT, @ERROR_PROCEDURENVARCHAR(126)OUTPUT, @ERROR_LINEINTOUTPUT,@FormattedMessageNVARCHAR(4000)OUTPUTAS BEGIN SET NOCOUNT ON -- this forces a "divide by zero" exceptionSELECT 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_NUMBERINTOUTPUT,@ERROR_MESSAGENVARCHAR(4000)OUTPUT, @ERROR_SEVERITYINTOUTPUT, @ERROR_STATEINTOUTPUT, @ERROR_PROCEDURENVARCHAR(126)OUTPUT, @ERROR_LINEINTOUTPUT,@FormattedMessageNVARCHAR(4000)OUTPUTAS 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" exceptionSELECT 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_NUMBERINTDECLARE@local_ERROR_MESSAGENVARCHAR(4000)DECLARE @local_ERROR_SEVERITYINTDECLARE @local_ERROR_STATEINTDECLARE @local_ERROR_PROCEDURENVARCHAR(126)DECLARE @local_ERROR_LINEINTDECLARE @local_FormattedMessageNVARCHAR(4000) EXEC [dbo].[sp_ThrowException]@ERROR_NUMBER= @local_ERROR_NUMBEROUTPUT,@ERROR_MESSAGE= @local_ERROR_MESSAGEOUTPUT,@ERROR_SEVERITY= @local_ERROR_SEVERITYOUTPUT,@ERROR_STATE= @local_ERROR_STATEOUTPUT,@ERROR_PROCEDURE= @local_ERROR_PROCEDUREOUTPUT,@ERROR_LINE= @local_ERROR_LINEOUTPUT,@FormattedMessage= @local_FormattedMessageOUTPUTSELECT @local_ERROR_NUMBERAS ERROR_NUMBER,@local_ERROR_MESSAGEAS ERROR_MESSAGE,@local_ERROR_SEVERITYAS ERROR_SEVERITY,@local_ERROR_STATEAS ERROR_STATE,@local_ERROR_PROCEDUREAS ERROR_PROCEDURE,@local_ERROR_LINEAS ERROR_LINE,@local_FormattedMessageAS FormattedMessage Thanks for looking at this. BCB
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2007 1:01am

As long as it's posted to the messages tab, to my knowledge, you cannot capture that inside SSIS.
May 31st, 2007 6:02am

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 pointof 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 procedureand 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. It's not elegant, but it should get the job done...
Free Windows Admin Tool Kit Click here and download it now
May 31st, 2007 6:58am

We had exactly the same issue with SSIS and RAISERROR in the TRY/CATCH block the solution was a PRINT in the CATCH block, why?...i have no clue but it works Code Snippet BEGIN CATCH PRINT 'some error message' RAISERROR('some error message',16,1) END CATCH
January 23rd, 2008 4:15pm

I actually worked on the connect item for thisissue awhile ago. It has (mostly) been resolved in Katmai. One thing to note is that the behavior for RAISEERROR was different for all of the providers. It already worked correctly in 2005 SP2 for ADO.NET, succeeded for OLEDB and ADO (when it should fail), and returned a weird error for ODBC. In Katmai it's been fixed to work as expected for OLEDB and ODBC. The connect item is still open as we're trying to resolve whether or not it's possible to handle it correctly with ADO. ~Matt
Free Windows Admin Tool Kit Click here and download it now
January 23rd, 2008 10:32pm

Matt, Thanks for posting this workaround:One thing to note is that the behavior for RAISEERROR was different for all of the providers. It already worked correctly in 2005 SP2 for ADO.NET, succeeded for OLEDB and ADO (when it should fail), and returned a weird error for ODBC.Changing the provider to ADO.net worked like a charm
April 1st, 2008 10:27pm

THANKS, I Tried it and it does work Thanks for pointing me to it.But, We have many queries already using OLEDB source objects and it would take a lot of effort to switch to DataReader objects to go ADO.net. We have exposed risk staying with oledb, so are there any updates, or plans to update OLEDB to work and raise these errors?RP
Free Windows Admin Tool Kit Click here and download it now
February 20th, 2009 11:40pm

See the amazing reply by Microsoft (ignoring zillion of SQL 2005 users): https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276828 Greetings, Thank you for your feedback and your use of SQL Server. We really appreciate customers like you that are willing to share their feedback and help make this product better. For this specific issue, SQL Server 2008 will contain fixes for this issue when using the Execute SQL Task with the OLEDB or ODBC connection managers. You should notice this fix in an upcoming CTP. We look forward to hearing more from you, and thanks again.-David Posted by Microsoft on 2/15/2008 at 10:39 AM
April 4th, 2009 1:15am

Sorry, I should have said the issue was: SQL Server 276828. SSIS Execute SQL Task not signalling failure on a Raiserror SSIS Execute SQL Task not signalling failure on a Raiserror event when having TSQL Raiserror statement and a Select statement returning a result set in same stored procedure or TSQL batch. The TSQL raiserror statement is used in stored procedures calls and TSQL batches in the Execute SQL task in SSIS to trigger a failure condition for the task. This is used quite heavily in the application framework to communicate error conditions between stored procedures and SSIS. It works beautifully except in the case when a select is returning a result set from the stored procedure when called from an Execute SQL task. Returns a successful result for Execute SQL task: select 'Parameter value is invalid, DXMatchOption 1' raiserror ('Parameter value is invalid, DXMatchOption 1', 16, 1) Returns an Error Result for Execute SQL task: raiserror ('Parameter value is invalid, DXMatchOption 1', 16, 1) Closed feedback entered 5/11/2007 by vnapoli pseverini wrote "YES" on 2/26/2008 Microsoft, Please fix this issue. ryan_gartner wrote "YES" on 3/6/2008 SQLinSouthseaUK wrote "YES" on 3/11/2009 In SSIS (SQL Server 2005 Integration Services) In an "Execute SQL Task" block This RAISERROR does NOT cause the SSIS package to fail: RAISERROR(N'My Error Message',16,1) Whereas this DOES force the SSIS package to return an error PRINT N'My Error Message'; RAISERROR(N'My Error Message',16,1) *@#$%*! And YES, I agree, I should be fixed in 2005 not 2008
Free Windows Admin Tool Kit Click here and download it now
April 4th, 2009 1:22am

Can you please let me know if there is a plan to issue a fix in a CU for 2005?I am running into the same issue and no workaround seems to work for me.I am on: SQL Server 2005 - 9.00.4207.00 (X64)1. Using OLEDB - which is our preferred method: I tried the inclusion of PRINT stmt as suggested by some, and it doesn't work for me.2. Using ADO.Net - which is NOT our preferred method since we would need to rework a lot of code: I run into the issue "Object Reference Not Set to An Instance of an Object" while parsing the Execute SQL task as posted at:http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/b08a2bbf-0e71-4b90-ad3f-081de2109706-- with the exception (to that post) that my task always fails due to that (known? -- is it really a known issue, with no fix?) issue.So, I am at a dead end.Let me knowif there is a way forward
June 17th, 2009 10:32am

The workaround does not work against SQL Server 2008 R2 either. This includes but is not limited to Raiserror statements The only way to ensure your error messages are propagated to .NET clients is to either refrain from selecting to a recordset before any code that may generate an error OR perform your SELECT operations in a separate Stored Procedure to any other activity.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2011 6:34am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics