I've seen also that sysjobactivity contains several lines per jobs.

For information i have implemented this using two stored procedures:

- the first one called "uspTECH_GET_JOB_RUNSTATE" to retrieve current status:

- the second one called "uspTECH_GET_JOB_FINALSTATUS" to retrieve the final status

(once the job has finished)

- an TSQL sql file named "exec_job_and_wait.sql" that:

execute sp_start_job

loop through uspTECH_GET_JOB_RUNSTATE while job is not finished

finally get the final status with "uspTECH_GET_JOB_FINALSTATUS"

This "exec_job_and_wait.sql" sql file is called for information by a batch dos called:

exec_job_and_wait.bat

I decided to compile those two stored proc. in master database.

For information, current user that invoke this batch dos must have the following rights on SQL Server database:

on master database:

Grant execute on both stored proc

GRANT EXECUTE ON [sys].[xp_sqlagent_enum_jobs]

on msdb database (to allow execution of jobs):

EXEC sp_droprolemember N'SQLAgentReaderRole', N'ToMe'

EXEC sp_droprolemember N'SQLAgentUserRole', N'ToMe'

EXEC sp_droprolemember N'SQLAgentOperatorRole', N'ToMe'

For information, i found those two stored proc. on internet, and i had a little bit adapt them:

Current status:

-- ======================================================================

-- Author:From Internet

-- Create date: 12/03/2009

-- Description:Allow to determin the status of a job (executing or not)

-- History:

-- . :

-- . :

--

-- Parameter:

-- Input:

-- . pIN_JobName : Job name to detect the status

-- . pIN_JobID : Job ID to detect the status

--

-- Output:

-- . pOUT_RunState : return the status of the job

--

-- ======================================================================

ALTER PROCEDURE [dbo].[uspTECH_GET_JOB_RUNSTATE]

@pIN_JobName sysname = NULL,

@pIN_JobID UNIQUEIDENTIFIER = NULL,

@pOUT_RunState int OUTPUT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

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

-- DECLARE

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

DECLARE @vErrCode INT

DECLARE @VErr_Stored_Proc nvarchar(126)

DECLARE @vErrline int

DECLARE @vErrMessage nvarchar(4000)

declare @vRc int

declare @vBeginTranCount int

DECLARE @is_sysadmin INT

DECLARE @vCpt INT

DECLARE @job_owner sysname

DECLARE @wrkJobID UNIQUEIDENTIFIER

DECLARE @wrkJobName sysname

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

-- INITIALIZING

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

-- variable locale commencant par v

SET @vErrCode = 0

set @vRc=0

SET @vErrCode = 0

set @vErrMessage ='';

set @vErrline=0;

set @VErr_Stored_Proc='' ;

set @vBeginTranCount= @@TRANCOUNT

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

-- PROCESSING

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

BEGIN TRY

-- SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

SET @is_sysadmin = 1

SELECT @job_owner = SUSER_SNAME()

IF (@pIN_JobName = N'')

SELECT @pIN_JobName = NULL

-- jobname or job id must be set

IF @pIN_JobName IS NULL

IF @pIN_JobID IS NULL

RETURN (-1) -- Need a job parameter

-- retrieve jobname from jobid

IF @pIN_JobName IS NULL

BEGIN

SELECT

@pIN_JobName = [name]

FROM

msdb.dbo.sysjobs_view

WHERE

job_id = @pIN_JobID

END

ELSE

BEGIN

-- retrieve jobid from jobname

IF @pIN_JobID IS NULL

SELECT

@pIN_JobID = job_ID

FROM

msdb.dbo.sysjobs_view

WHERE

[name] = @pIN_JobName

ELSE

BEGIN

SELECT

@wrkJobID = job_id,

@wrkJobName = [name]

FROM

msdb.dbo.sysjobs_view

WHERE

job_id = @pIN_JobID

IF IsNull(@wrkJobName,'') @pIN_JobName

RETURN (-2) -- @pIN_JobID does not match @pIN_JobName

END

END

IF @pIN_JobID IS NULL OR @pIN_JobName IS NULL

RETURN (-3) -- No job by that name/id on file

CREATE TABLE #xp_results (

job_id UNIQUEIDENTIFIER NOT NULL,

last_run_date int NOT NULL,

last_run_time int NOT NULL,

next_run_date int NOT NULL,

next_run_time int NOT NULL,

next_run_schedule_id int NOT NULL,

requested_to_run int NOT NULL,

request_source int NOT NULL,

request_source_id sysname NULL,

