Page 1 of 1

Store Procedure

Posted: Thu Jan 22, 2009 10:08 am
by thumsup9
Hi All,

I have a server job in which an ODBC stage is calling a SQL Server Stored Procedure. This job is scheduled thru BMC scheduler. The job runs fine while the BMC shows it as a failure. Looks like the return code is an issue.

What would be the return code from the DS job in this case.

Currently when the return code =1 No action Otherwise Terminate is the condition in the BMC.

Please advice, thanks

Posted: Thu Jan 22, 2009 10:12 am
by chulett
Depends on the options you used in dsjob when scheduling. The -jobstatus option returns the 1 for OK you are looking for, without that a zero indicates success.

ps. Got nothing to do with the fact that your job runs a stored procedure, this applies across the board to all jobs regardless of their design.

Posted: Thu Jan 22, 2009 10:27 am
by thumsup9
This is how we have defined and from your response it looks like it will be 1 when success
D:\APPS\Ascential\DataStage\Engine\bin\dsjob -file d:\bmc_jobs\login Stateromsvr -run -jobstatus -param pmEnvironment="PROD" Production RunProc

Posted: Thu Jan 22, 2009 10:36 am
by chulett
Yes, you should be getting the standard DataStage return values from the job:

1 = Finished OK
2 = Finished (see log)
3 = Aborted

Etc. Can you check inside BMC what the actual return code is and verify that it knows a 1 is ok? Out of the box they typically want a 0 for success, from what I've seen. And verify there are indeed no warnings generated in your job's log.