DSXchange

Hi,

I have a parallel job in V 8.7. The design has a row generator, generating 1 dummy row followed by Stored Procedure stage.

The DB is MSSQL Server
Procedure type - Transform
No parameters are being passed to SP and no values are returned from the SP.
However even when the SP fails, the Datastage job is finishing successfully. The SP uses RAISERROR and returning @Errmsg.

I want the job to fail when SP fails.

Any insight would be helpful.

Thanks.

A Transform SP usually expects to have input(s) and output.

Right... in your case it should be set to 'Target'.

Mostly the job does abort when the SP fails due to primary key violation. There haven't been any other errors which made the SP fail and hence the datastage job didn't fail too.

We haven't changed anything in the job.

I wanted to add to this because I was struggling with a similar issue and just discovered the answer. For calling a Target SQL Server Stored Procedure, I had to uncheck the General Procedure Call box and use the following syntax:

EXEC ? = <procedure name> ?, ?, ?, ?