Force a warning depending on Procedure Execution Result

[joycerecacho]

Hi everybody.

I am calling an SQL Server Stored Procedure through a job DataStage and, at the end of its execution, the job always finishes ok, independent on the Stored Procedure result.

I can see its Output variable by the Peek - at the log - but I can take any action about it.

I would like to provoke a warning in case of problems with the Procedure Execution. If there is a problem when execute an Update operation at the Proc, the RAISERROR doesn't appear at the Datastage log and there is no log to report it, only the Output variable tells me there is a problem.

The user must be notified and the job must finish with warnings or aborted.

How could I do this?

PS. I prefer not to use those rules to message handler.

Thank you very much,

[chulett]

Perhaps just a call to DSLogFatal() depending on the outcome of the output variable check?

[joycerecacho]

Craig, thanks for your answer.

And where should I place/call this function?

[joycerecacho]

You mean read the Output variable in the Transformer Constraint and check the Otherwise/Log Option if the result is different from the one that I want?

Or you have another idea?

[chulett]

Downstream transformer. If that function is not available in a PX transformer, then perhaps a Server job or a Server Shared Container to leverage a BASIC transformer.

[chulett]

The constraint idea could work as well, I would imagine.

[joycerecacho]

Hi friends.

I used the transformer to read the output from the Procedure and then, I directed this output to two peeks. One of them is to identify the problem. If a row passes by that link, the job aborts. I informed this action at the option: 'Abort After Rows' - I informed one row.

So, when the job aborts, there is an error log with the Procedure Return at the Director.

Thanks Guys.
Joyce