Invoked Schedule Id of the Job : Urgent!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Invoked Schedule Id of the Job : Urgent!

17 posts, Page 1 of 2

1 2 »»

Invoked Schedule Id of the Job : Urgent!

Rate Topic

Display Mode

Topic Options

Author

Message

ganeshmuthuvelu

Posted Monday, August 10, 2009 4:21 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:47 PM
Points: 89, Visits: 290

Hello:
When a job step is being executed from SQL Server Agent, I need to determine the "schedule_id" that invoked the job. For example, a job could have multiple schedules "S1", "S2", "S3" etc., and assuming that the job has a TSQL job step in it, when executing the TSQL job step I need to determine which schedule_id that caused this job to be invoked (to run).

Is there a way to determine this from TSQL. Please help as this is badly needed.

Thanks.

Post #768256

Lynn Pettis

Posted Monday, August 10, 2009 4:36 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 21,630, Visits: 27,485

Can you explain what you are looking for or why it is important to do this?

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #768259

ganeshmuthuvelu

Posted Tuesday, August 11, 2009 7:27 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:47 PM
Points: 89, Visits: 290

I have an reporting application to which the users subscribe to have reports automatically emailed to them. For example, user1 will subscribe for "Report1" to be emailed to him "every 1 hour", user2 will subscribe for "Report2" to be emailed "every 2 hours".

The reporting application will be a custom .exe that will be one of the job steps in SQL Server Agent. The job will have multiple schedules such as "every 1 hour", "every 2 hours", "every day" and so on.

So, when the job runs on the "every 1 hour" schedule, the application has to look for all users who have subscribed for reports "every 1 hour" and email them the reports. Similarly, when the job runs on the "every 2 hours" schedule, the application has to look for all users who have subscribed for reports "every 2 hours" and email them the reports.

It is therefore important for the reporting application to know under which schedule the job is running (or which schedule invoked the job) so that the application can lookup for the reports and users who had subscribed for that schedule.

I hope this helps you understand my requirement.

Thanks for any help provided.

Post #768574

ganeshmuthuvelu

Posted Tuesday, August 11, 2009 11:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:47 PM
Points: 89, Visits: 290

Any help on this will be greatly appreciated.

Thanks.

Post #768851

winash

Posted Tuesday, August 11, 2009 12:44 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883

You can try the sp_help_jobschedule stored procedure(if this gives you what you were looking for)...

Post #768886

ganeshmuthuvelu

Posted Tuesday, August 11, 2009 12:52 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:47 PM
Points: 89, Visits: 290

sp_help_jobschedule provides only the list of schedules for a job. But that's not what I wanted.

I want to know under which schedule the job is currently running. A job can have any number of schedules and I want to know which schedule invoked the job that is currently running.

Please help!

Thanks.

Post #768891

Lynn Pettis

Posted Tuesday, August 11, 2009 1:05 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 21,630, Visits: 27,485

Assumptions:

1. Single Reporting Job: Report Job

2. Multiple Schedules:
Schedule 1, every hour between 6:00 AM and 6:00 PM (i.e 6:00, 7:00, 8:00,...)
Schedule 2, every two hours between 6:00 Am and 6:00 PM (i.e. 6:00, 8:00, 10:00,...)

The job will only run once at 6:00, 7:00, 8:00, 9:00, 10:00, etc. How are you going to know what to send if the same job is run by both schedules? Only one of the schedules will actually fire the job, and I have no idea which. I have a full backup job that is run by two schedules, one that runs the backup once a week, and another on the first of the month. Sometimes the two are the same day. All I know, and care about in this case, is that the job runs, and it does with out failure.

Not sure how you will accomplish this as your requirements are, unfortunately, still a little vague to allow me to really help.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #768899

ganeshmuthuvelu

Posted Tuesday, August 11, 2009 1:24 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:47 PM
Points: 89, Visits: 290

Thanks for the reply. The schedules that I mentioned are just examples. In reality, these schedules will be such that are they are non overlapping. For example, schedule S1 will run every 1 hour on the 20th minute and schedule S2 will run every 2 hours on the 40th minute and so on.

This is how I have the data stored in the database table that has information about what reports to be emailed to users and the schedules.

User1 > Report1 > Schedule (S1)
User1 > Report2 > Schedule (S2)
User2 > Report4 > Schedule (S2)
....
User4 > Report1 > Schedule (S1)

So, when the job runs and if I know that schedule "S1" invoked it, then I will know that - Report1 has to be emailed to User1 and User2.
I hope this makes it clear why I need to know the schedule that invoked the job.

Please help!

Post #768910

Lynn Pettis

Posted Tuesday, August 11, 2009 2:03 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 2:45 AM
Points: 21,630, Visits: 27,485

Let us throw in another monkey into the wrench. What happens if the job is started at 20 minutes after the hour, and for various reasons ends up running until 45 minutes after the hour? Since the job is running when it is supposed to run at 40 minutes after the hour, it won't.

Please, don't tell me that this will never happen, as the first time it does what then? You need to plan for the possibility.

I'd recommend separate jobs and code each job appropriately.

On the other side, it does appear that you should be able to determine which schedule invoked the job, as I have seen it reported in the history for my backup job. I just haven't figured out how it does it yet. I'll do some more research tonight when I have a bit more time.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #768935

ganeshmuthuvelu

Posted Tuesday, August 11, 2009 2:11 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 2:47 PM
Points: 89, Visits: 290

I do understand that possibility but I would like to hear from you on determining the invoked schedule_id of the job. Please do understand that I do need to know the schedule_id while the job is running and not after it is finished, since it will not be of any use.

I do see that the sysjobhistory table does contain the schedule_id of the invoked job but that history table gets the data only after the job is completed, not when it being run.

I eagerly wait for your response.

Thanks.

Post #768943

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions