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.

quotation marks in xml

I have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.

this section shows the problem, i am loading xml from a file and trying to insert this into the sql database

XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));
reader.WhitespaceHandling = WhitespaceHandling.None;
XmlDocument xmlDocF = new XmlDocument();
xmlDocF.Load(reader);
this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;
this.SqlDataSource1.Update();
reader.Close();

pls help

Can you post some part of xml?

|||

<_n0011 HyperLink="undefined" Welcome="A'hneiv zipv meih" Language="undefined" country="undefined" x="1329" y="338"/>

this is an example.

there are about 400 similar tags with about 30 instances of the single quote mark. this may change in the future as i am trying to make the xml editable.

|||

I believe using Parameters will take care of the escaping automatically.

|||

I have tried the following syntax and got the same problems. I think this type of parameterising just parses the text into the string and produces effectively the same problem. Is there a different type of parameterising that I should be using.

this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] @.xmlDocF.InnerXml where [index] = 1" ;

I am a bit new to this subject and have looked for an article on this subject but not been able to find anything that deals with this specific requirement.

many thx for your assistance.

|||

what kind of parameter method did u have in mind?

|||

I created a DataSource with insert/update/delete commands and connected it to a GridView.

In edit mode, I entered this in the AVarCharField: Hello, "test" 'test'

When I clicked update, it worked.

The UpdateCommand looks like this:

UpdateCommand="UPDATE [TestTable] SET [CustomerName] = @.CustomerName, [Status] = @.Status, [AVarCharField] = @.AVarCharField WHERE [ID] = @.ID">

The Parameters look like this:

<UpdateParameters> <asp:Parameter Name="CustomerName" Type="String" /> <asp:Parameter Name="Status" Type="Single" /> <asp:Parameter Name="AVarCharField" Type="String" /> <asp:Parameter Name="ID" Type="Int32" /></UpdateParameters>

I hope that helps.

quick transaction inside a stored procedure question

a transaction block inside a sproc

if after one of the tran statements, I bust out of the sproc with a return statement

do I have to rollback explicitly first or does the tran rollback automatically?

appreciate the tip

After returning to another procedure the @.@.TRANCOUNT should be still increased by 1. You can test that by printing out the @.@.trancount variables in both procedure. In common, I would always handle the transcation context on my own by settings explizit transaction heaviour.

HTH, jens Suessmeyer.|||thanks jens|||

Could you please rate the thread as solved or helpful or whatever, that its no longer present as unanswerd ?

Thanks, Jens.