Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find the status of a job in sql server 2005 (started,running, finished) Expand / Collapse
Author
Message
Posted Thursday, October 14, 2010 2:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:47 AM
Points: 19, Visits: 121
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
Posted Thursday, October 14, 2010 3:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
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.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1004200
Posted Thursday, October 14, 2010 5:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:47 AM
Points: 19, Visits: 121
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
Posted Thursday, October 14, 2010 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
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'



--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1004272
Posted Thursday, October 14, 2010 6:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:47 AM
Points: 19, Visits: 121
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
Posted Thursday, October 14, 2010 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 5,014, Visits: 10,517
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.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1004296
Posted Thursday, October 14, 2010 6:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, October 14, 2010 6:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:47 AM
Points: 19, Visits: 121
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
Posted Thursday, October 14, 2010 7:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 3:47 AM
Points: 19, Visits: 121
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
Posted Tuesday, September 4, 2012 5:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 10:52 AM
Points: 12, Visits: 45
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 »

Add to briefcase

Permissions Expand / Collapse