How to find the status of a job in sql server 2005 (started,running, finished)

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 Leo Peysakhovich » How to find the status of a job in sql server...

How to find the status of a job in sql server 2005 (started,running, finished)

Rate Topic

Display Mode

Topic Options

Author

Message

Narayana_17

Posted Thursday, October 14, 2010 2:27 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 11, 2013 6:15 AM
Points: 19, Visits: 116

Hi guys,
I stuck in an issue with sql server jobs. and struggling from 4 days to resolve the issue.
I am generating the reports using reporting services subscriptions in SQL Server 2005 standard edition and i m blocked at one stage where i need to send reports to multiple(clients) email address and the parameter values are different to every client. I am changing the parameter settings with the client parametrs and manually running the job of the subscription. but the job the executing with same parameter values and generating same report.

Can anyone tell me how to find the status of the job in sql server, whether it is started, running, executed successfully or not.

Any help is greatly appreciated

Thanks
Narayana

Post #1004175

spaghettidba

Posted Thursday, October 14, 2010 3:23 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

These procedures should help.

-- Returns the status of each job
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, SUSER_SNAME
-- Returns the last execution outcome
EXECUTE msdb.dbo.sp_help_job

The first one is undocumented, but you should find some documentation with a Google search.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1004200

Narayana_17

Posted Thursday, October 14, 2010 5:16 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 11, 2013 6:15 AM
Points: 19, Visits: 116

Thanks for the reply,
I am using the code
EXEC msdb..sp_start_job @job_name = 'Jobname'
inside a cursor for every record of the cursor dataset.
I want to execute the job only after successfully completion of same Job for previous record.
Like a session for each record.

Please help

Thanks
Narayana

Post #1004242

spaghettidba

Posted Thursday, October 14, 2010 6:04 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

OK, so you just have to query sysjobservers:

SELECT last_run_outcome -- 0 = Fail
                        -- 1 = Succeed
                        -- 3 = Cancel
FROM msdb.dbo.sysjobservers sjs
INNER JOIN msdb.dbo.sysjobs sj
	ON sj.job_id = sjs.job_id
WHERE sj.name = 'YOUR JOB NAME'

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1004272

Narayana_17

Posted Thursday, October 14, 2010 6:27 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 11, 2013 6:15 AM
Points: 19, Visits: 116

Hi,
The above srcipt u gave is only for Fail,Success, Cancel.

the code i used is like as follows
_____________________________________________
DECLARE Csr_CursorEmail CURSOR
FOR SELECT top(5) ClientNo, Depot, Service, Sent FROM EmailFileSent WHERE SENT = '' order by clientno

OPEN Csr_CursorEmail
FETCH NEXT FROM Csr_CursorEmail into @ClientNo, @Depot, @Service, @Sent
WHILE (@@FETCH_status =0)
BEGIN
EXEC msdb..sp_start_job @job_name = 'Job Name'

FETCH NEXT FROM Csr_CursorEmail into @ClientNo, @Depot, @Service, @Sent
END --CursorEmail
close Csr_CursorEmail
DEALLOCATE Csr_CursorEmail
___________________________________________________
When i ran the cursor for sum records job is executing successfully, but for some records the job not executing
and giving the error message as

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job C5E96594-9876-42FE-9324-5F6DF8F5CC0B (from User Narayana) refused because the job already has a pending request from User Narayana.

It seems Cursor is forcing to start the JOB while the previous record job is still executing.
I think if i control job like = job have to wait until the previous record job is successfully executed
How can i resolve this problem
Please help.

Narayana

Post #1004289

spaghettidba

Posted Thursday, October 14, 2010 6:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

I don't think you can.
sp_start_job doesn't actually start the job, but tells SQLAgent that there's a start request and then returns immediately.
I think you could add a sleep (WAITFOR) in the loop, but I don't know if this suits your needs. It could slow down things significantly.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1004296

Leju Geevarghese

Posted Thursday, October 14, 2010 6:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, December 29, 2011 8:34 AM
Points: 46, Visits: 154

Not sure why you are looping and then running the job based on the client records.
Instead have a single job that would loop through the client records and run the required tasks.

---------------------------------------------------------------------------------------
It begins by taking the first step.

Post #1004303

Narayana_17

Posted Thursday, October 14, 2010 6:51 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 11, 2013 6:15 AM
Points: 19, Visits: 116

I am looping the job with records because.
I created a subscription to email a report in reporting services. and i observed that a subscription creates a job and parameter fields in the subscriptions table.
I want to send a report to different clients having different parameter values, email address. Top do this for every record i am overwriting the parameter values in subscription table and then executing the job.
so that i can generate the reports with different parameters.
And we are using the standard edition of sql server.

Please help if you know any other way to do this task.

Any help is appreciated

Narayana

Post #1004316

Narayana_17

Posted Thursday, October 14, 2010 7:02 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 11, 2013 6:15 AM
Points: 19, Visits: 116

Hi ,
Actually my task is to send report to different clients to their email address using respective parameter values
The cursor will give a result set with columns like clientno, emailadddress, depot
need to generate a report using values client1,depot1,email1 and to send mail to email1
similarly
need to generate a report using values client2,depot2,email2 and to send mail to email2
need to generate a report using values client3,depot3,email3 and to send mail to email3
Parameter values changes with clientno. and report format is same, data is different to different clients
I am looping the job with records because.
I created a email delivery subscription to email a report in reporting services.
and i observed that a subscription creates a job and with parameter fields and values in the subscriptions table.
I want to send a report to different clients having different parameter values, email address.
To do this for every record i am overwriting the parameter values in subscription table and then executing the job.
so that i can generate the reports with different parameters.
Same like data driven subscription.
And we are using the STANDARD EDITION of sql server.

Please help if you know any other way to do this task. LIKE using webservices or any other method to email reports to different clients

Any help is appreciated

Narayana

Post #1004333

TheSpyder

Posted Tuesday, September 04, 2012 5:08 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, November 11, 2012 2:10 AM
Points: 12, Visits: 44

I know this is a while ago but just in case anyone is looking......
A technique I use is to look in the job history. The history record is only written when the job completes. Try this:

CREATE PROC
RunMyJob
AS
BEGIN
SET NOCOUNT ON

DECLARE @LastID BIGINT

SELECT
@LastID = MAX(S.instance_id)
FROM
msdb.dbo.sysjobhistory S
join
msdb.dbo.sysjobs SJ
ON
S.job_id = SJ.job_id
WHERE
SJ.name = 'MyJob'

EXEC msdb.dbo.sp_start_job 'MyJob'

WHILE NOT EXISTS
(
SELECT
*
FROM
msdb.dbo.sysjobhistory S
join
msdb.dbo.sysjobs SJ
ON
S.job_id = SJ.job_id
WHERE
SJ.name = 'MyJob'
AND
S.instance_id > @LastID
)
BEGIN
WAITFOR DELAY '00:00:01'
END

……more code here
END

so, try every one second to see if the history record is there (you need to make sure there is at least one history record before the sproc runs). Once the history record is there, you code can continue confident that the job has completed (but not necessarily successfully).

Have fun
TheSpyder

Post #1353816

« Prev Topic | Next Topic »

Permissions