SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Adam Aspin / SQL Server Agent Jobs – Displaying Status in an Application / Latest Posts

RE: SQL Server Agent Jobs – Displaying Status in an Application

James O. — Thu, 10 Jun 2010 08:27:08 GMT

I was naively trying to run the sp_stop_job in a maintenance plan, which of course, was throwing errors if the job was not running. Thanks to your article, I was able to now craft a simple sp to check the status and only stop it if it's running. Thanks!

RE: SQL Server Agent Jobs – Displaying Status in an Application

core-909671 — Wed, 20 Jan 2010 20:15:57 GMT

I created a stored proc to pull job status (like the article), and it works great in the test environment. As soon as I pushed it to production I get the message "select permissions was denied on object 'sys.jobs'".I'm stumped on a safe way to show the job status.

RE: SQL Server Agent Jobs – Displaying Status in an Application

brettveenstra — Wed, 16 Sep 2009 12:40:18 GMT

You might want to look at msdb.dbo.sp_get_composite_job_info instead, single row per job, gives you running status.

RE: SQL Server Agent Jobs – Displaying Status in an Application

mstjean — Wed, 16 Sep 2009 12:33:18 GMT

You need to tell the user the status of a job (sql job name is known) that was kicked off at a known date and time. Unless there's something else special going on, I don't think you need SSIS to get this done. (does your SSIS keep auto-refreshing your application page or something?)I think you're better off using sysjobhistory and run_status to check job status...The code below (turn it into a USP) will show you job/step details for a named job, run within the last n seconds (where n is set via @WithinTheLastNseconds). You could probably grab the exact time when the user kicked off the job so you could tighten this part up. Call the USP when the user clicks on a "refresh status" button or similar. You may want more or less detail; that's your call. I used a variation of this when I had a 30-40 ETL scripts (as separate sql jobs) to run. They had to run without overlapping, so I created a sql job that would run each job-- and at 30 second intervals, check to see if that job had completed before going on to the next.[code]DECLARE @jname varchar(200);SET @jname = 'PUT YOUR SQL JOB NAME HERE' -- faked parmDECLARE @WithinTheLastNseconds int;SET @WithinTheLastNseconds = 6000 -- restrict to only jobs within this many seconds; recommended parmDECLARE @yyyymmddint int, @hhmmssch char(8), @hhmmssint intSELECT @yyyymmddint = CONVERT(varchar(20),getdate(),112),@hhmmssch = CONVERT(char(8),DATEADD(second,-@WithinTheLastNseconds,getdate()),8)SET @hhmmssint = SUBSTRING(@hhmmssch,1,2) + SUBSTRING(@hhmmssch,4,2) + SUBSTRING(@hhmmssch,7,2)select sj.name, jh.instance_id, jh.run_date, jh.run_time, jh.step_id, jh.step_name, jh.message, jh.run_status, CASE jh.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' ELSE '-abnormal-' END as run_status_desc,jh.*from msdb.dbo.sysjobhistory jhinner join msdb.dbo.sysjobs sj ON jh.Job_ID = sj.Job_IDwhere jh.run_date = @yyyymmddint and jh.run_time >= @hhmmssint and sj.name=@jname order by sj.name, jh.instance_id[/code]

RE: SQL Server Agent Jobs – Displaying Status in an Application

mstjean — Wed, 16 Sep 2009 10:40:38 GMT

[quote]Re << Carrier pigeon has not happened yet, but we are still waiting...>>It was only a matter of time....http://news.bbc.co.uk/1/hi/world/africa/8248056.stm [/quote]When a co worker sent this to me I replied with[quote]However it was found that when suitable security and data protection processes were included, the bird was squished by the chain mail. Debate arose among the researchers when a splinter group opposed a plan to do a feasibility study using Kevlar-based chain mail—claiming CrocodileNet™® would have inherent security and data protection advantages.CrocodileNet advocates pointed out that the Chicken-on-a-Stick/GPS interface used to guide the reptile worked flawlessly and minimized the 78% mortality rate in reception staff at the target site on data delivery. An additional faction claims these losses could be reduced by 37.2% if the rushed to production Chicken-on-a-Stick/GPS interface were to include the Croco-Snooze add on (consisting of a tranquilizer syringe that would be triggered when the GPS detected arrival at target site and/or in the reception area thereof), which had originally been part of the base specification but was dropped late during the rollout due to budgetary constraints. [/quote]

RE: SQL Server Agent Jobs – Displaying Status in an Application

pgineste — Wed, 16 Sep 2009 09:33:48 GMT

For information sp_start_job is asynchonous, this is the problem .....

RE: SQL Server Agent Jobs – Displaying Status in an Application

pgineste — Wed, 16 Sep 2009 09:31:21 GMT

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.batI 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 OUTPUTASBEGIN -- 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 <= 0 BEGIN DROP TABLE #xp_results RETURN (-4) END DROP TABLE #xp_results END TRY BEGIN CATCH SET @vErrCode = @@ERROR set @vErrMessage =ERROR_MESSAGE(); set @vErrline=ERROR_LINE(); set @VErr_Stored_Proc=ERROR_PROCEDURE() ; IF @@TRANCOUNT > @vBeginTranCount ROLLBACK TRANSACTION RAISERROR (@vErrMessage,16,1) END CATCH RETURN 0ENDThen 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 = 0ASBEGIN 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 0END---------------------------------------------------------------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 parametreDECLARE @ReturnCode INTDECLARE @ReturnStatus INTDECLARE @ReturnStatusDesc VARCHAR(50)DECLARE @Job sysnameDECLARE @JobState intDECLARE @Msg NVARCHAR(250)SET @Job = N'$(V_JOBNAME)' IF COALESCE(N'$(V_STEP)',N'') = N'' EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=@JobELSE EXEC @ReturnCode = msdb.dbo.sp_start_job @job_name=@Job, @step_name=N'$(V_STEP)'IF @ReturnCode <> 0BEGIN SET @Msg = 'Error execution du job "' + COALESCE(CAST( @Job AS VARCHAR(100) ),'') + '"' RAISERROR(@Msg,16,1)ENDELSEBEGIN 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 ENDENDGO==========================================batch dos for information:-------------------------@echo offset SQLCMDSERVER=%~1%set SQLCMDDBNAME=masterset VAR_JOBNAME=%~2%set VAR_STEP=%~3%call :mydateecho.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:FINecho.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_CDecho.ECHO !!! Erreur lors du lancement du job "%VAR_JOBNAME%" sur le serveur "%SQLCMDSERVER%" a partir de l'etape "%VAR_STEP%"goto FINALL:mydatefor /f "tokens=1-3 delims=/" %%a in ('date /T') do set jour=%%a&set mois=%%b&set anneedeb=%%cfor /f "tokens=1-3 delims=:" %%a in ('echo %time%') do set heures=%%a&set minutes=%%b&set secondesdeb=%%cSET annee=%anneedeb: =%SET secondes=%secondesdeb: =%SET mydate=%username%_%annee%%mois%%jour%%heures%%minutes%%secondes% :FINALL

RE: SQL Server Agent Jobs – Displaying Status in an Application

Toby White — Wed, 16 Sep 2009 07:43:19 GMT

I find the pr_RunAgentJob procedure a little curious. It checks if the job exists and if it is currently running. Well, it won't run if it doesn't exist and it won't run again if it is already running, so why not run the sp_start_job procedure and just let the code deal with any errors it would throw? Maybe I'm missing something, but I just don't see any added value from the "wrapper" procedure.

RE: SQL Server Agent Jobs – Displaying Status in an Application

F.L — Wed, 16 Sep 2009 07:19:12 GMT

Thanks,However, can this wrapper really work?As Dave Wille says sysjobactivity contains many records for each job and for me most jobs contain values in Start_execution_date and Stop_execution_date even if they are not running./F

RE: SQL Server Agent Jobs – Displaying Status in an Application

Adam Aspin — Wed, 16 Sep 2009 06:45:01 GMT

Here is the code for pr_RunAgentJob:Sorry about the non-inclusionCREATE PROCEDURE [dbo].[pr_RunAgentJob](@JobName VARCHAR(200))AS/*This procedure is a wrapper for the sp_start_job stored procedure.It tests whether the job is running before allowing the job to be re-run*/DECLARE @TestExists TINYINTSELECT @TestExists = COUNT(*)FROM msdb.dbo.sysjobactivity JA inner join msdb.dbo.sysjobs JOON JA.Job_ID = JO.Job_IDWHERE JO.Name = @JobName AND start_execution_date IS NOT NULL AND Stop_execution_date IS NULLIF @TestExists = 0BEGINEXEC msdb.dbo.sp_start_job @JobNameRETURN 0 -- job called OKENDELSEBEGINRETURN 1 -- Job runningEND

RE: SQL Server Agent Jobs – Displaying Status in an Application

F.L — Wed, 16 Sep 2009 06:31:41 GMT

Hi, I would like to try this out but I can't find the "pr_RunAgentJob".Is this similar to the sp_start_job in MS SQL 2005?Also I found that code was missing in the VB.NET part:"'pollResult' is not declared."Guess a Dim pollResult As String = "" is missingAttaching the codefiles would make this easier to test.

RE: SQL Server Agent Jobs – Displaying Status in an Application

Dave Wille — Wed, 16 Sep 2009 06:22:38 GMT

When the article stated that each job is only listed once in the sysjobactivity table I checked the table and found that to be false. I loose interest in articles that are inaccurate and poorly edited.

RE: SQL Server Agent Jobs – Displaying Status in an Application

Adam Aspin — Wed, 16 Sep 2009 03:05:25 GMT

Hilarious this one, thanks for the link.Now, when you consider that I wrote this a couple of months ago, I really should concentrate on guessing the lottery numbers...:-)

RE: SQL Server Agent Jobs – Displaying Status in an Application

Boreades — Wed, 16 Sep 2009 02:42:02 GMT

Re << Carrier pigeon has not happened yet, but we are still waiting...>>It was only a matter of time....http://news.bbc.co.uk/1/hi/world/africa/8248056.stm

SQL Server Agent Jobs – Displaying Status in an Application

Adam Aspin — Wed, 16 Sep 2009 00:36:20 GMT

Comments posted to this topic are about the item [B]SQL Server Agent Jobs – Displaying Status in an Application[/B]