SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Agent Jobs – Displaying Status in an Application


SQL Server Agent Jobs – Displaying Status in an Application

Author
Message
Adam Aspin
Adam Aspin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1032
Comments posted to this topic are about the item SQL Server Agent Jobs – Displaying Status in an Application
Boreades
Boreades
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 61
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



Adam Aspin
Adam Aspin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1032
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...:-)
Dave Wille
Dave Wille
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 36
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.
F.L
F.L
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 402
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.
Adam Aspin
Adam Aspin
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1135 Visits: 1032
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(*)
FROM msdb.dbo.sysjobactivity JA inner join msdb.dbo.sysjobs JO
ON JA.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
F.L
F.L
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 402
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
Toby White
Toby White
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1511 Visits: 639
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.
pgineste
pgineste
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 82
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 <= 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 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
pgineste
pgineste
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 82
For information sp_start_job is asynchonous, this is the problem .....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search