I need to run a job based on the return code from a stored procedure.
I want to exec the stored procedure first then depending on the return run the job.
I know I need to set a dependency but first I need to have 24x7 read the return code and decide whether to run the job.
Is this possible with 24X7?
If so can you tell me how because I am lost. Thanks for any help.

Sure it is possible. However the solution differes for different database systems. Which database do you use?

: I need to run a job based on the return code from a stored procedure.
: I want to exec the stored procedure first then depending on the return run
: the job.
: I know I need to set a dependency but first I need to have 24x7 read the
: return code and decide whether to run the job.
: Is this possible with 24X7?
: If so can you tell me how because I am lost. Thanks for any help.

: Sure it is possible. However the solution differes for different database
: systems. Which database do you use?

The database we are using is Sybase 12.5

Using a JAL Script job you can do the following

Dim rows, number
Dim sql, string
Dim ret_code, number
DatabaseConnect "profile name here"
ConcatEx "DECLARE @ret_code INT\n", &

"EXEC @ret_code = my_proc_name parameters\n",

"SELECT @ret_code AS ret_code INTO #temp", sql

DatabaseExecute sql, rows
DatabaseRetrieve "SELECT ret_code FROM #temp", rows
DatabaseGet 1, 1, ret_code
DatabaseDisconnect

----------------------------------------------
In VBScript type jobs you can use ADO , for example

Dim objCmd
Dim ret_val
Set objCmd = Server.CreateObject("ADODB.Command")

ObjCmd.ActiveConnection = "Provider="
ObjCmd.CommandType = 4 'Stored Procedures
ObjCmd.CommandText = "my_procedure"

ObjCmd.Parameters.Refresh

ObjCmd.Parameters(1).Value = "some value here"
ObjCmd.Parameters(2).Value = "some value here"

ObjCmd.Execute()

Ret_Val = ObjCmd.Parameters(0).Value

Set objCmd = Nothing

-----------------------------------

: The database we are using is Sybase 12.5

Thanks so much for your help. 1 more question. Where
do I set the conditional logic. What I mean is, (see code snippet)

if ret_code = 1

then "do some job"
else
"send email to me that there was an error in the database"

: Using a JAL Script job you can do the following

: Dim rows, number
: Dim sql, string
: Dim ret_code, number
: DatabaseConnect "profile name here"
: ConcatEx "DECLARE @ret_code INT\n", &

: "EXEC @ret_code = my_proc_name parameters\n",

: "SELECT @ret_code AS ret_code INTO #temp", sql

: DatabaseExecute sql, rows
: DatabaseRetrieve "SELECT ret_code FROM #temp", rows
: DatabaseGet 1, 1, ret_code
: DatabaseDisconnect

: ----------------------------------------------
: In VBScript type jobs you can use ADO , for example

: Dim objCmd
: Dim ret_val
: Set objCmd = Server.CreateObject("ADODB.Command")

: ObjCmd.ActiveConnection = "Provider="
: ObjCmd.CommandType = 4 'Stored Procedures
: ObjCmd.CommandText = "my_procedure"

: ObjCmd.Parameters.Refresh

: ObjCmd.Parameters(1).Value = "some value here"
: ObjCmd.Parameters(2).Value = "some value here"

: ObjCmd.Execute()

: Ret_Val = ObjCmd.Parameters(0).Value

: Set objCmd = Nothing

: -----------------------------------

: Thanks so much for your help. 1 more question. Where
: do I set the conditional logic. What I mean is, (see code snippet)

: if ret_code = 1

: then "do some job"
: else
: "send email to me that there was an error in the database"

I am also getting an error when I try to run this script...

Dim rows, number
Dim sql, string
Dim ret_code, number
DatabaseConnect "profile name here"
ConcatEx "DECLARE @ret_code INT\n", &

"EXEC @ret_code = my_proc_name parameters\n",

"SELECT @ret_code AS ret_code INTO #temp", sql

