Fatal Error for Stored Proc stage

dsuser7 · Post by **dsuser7** » Tue Nov 25, 2014 9:54 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 25, 2014 3:35 pm

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

chulett · Post by **chulett** » Tue Nov 25, 2014 5:39 pm

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

dsuser7 · Post by **dsuser7** » Wed Dec 31, 2014 9:35 am

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.

devo · Post by **devo** » Tue Mar 17, 2015 9:24 am

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> ?, ?, ?, ?