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 12»»

Invoked Schedule Id of the Job : Urgent! Expand / Collapse
Author
Message
Posted Monday, August 10, 2009 4:21 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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
Posted Monday, August 10, 2009 4:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
Can you explain what you are looking for or why it is important to do this?



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
Posted Tuesday, August 11, 2009 7:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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
Posted Tuesday, August 11, 2009 11:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
Any help on this will be greatly appreciated.

Thanks.



Post #768851
Posted Tuesday, August 11, 2009 12:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Tuesday, August 11, 2009 12:52 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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
Posted Tuesday, August 11, 2009 1:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
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.



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 #768899
Posted Tuesday, August 11, 2009 1:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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
Posted Tuesday, August 11, 2009 2:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
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.



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 #768935
Posted Tuesday, August 11, 2009 2:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 8:48 AM
Points: 90, Visits: 305
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 »

Add to briefcase 12»»

Permissions Expand / Collapse