running int NOT NULL, -- Boolean 1 Yes 0 No

current_step int NOT NULL,

current_retry_attempt int NOT NULL,

job_state int NOT NULL

)

INSERT INTO #xp_results

EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner, @pIN_JobID

IF @@ERROR 0

BEGIN

DROP TABLE #xp_results

RETURN (-4)

END

SELECT @pOUT_RunState = running

FROM #xp_results

WHERE

job_id = @pIN_JobID

set @vCpt = coalesce(@@rowcount,0)

IF @vCpt @vBeginTranCount ROLLBACK TRANSACTION

RAISERROR (@vErrMessage,16,1)

END CATCH

RETURN 0

END

Then Final status:

==============

-- ======================================================================

-- Author:From Internet

-- Create date: 23/03/2009

-- Description:Allow to determin the final status of a job once terminated

-- History:

-- . :

-- . :

--

-- Parameter:

-- Input:

-- . pIN_JobName : Job name to detect the status

-- . pIN_JobID : Job ID to detect the status

--

-- Output:

-- . pOUT_FinalStatus : return the status once finished

-- . pOUT_FinalStatus : description associe du status

--

-- ======================================================================

ALTER PROCEDURE [dbo].[uspTECH_GET_JOB_FINALSTATUS]

@pIN_JobName sysname = NULL,

@pIN_JobID UNIQUEIDENTIFIER = NULL,

@pOUT_FinalStatus int OUTPUT,

@pOUT_FinalStatusDesc VARCHAR(1024) OUTPUT,

@pIN_bDescriptionComplete int = 0

AS

BEGIN

DECLARE @is_sysadmin INT

DECLARE @job_owner sysname

DECLARE @wrkJobID UNIQUEIDENTIFIER

DECLARE @wrkJobName sysname

SET NOCOUNT ON

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

SELECT @job_owner = SUSER_SNAME()

IF (@pIN_JobName = N'')

SELECT @pIN_JobName = NULL

-- jobname or job id must be set

IF @pIN_JobName IS NULL

IF @pIN_JobID IS NULL

RETURN (-1) -- Need a job parameter

-- retrieve jobname from jobid

IF @pIN_JobName IS NULL

BEGIN

SELECT

@pIN_JobName = [name]

FROM

msdb.dbo.sysjobs_view

WHERE

job_id = @pIN_JobID

END

ELSE

BEGIN

-- retrieve jobid from jobname

IF @pIN_JobID IS NULL

SELECT

@pIN_JobID = job_ID

FROM

msdb.dbo.sysjobs_view

WHERE

[name] = @pIN_JobName

ELSE

BEGIN

SELECT

@wrkJobID = job_id,

@wrkJobName = [name]

FROM

msdb.dbo.sysjobs_view

WHERE

job_id = @pIN_JobID

IF IsNull(@wrkJobName,'') @pIN_JobName

RETURN (-2) -- @pIN_JobID does not match @pIN_JobName

END

END

IF @pIN_JobID IS NULL OR @pIN_JobName IS NULL

RETURN (-3) -- No job by that name/id on file

-- retourne le resultat du job

SELECT

@pOUT_FinalStatus = sjh.run_status,

@pOUT_FinalStatusDesc =

case @pIN_bDescriptionComplete

-- description succinte

when 0 then

case sjh.run_status

when 0 then 'Failed '

when 1 then 'Succeeded '

when 2 then 'Retry (step only) '

when 3 then 'Canceled '

when 4 then 'In-progress message '

when 5 then 'Unknown '

else 'Unknown '

end

when 1 then

CAST(SUBSTRING(sjh.message,1,1024) AS VARCHAR(1024))

end

FROM

msdb.dbo.sysjobhistory sjh

INNER JOIN

msdb.dbo.sysjobs_view sj

on sjh.job_id = sj.job_id

where sj.name = @pIN_JobName

and exists (

SELECT max_step

FROM (

SELECT max(sjhii.step_id) as max_step

FROM msdb.dbo.sysjobhistory sjhii

INNER JOIN msdb.dbo.sysjobs_view sjii

on sjhii.job_id = sjii.job_id

and sjii.name = @pIN_JobName

where (cast ( sjhii.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjhii.run_time, 6)) in

(

SELECT max(dt) from (

SELECT sjhi.job_id, sjhi.step_id, cast ( sjhi.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjhi.run_time, 6) dt

FROM msdb.dbo.sysjobhistory sjhi

INNER JOIN msdb.dbo.sysjobs_view sji

on sjhi.job_id = sji.job_id

and sji.name = @pIN_JobName

) I2

)

and cast ( sjhii.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjhii.run_time, 6) =

cast ( sjh.run_date AS VARCHAR(10)) + dbo.FN_PAD_ZERO (sjh.run_time, 6)

) I

where sjh.step_id = I.max_step

);