DatabaseExecute sql, rows
DatabaseRetrieve "SELECT ret_code FROM #temp", rows
DatabaseGet 1, 1, ret_code
DatabaseDisconnect

Error "Line 7 Keyword not found"

: I am also getting an error when I try to run this script...

: Dim rows, number
: Dim sql, string
: Dim ret_code, number
: DatabaseConnect "profile name here"
: ConcatEx "DECLARE @ret_code INT\n", &

: "EXEC @ret_code = my_proc_name parameters\n",

: "SELECT @ret_code AS ret_code INTO #temp", sql

: DatabaseExecute sql, rows
: DatabaseRetrieve "SELECT ret_code FROM #temp", rows
: DatabaseGet 1, 1, ret_code
: DatabaseDisconnect

: Error "Line 7 Keyword not found" In the "my proc name is my procedure name
and "profile name" is the connection to my DB i set up

Sorry, it a text wrapping issue in the web page. Make sure the ConcatEx command appears on a single line. Here is the corrected script with the boolean logic included

Dim rows, number
Dim sql, string
Dim ret_code, number
DatabaseConnect "profile name here"
ConcatEx "DECLARE @ret_code INT\n", "EXEC @ret_code = my_proc_name parameters\n", "SELECT @ret_code AS ret_code INTO #temp", sql

DatabaseExecute sql, rows
DatabaseRetrieve "SELECT ret_code FROM #temp", rows
DatabaseGet 1, 1, ret_code
DatabaseDisconnect

ChooseCase ret_code, END_CHOOSE
Case 1
// do something here
Case 2
// do something else
CaseElse
// handle errors here (if other exit code is sign of an error)
END_CHOOSE:

: and "profile name" is the connection to my DB i set up

Ok last question. :-) In the case statement I want to have 2 like you put.

Case 1 If ret_code is 0 then have job go into wait status
for one hour then try again.

Case 2 IF ret_code is 1 then run job in 24x7.

Can you give me the syntax for that? I know I am asking a lot. IF you dont want to I understand
and could you point me in the direction that would have samples for my issues.

In any case thanks for your help.

: Sorry, it a text wrapping issue in the web page. Make sure the ConcatEx
: command appears on a single line. Here is the corrected script with the
: boolean logic included

: Dim rows, number
: Dim sql, string
: Dim ret_code, number
: DatabaseConnect "profile name here"
: ConcatEx "DECLARE @ret_code INT\n", "EXEC @ret_code =
: my_proc_name parameters\n", "SELECT @ret_code AS ret_code INTO
: #temp", sql

: DatabaseExecute sql, rows
: DatabaseRetrieve "SELECT ret_code FROM #temp", rows
: DatabaseGet 1, 1, ret_code
: DatabaseDisconnect

: ChooseCase ret_code, END_CHOOSE
: Case 1
: // do something here
: Case 2
: // do something else
: CaseElse
: // handle errors here (if other exit code is sign of an error)
: END_CHOOSE:

RESTART:
// run your procedure here and get the return code
// see previous messages for details

ChooseCase ret_code, END_CHOOSE

Case 0
// wait 1 hour (3600 seconds) and then re-run procedure
LogAddMessageEx "INFO", V@"job_id", "@V"job_name"", "Got exit code 0, waiting 1 hour"
Wait 3600
GoTo RESTART

Case 1
// run another job
RunJob "[put job id or name here]"

CaseElse
RaiseError "Unexpected return code obtained from stored procedure"

END_CHOOSE:

: Ok last question. :-) In the case statement I want to have 2 like you put.

: Case 1 If ret_code is 0 then have job go into wait status
: for one hour then try again.

: Case 2 IF ret_code is 1 then run job in 24x7.

: Can you give me the syntax for that? I know I am asking a lot. IF you dont
: want to I understand
: and could you point me in the direction that would have samples for my
: issues.

: In any case thanks for your help.