topic Re: How to capture raiseerror warnings from mssql stored procedure in Talend Studio

How to capture raiseerror warnings from mssql stored procedure

Anonymous — Tue, 19 Feb 2019 21:53:07 GMT

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.

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Wed, 20 Feb 2019 08:44:27 GMT

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)

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 🙂

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Wed, 20 Feb 2019 14:31:20 GMT

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

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Wed, 20 Feb 2019 15:20:59 GMT

Hi,

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

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

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 🙂

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Wed, 20 Feb 2019 20:17:39 GMT

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.

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Thu, 21 Feb 2019 03:56:25 GMT

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-2017

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 🙂

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Thu, 21 Feb 2019 18:12:31 GMT

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-2017

Re: How to capture raiseerror warnings from mssql stored procedure

Anonymous — Thu, 21 Feb 2019 21:46:31 GMT

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