SET @pOUT_FinalStatus = COALESCE(@pOUT_FinalStatus,-1);

RETURN 0

END

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

An finally a TSQL code that i execute from a batch dos, that

- start the job

- execute the first SP until it is finished

- get the final status using the second stored proc.

TSQL stored in exec_job_and_wait.sql:

====================================

-- execute le job passe en parametre

DECLARE @ReturnCode INT

DECLARE @ReturnStatus INT

DECLARE @ReturnStatusDesc VARCHAR(50)

DECLARE @Job sysname

DECLARE @JobState int

DECLARE @Msg NVARCHAR(250)

SET @Job = N'$(V_JOBNAME)'

IF COALESCE(N'$(V_STEP)',N'') = N''

EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=@Job

ELSE

EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=@Job, @step_name=N'$(V_STEP)'

IF @ReturnCode 0

BEGIN

SET @Msg = 'Error execution du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"'

RAISERROR(@Msg,16,1)

END

ELSE

BEGIN

print Convert(VARCHAR(30), GetDate(),121) + ': Lancement job("' + @Job + '")'

WaitFor Delay '00:00:02' -- Allow 2 seconds for job to start

SET @JobState = 1

WHILE @JobState 0 AND @ReturnCode = 0

BEGIN

print Convert(VARCHAR(30), GetDate(),121) + ': Attente Fin job("' + @Job + '")'

WaitFor Delay '00:00:05' -- Check every 05 seconds

Execute @ReturnCode = uspTECH_GET_JOB_RUNSTATE @pIN_JobName = @Job, @pOUT_RunState = @JobState OUTPUT

END

IF @ReturnCode 0

BEGIN

SET @Msg = 'Error attente du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"'

RAISERROR(@Msg,16,1)

END

ELSE

BEGIN

Execute @ReturnCode = uspTECH_GET_JOB_FINALSTATUS @pIN_JobName = @Job,

@pOUT_FinalStatus = @ReturnStatus OUTPUT,

@pOUT_FinalStatusDesc = @ReturnStatusDesc OUTPUT

IF @ReturnCode 0

BEGIN

SET @Msg = 'Error recuperation final status du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"'

RAISERROR(@Msg,16,1)

END

ELSE

print Convert(VARCHAR(30), GetDate(),121) + ': Status Final job("' + @Job + '")="' + + COALESCE(@ReturnStatusDesc,'') + '"'

-- 1 = Success

IF @ReturnStatus 1

BEGIN

SET @Msg = 'Error lors du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '", status final = "' + COALESCE(@ReturnStatusDesc,'') + '"'

RAISERROR(@Msg,16,1)

END

END

END

GO

==========================================

batch dos for information:

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

@echo off

set SQLCMDSERVER=%~1%

set SQLCMDDBNAME=master

set VAR_JOBNAME=%~2%

set VAR_STEP=%~3%

call :mydate

echo.

echo execution du job "%VAR_JOBNAME%" a l'etape "%VAR_STEP%"

sqlcmd -b -i exec_job_and_wait.sql -v V_JOBNAME="%VAR_JOBNAME%" -v V_STEP="%VAR_STEP%" -o "log\exec_job_and_wait_%VAR_JOBNAME%_%mydate%.log"

IF NOT %ERRORLEVEL% == 0 GOTO FIN_ERREUR_CD

:FIN

echo.

ECHO C est la fin correcte du lancement du job "%VAR_JOBNAME%" sur le serveur "%SQLCMDSERVER%" a partir de l'etape "%VAR_STEP%"

goto FINALL

:FIN_ERREUR_CD

echo.

ECHO !!! Erreur lors du lancement du job "%VAR_JOBNAME%" sur le serveur "%SQLCMDSERVER%" a partir de l'etape "%VAR_STEP%"

goto FINALL

:mydate

for /f "tokens=1-3 delims=/" %%a in ('date /T') do set jour=%%a&set mois=%%b&set anneedeb=%%c

for /f "tokens=1-3 delims=:" %%a in ('echo %time%') do set heures=%%a&set minutes=%%b&set secondesdeb=%%c

SET annee=%anneedeb: =%

SET secondes=%secondesdeb: =%

SET mydate=%username%_%annee%%mois%%jour%%heures%%minutes%%secondes%

:FINALL