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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply