How to capture raiseerror warnings from mssql stored procedure

Five Stars

How to capture raiseerror warnings from mssql stored procedure

I am using tDBSP to call MS SQL stored procedure.  I can get the Out parameters, resultset parameters and '@RETURN_VALUE from the SP but I could not get the raiseError exception thrown from SP.   I have tried the below options to capture the exception in the job but I could not.

1. globalMap.get("tDBSP_1_ERROR_MESSAGE") . - Returning null

2. tAssertCatcher/tLogCatcher 

 

POC Store procedure

    BEGIN

       DECLARE @ErrMsg NVARCHAR(4000) ,@ErrSeverity INT

       SELECT  @ErrMsg = 'Test Error' , @ErrSeverity = -1

       RAISERROR(@ErrMsg, @ErrSeverity, 1)

    END

 

Please see the attached screen shot from DB Visualizer when I try to execute the SP directly.  Is there any way i can capture the Warning  in Talend.

 

Screen Shot 2019-02-19 at 4.48.23 PM.png

 


Accepted Solutions
Five Stars

Re: How to capture raiseerror warnings from mssql stored procedure

The tlogcatcher get the RAISEERROR exception message from SP.  Incase any one looking for the solution.

View solution in original post


All Replies
Highlighted
Employee

Re: How to capture raiseerror warnings from mssql stored procedure

Hi,

 

    Could you please try to use it as function where you can capture the error value inside SP itself and send them as a return value with a specific return code (say -3 instead of normal return value of 0)

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: How to capture raiseerror warnings from mssql stored procedure

Thanks nikhil.  I am already using isFunction and get the return value.  The issue is how to get the RaiseError message from SP.

Employee

Re: How to capture raiseerror warnings from mssql stored procedure

Hi,

 

    Could you please try below method where you are passing the data to an output variable and send it to a tFlowtoIterate.

image.png

 

Then you can verify whether the status is not equal to 0 by a Run if condition.

image.png

 

I dont have a MSSQL server handy with me. So I could not personally test it. But I believe this flow in its exact form or with minor modifications should work fine.

 

Could you please try it out and let me know the results?

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Five Stars

Re: How to capture raiseerror warnings from mssql stored procedure

Thanks Nikhil.

 

I already have the same steps to get the return value using 'Is Function'  and verify the SP is returning 0 or something else.  The tFlowIterate doesnt help with the

RAISERROR messages from Stored Procedure.

 

Employee

Re: How to capture raiseerror warnings from mssql stored procedure

Hi,

 

    Are you using Try catch block in the Stored Procedure so that any error can be caught within Stored Procedure itself and can be converted to appropriate return values?

 

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-...

 

Once you are handling the error messages within Stored Proecedure and converting them to proper return values, my understanding is that you will be able to manage them using tFlowtoIterate as shown above.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

Five Stars

Re: How to capture raiseerror warnings from mssql stored procedure

Yes Nikhil. I am using try ... catch block in the Stored Procedures with user defined error message. (    Are you using Try catch block in the Stored Procedure so that any error can be caught within Stored Procedure itself and can be converted to appropriate return values?)

 

In the Talend I am trying to get the User defined Error Message and store it in the log and another error table.    But not sure how to get the RAISERROR messages in tDBSP.  I have tried the OUT/RECORD SET type but getting error saying invalid column name .    I am already getting the returnvalue (that is not an issue).   Based on the return value i can handle further steps for SP success/failure steps .  tFlowtoIterate doesnt help to get the RAISERROR messages.

 

Here is the raiseerror doc:

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-...

 

Five Stars

Re: How to capture raiseerror warnings from mssql stored procedure

The tlogcatcher get the RAISEERROR exception message from SP.  Incase any one looking for the solution.

View solution in original post

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog