September 16, 2009 at 12:36 am
Comments posted to this topic are about the item SQL Server Agent Jobs – Displaying Status in an Application
September 16, 2009 at 2:42 am
September 16, 2009 at 3:05 am
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...:-)
September 16, 2009 at 6:22 am
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.
September 16, 2009 at 6:31 am
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 missing
Attaching the codefiles would make this easier to test.
September 16, 2009 at 6:45 am
Here is the code for pr_RunAgentJob:
Sorry about the non-inclusion
CREATE 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 TINYINT
SELECT@TestExists = COUNT(*)
FROMmsdb.dbo.sysjobactivity JA inner join msdb.dbo.sysjobs JO
ONJA.Job_ID = JO.Job_ID
WHERE JO.Name = @JobName
AND start_execution_date IS NOT NULL
AND Stop_execution_date IS NULL
IF @TestExists = 0
BEGIN
EXEC msdb.dbo.sp_start_job @JobName
RETURN 0 -- job called OK
END
ELSE
BEGIN
RETURN 1 -- Job running
END
September 16, 2009 at 7:19 am
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
September 16, 2009 at 7:43 am
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.
September 16, 2009 at 9:31 am
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
September 16, 2009 at 9:33 am
For information sp_start_job is asynchonous, this is the problem .....
September 16, 2009 at 10:40 am
Re <>
It was only a matter of time....
When a co worker sent this to me I replied with
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.
September 16, 2009 at 12:33 pm
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.
DECLARE @jname varchar(200);SET @jname = 'PUT YOUR SQL JOB NAME HERE'-- faked parm
DECLARE @WithinTheLastNseconds int;SET @WithinTheLastNseconds = 6000-- restrict to only jobs within this many seconds; recommended parm
DECLARE
@yyyymmddint int,
@hhmmssch char(8),
@hhmmssint int
SELECT @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 jh
inner join msdb.dbo.sysjobs sj ON jh.Job_ID = sj.Job_ID
where
jh.run_date = @yyyymmddint
and jh.run_time >= @hhmmssint
and sj.name=@jname
order by
sj.name,
jh.instance_id
September 16, 2009 at 12:40 pm
You might want to look at msdb.dbo.sp_get_composite_job_info instead, single row per job, gives you running status.
January 20, 2010 at 8:15 pm
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.
June 10, 2010 at 8:27 am
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!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply