SQL Server Agent Jobs – Displaying Status in an Application

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Adam Aspin » SQL Server Agent Jobs – Displaying Status in...

15 posts, Page 1 of 2

1 2 »»

SQL Server Agent Jobs – Displaying Status in an Application

Rate Topic

Display Mode

Topic Options

Author

Message

Adam Aspin

Posted Wednesday, September 16, 2009 12:36 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:42 AM
Points: 80, Visits: 846

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

Post #788722

Boreades

Posted Wednesday, September 16, 2009 2:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 7:39 AM
Points: 9, Visits: 44

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

Post #788775

Adam Aspin

Posted Wednesday, September 16, 2009 3:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:42 AM
Points: 80, Visits: 846

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... Smile

Post #788785

Dave Wille

Posted Wednesday, September 16, 2009 6:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 12:21 PM
Points: 5, Visits: 31

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.

Post #788864

F.L

Posted Wednesday, September 16, 2009 6:31 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:36 AM
Points: 76, Visits: 392

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.

Post #788868

Adam Aspin

Posted Wednesday, September 16, 2009 6:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:42 AM
Points: 80, Visits: 846

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

Post #788878

F.L

Posted Wednesday, September 16, 2009 7:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:36 AM
Points: 76, Visits: 392

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

Post #788907

Toby White

Posted Wednesday, September 16, 2009 7:43 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636

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.

Post #788934

pgineste

Posted Wednesday, September 16, 2009 9:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 10:18 AM
Points: 3, Visits: 36

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

Post #789065

pgineste

Posted Wednesday, September 16, 2009 9:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 10:18 AM
Points: 3, Visits: 36

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

Post #789067

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions