SQL Server Performance Forum – Threads Archive

SQL Server Jobs

Hi,
I am new to SQL server Development/DBA Part. on my work, I am given one task to complete. There are several daily jobs running in our SQL server. There is one Job(Job-A) that I need to look after. as and when that Job-A completes, I need to see if that day is the Last Day of the Month or not? If that is the last day of the month then invoke another Job. Now here is the tricky part. I don’t have to only validate if that day is the Last day of the month. But also I need to make sure that even if that is not the last day of the month, that should be the last day of the Job-A Schedule for the Month. Let me clarify with the example.
Job-A is running for Monday through Thursday in a month.
Now, Lets say, Friday is the Last Working day of this Month. So, if Job-A completes on Thursday for this month, that should be the last day of the month, cause that job is not going to run again in this month. so right away it should invoke Job-B on Thursday. That’s what i want to do. Please Help… Thanks in advance.

Script to check the last day of the month… select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0)) Here is the script find the next run date… SELECT dbo.sysjobs.Name AS ‘Job Name’, ’Job Enabled’ = CASE dbo.sysjobs.Enabled WHEN 1 THEN ‘Yes' WHEN 0 THEN ‘No' END, ’Schedule Enabled’ = CASE dbo.sysjobschedules.Enabled WHEN 1 THEN ‘Yes' WHEN 0 THEN ‘No' END, ’next_run_date’ = CASE next_run_date WHEN 0 THEN null ELSE substring(convert(varchar(15),next_run_date),1,4) + ‘/’ + substring(convert(varchar(15),next_run_date),5,2) + ‘/’ + substring(convert(varchar(15),next_run_date),7,2) END+’ ‘+ CASE len(next_run_time) WHEN 1 THEN cast(’00:00′ –+ Left(right(active_start_time,2),1) +’:0′ + right(active_start_time,1) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 3 THEN cast(’00:0′ + Left(right(next_run_time,3),1) +’:’ + right(next_run_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 4 THEN cast(’00:’ + Left(right(next_run_time,4),2) +’:’ + right(next_run_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 5 THEN cast(‘0′ + Left(right(next_run_time,5),1) +’:’ + Left(right(next_run_time,4),2) +’:’ + right(next_run_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) WHEN 6 THEN cast(Left(right(next_run_time,6),2) +’:’ + Left(right(next_run_time,4),2) +’:’ + right(next_run_time,2) as char (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) END FROM dbo.sysjobs LEFT OUTER JOIN dbo.sysjobschedules ON dbo.sysjobs.job_id = dbo.sysjobschedules.job_id LEFT OUTER JOIN (SELECT job_id, max(run_duration) AS run_duration FROM dbo.sysjobhistory GROUP BY job_id) Q1 ON dbo.sysjobs.job_id = Q1.job_id WHERE dbo.sysjobs.Enabled = 1 and dbo.sysjobschedules.Enabled = 1 –and dbo.sysjobs.Name = ” ORDER BY [next_run_date] desc,[next_run_time] desc – select * from msdb..sysjobschedules MohammedU. Moderator SQL-Server-Performance.com

Thanks Mohammed.. I hardcoded that anyway.. but it was helpful though…
Once again .. Thanks for your quick response.. Regards, Rupesh

]]>