SQL Server Examples:
1. SQL Server example of a call from a Stored Procedure:
Add the following to the declaration section of the stored procedure:
DECLARE @v_result integer
DECLARE @v_return_code varchar(1)
DECLARE @v_return_msg varchar(256)
DECLARE @v_job_status_simple varchar(1)
DECLARE @v_job_status_standard varchar(1)
DECLARE @v_job_status_enhanced varchar(2)
Then add the call to the procedure:
EXEC Ws_Job_Status @p_sequence, @p_job_name, @p_task_name, @p_job_id, @p_task_id
, NULL, 'Daily Run', 10, NULL
, @v_return_code OUTPUT, @v_return_msg OUTPUT, @v_result OUTPUT
, @v_job_status_simple OUTPUT, @v_job_status_standard OUTPUT, @v_job_status_enhanced OUTPUT
Perhaps add a check of the result:
IF @v_result <> 1
BEGIN
SET @p_status =@v_result
SET @p_return_msg = 'Failed to check job status. Return Code was ' +@v_return_code
SET @p_return_msg = @p_return_msg + '. ' + RTRIM(@v_return_msg)
END
2. SQL Server example of a call from the command line using sqlcmd (all one line):
sqlcmd -S"SQL SERVER" -WslWarehouse -E -Q"DECLARE @RES integer;DECLARE @RET varchar(1);DECLARE @MSG varchar(55);DECLARE @STAT varchar(1);DECLARE @STAND varchar(1);DECLARE @ENH varchar(2);EXEC Ws_Job_Status 1,'Check Job','Stat',0,0,NULL,'Daily Run',10,NULL,@RET OUTPUT,@MSG OUTPUT,@RES OUTPUT,@STAT OUTPUT,@STAND OUTPUT,@ENH OUTPUT; SELECT @RES,@RET,@MSG,@STAT,@STAND,@ENH"
The following out is typical:
----------- - ------------------------------------------------------- - - --
1 S Job Sequence 6 Failed F F 8
(1 rows affected)
3. SQL Server example of a call from QAD SQL Admin
{Call Ws_Job_Status(1,'Check Job','Stat',0,0,NULL,'Daily Run',10,NULL,?,?,?,?,?,?)};