SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invoked Schedule Id of the Job : Urgent!


Invoked Schedule Id of the Job : Urgent!

Author
Message
ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 312
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.



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92847 Visits: 38954
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)
ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 312
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.



ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 312
Any help on this will be greatly appreciated.

Thanks.



winash
winash
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3062 Visits: 1883
You can try the sp_help_jobschedule stored procedure(if this gives you what you were looking for)...



ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 312
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.



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92847 Visits: 38954
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

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)
ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 312
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!



Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

Group: General Forum Members
Points: 92847 Visits: 38954
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

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)
ganeshmuthuvelu
ganeshmuthuvelu
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 312
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search