InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:36:10 GMT20http://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxLynn,Did you get a chance to find this out? This is still a problem for me..Thanks.Thu, 13 Dec 2012 13:19:43 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxI'm also having this problem on SQL 2008 R2. You could write an ugly parsing procedure to extract the schedule_id from the message field in the stepid=0 record of sysjobhistory after the job has ran. That's the closest I've been able to come up with.Tue, 06 Nov 2012 12:44:56 GMTBrian Stoverhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxHi, Have you find solution to get schedule ID when its running as I have facing exact same problem,you were facing.can you please share your solution? Thanks in advance,Tue, 30 Oct 2012 02:58:11 GMTpateldhruvbhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxHi, i also facing this problem, is there any solution for this ?Sun, 07 Oct 2012 07:42:12 GMTx_japanfans5312http://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxThanks for the update.. I will wait to hear from you.Wed, 12 Aug 2009 10:15:07 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxI didn't forget, I just spent most of last night figuring out a problem I was having here at work. Hopefully tonight I may get some time to look into this.Wed, 12 Aug 2009 10:01:42 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxI seem to understand what you are looking for...however I can't find any tables that store the schedule id from which a job is invoked...There is a table - msdb.dbo.sysjobactivity - that stores a record for each job during the job execution (as long as the stop_execution_date is not null for a job in this table the job is currently executing). I tried to see if there is any way to get the schedule that invoked the job when it is being executed but couldn't find anything - except for the horrible hacked code below which I am only attaching as I worked on it for the past hour :-PThe result of this code (best viewed by setting results to text) will display the name of the schedule and schedule id the job is invoked from (while the job is running)And finally - I have no clue if this is going to help you in any way... :ermm:[code="sql"]/* IMPORTANT: Creates a job called TestJob1 (and deletes any existing job with that name first)This job will keep running at intervals of 10 and 23 secondsThis code will keep runningStopping the code will not delete the job - it will still be running - delete the job after stopping the execution*/-- make the job wait for the specified delay and then do some very-- meaningful thing like selecting the current dateDECLARE @JobSql nvarchar(max),@Delay varchar(12);SET @Delay = '00:00:05';SET @JobSql = 'WAITFOR DELAY '+QUOTENAME(@Delay,'''');SET @JobSql = @JobSql + CHAR(13)+CHAR(10)+'SELECT getdate()';DECLARE @JobCmd varchar(200) = @JobSql;-- SET YOUR SERVER NAME HERE!!!DECLARE @srvname sysname = '<<PUTSERVERNAMEHERE>>';IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'TestJob1')BEGIN PRINT 'Deleting job'; EXEC msdb.dbo.sp_delete_job @job_name = 'TestJob1';ENDPRINT 'Creating job';EXEC msdb.dbo.sp_add_job @job_name = 'TestJob1',@owner_login_name='sa';EXEC msdb.dbo.sp_add_jobstep @job_name='TestJob1',@step_name = 'Step1',@command=@JobCmd;EXEC msdb.dbo.sp_add_jobserver @job_name='TestJob1',@server_name=@srvname;-- 10 second interval scheduleEXEC msdb.dbo.sp_add_jobschedule @job_name='TestJob1', @name='TestJob1_Sched10Second', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=10;-- 23 second interval scheduleEXEC msdb.dbo.sp_add_jobschedule @job_name='TestJob1', @name='TestJob1_Sched23Second', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=23; DECLARE @jobname sysname,@jobid uniqueidentifier,@joboutcomemsg nvarchar(1024);SET @jobname = 'TestJob1';SELECT @jobid = job_id FROM msdb.dbo.sysjobs WITH (NOLOCK) WHERE name = @jobname;WHILE(1=1)BEGIN WHILE EXISTS(SELECT 1 FROM msdb.dbo.sysjobactivity WHERE job_id = @jobid) BEGIN IF ((SELECT stop_execution_date FROM msdb.dbo.sysjobactivity WHERE job_id = @jobid )IS NOT NULL) BEGIN IF (@joboutcomemsg IS NOT NULL) SELECT 'Before Break',@joboutcomemsg JobOutcomeMessage; WAITFOR DELAY '00:00:01' BREAK -- job has completed - break out END ELSE BEGIN SELECT @joboutcomemsg = last_outcome_message FROM msdb.dbo.sysjobactivity T1 INNER JOIN msdb.dbo.sysjobservers T2 ON T1.job_id = T2.job_id WHERE T1.job_id = @jobid IF (@joboutcomemsg IS NOT NULL) SELECT 'During continue',@joboutcomemsg JobOutcomeMessage; WAITFOR DELAY '00:00:01' CONTINUE -- continue waiting END ENDEND;[/code]Tue, 11 Aug 2009 14:34:27 GMTwinashhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxI 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.Tue, 11 Aug 2009 14:11:54 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxLet 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.Tue, 11 Aug 2009 14:03:41 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxThanks 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!Tue, 11 Aug 2009 13:24:01 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxAssumptions:1. Single Reporting Job: Report Job2. 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.Tue, 11 Aug 2009 13:05:53 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxsp_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.Tue, 11 Aug 2009 12:52:53 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxYou can try the sp_help_jobschedule stored procedure(if this gives you what you were looking for)...Tue, 11 Aug 2009 12:44:04 GMTwinashhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxAny help on this will be greatly appreciated.Thanks.Tue, 11 Aug 2009 11:51:09 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxI 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.Tue, 11 Aug 2009 07:27:04 GMTganeshmuthuveluhttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxCan you explain what you are looking for or why it is important to do this?Mon, 10 Aug 2009 16:36:25 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic768256-149-1.aspxHello: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.Mon, 10 Aug 2009 16:21:58 GMTganeshmuthuvelu