SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Job Execution Dates-Times Calculation (Past & Fut)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cumbrowski.com
Starting Member

USA
6 Posts

Posted - 01/29/2008 :  20:00:33  Show Profile  Visit Cumbrowski.com's Homepage  Send Cumbrowski.com an AOL message  Send Cumbrowski.com an ICQ Message  Reply with Quote
I have a tricky question to Microsoft SQL Server 2000/2005. I have failed to find a solution to the problem yet and others have failed too. So I thought that I throw it around here, because the fact that a lot of knowledgeable DBAs hang around in this forum.

I am looking for a script/stored procedure that is able to show me upcoming job executions for a selected date/time range based on the current settings for the jobs configured on the Database server.

Example
Date/Time From: 2/16/2007 00:00 a.m. (Friday)
Date/Time To: 2/17/2007 12:00 p.m. (Saturday)

A. Job 1 / Schedule 1 = Week Days, 1:00 a.m.
B. Job 1 / Schedule 2 = Saturdays, 10:00 a.m.
C. Job 2 / Schedule 1 = hourly, between 10:00 a.m. and 1:00 p.m.
D. Job 3 / Schedule 1 = every 3rd Saturday of the month, 5:00 a.m.
E. Job 4 / Schedule 1 = every 1st Friday of the month, 8:00 a.m.

The routine I have in mind would not return Job E, because 2/16/2007 is the 3rd Friday of the month and not the first.

Job A: 2/16/2007 01:00 a.m.
Job B: 2/16/2007 10:00 a.m.
Job B: 2/16/2007 11:00 a.m.
Job B: 2/16/2007 12:00 p.m.
Job B: 2/16/2007 01:00 p.m.
Job D: 2/17/2007 05:00 a.m.
Job A: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 10:00 a.m.
Job B: 2/17/2007 11:00 a.m.
Job B: 2/17/2007 12:00 p.m.

Output

It needs to return the Job ID, the Job Name, the Schedule ID, the Date and the Time.
Disabled Jobs and Schedules are by default excluded from the selection, but an option to include or exclude those would be a bonus.
Information such as the min, max and average execution time would be great too.

Notes

The schedules of Job D and Job B overlap as you can see in my example above.
This happens only once per month though. I have over 20 jobs with sometimes very frequent execution times, like every 5 minutes or every 20 minutes and jobs that run hourly, daily, weekdays only, weekends only, monthly once etc.

Purpose

I want to do two things.
I want to determine where jobs overlap, not just by start date/time, but also by average run time and maximum run time.

I also want to be able to generate a report that shows me what should have been done and what was actually done by the jobs (note on the site, 6 of the jobs create new jobs on the fly for other database servers and this is sometimes not happening properly, without getting any error message. The volume of jobs makes the manual search like a search for a needle in a haystack.)

Findings so far:

I did some digging myself and found following stored procedures that do some of the steps that I need and involved tables for the calculation.

Stored procedures:
- sp_get_schedule_description (in db: msdb) (undocumented stored procedure)
- sp_add_schedule (in db: msdb) http://msdn2.microsoft.com/en-us/library/ms187320.aspx

Tables:
- msdb.sysjobs
- msdb.sysjobschedules
- msdb.sysjobhistory

It is not a problem to determine the next execution of a job, but that is not what I need, anyway.
The problem is that it does not help you to determine all upcoming execution times, if the selected timeframe is long enough that SQL Server executes the job more than once.

There is no way around using the sysjobschedules table and calculate the execution dates and times based on the configured settings. See the Stored procedure: sp_get_schedule_description.

That one breaks down nicely the settings as documented for sp_add_schedule at
http://msdn2.microsoft.com/en-us/library/ms187320.aspx, but it does not allow the determination of the exact upcoming dates and times when the job is supposed to be executed.

Another Example
If there is only one job scheduled to run

1) every 5 minutes,
2) on every weekday
3) between 1:30pm and 2:00pm

You would get the following results

1) start date/time 7/7/2007 12:00pm, end date/time 7/8/2007 2:00pm

nothing, because the 7/7/2007 and 7/8/2007 are on the weekend

2) start date/time 7/5/2007 12:00pm, end date/time 7/5/2007 1:45pm

7/5/2007 1:30pm
7/5/2007 1:35pm
7/5/2007 1:40pm
7/5/2007 1:45pm

3) start date/time 7/5/2007 1:45pm, end date/time 7/6/2007 3:00pm

7/5/2007 1:45pm
7/5/2007 1:50pm
7/5/2007 1:55pm
7/5/2007 2:00pm
7/6/2007 1:30pm
7/6/2007 1:35pm
7/6/2007 1:40pm
7/6/2007 1:45pm
7/6/2007 1:50pm
7/6/2007 1:55pm
7/6/2007 2:00pm

Now SQL has a lot more configuration options for the scheduler.

And don't forget that you can have more than one schedule record for any single job, including no-schedule record (which would not interest me).

Autom. when SQL starts freq_type=64
Starts when CPU idle freq_type=128

One Time On Date mm/dd/yyyy at time: hh:mm:ss am/pm freq_type=1
or
Recurring

Occurs
Daily freq_type=4
Every x day(s) freq_interval=x
Weekly freq_type=8
Every x week(s) on freq_recurrence_factor=x

Mo [ ], Tu [ ], We [ ], Th [ ],
Fr [ ], Sa [ ], Su [ ],

1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday. Examples: Su and Mo enabled = 3 (1 (Su) + 2 (Mo)), Mo, We and Fr enabled = 42 (2 (Mo) + 8 (We) + 32 (Fr))
Monthly freq_type=16
Day X of every Y month(s) freq_interval=X
freq_recurrence_factor=Y
or

The 1st,2nd,3rd,4th,LAST WEEKDAY freq_type=32
of every Y month(s)

freq_relative_interval=1,2,4 (3rd),8 (4th),16(last)
freq_interval= 1=Su,2=Mo,3=Tu,4=We,5=Th,6=Fr,7=Sa,8=Day,9=Weekday,10=Weekend day
freq_recurrence_factor=Y

Occurs Once at hh:mm:ss AM/PM freq_subday_type=0x1
or
occurs Every X Hours/Minutes
Starting: hh:mm:ss A/PM freq_subday_type=0x4 (minutes) or 0x8 (hours)
Ending: hh:mm:ss A/PM freq_subday_interval=X
active_start_time
active_end_time

Start Date mm/dd/yyy End Date mm/dd/yyyy active_start_date
or active_end_date
No End Date active_end_date=99991231

Does anybody has a script that does that or several individual scripts that would have to be combined to do what I want to do?

Thanks. I appreciate it.

Cheers!

Carsten Cumbrowski
http://www.sqlhunt.com/

billsox
Yak Posting Veteran

74 Posts

Posted - 05/24/2008 :  19:35:33  Show Profile  Reply with Quote
Carsten -- Oddly enough, you describe the very same thing I need. Were you able to make any progress since you first posted your question?

Bill
Go to Top of Page

Cumbrowski.com
Starting Member

USA
6 Posts

Posted - 05/26/2008 :  09:22:57  Show Profile  Visit Cumbrowski.com's Homepage  Send Cumbrowski.com an AOL message  Send Cumbrowski.com an ICQ Message  Reply with Quote
quote:
Originally posted by billsox

Carsten -- Oddly enough, you describe the very same thing I need. Were you able to make any progress since you first posted your question?

Bill



Hi Bill,

Nope.... I posted it here, at SQLSeverCentral and at Expert-Exchange. I had at least some responses at the other places, which helped me to refine my explanation of the problem, but didn't got much answers to solve the problem. I also emailed two editors who write for SQL Server Magazine. Both did not even respond to my email :(. Not very nice.

It is good to know that I am not the only one who came across this. I became self employed since then and do not have to worry about the issue. I would still like to find an answer, just to help my former employer out and because I refuse to believe that there is nothing out there that tackles the issue.

If you should come across something, let me know. I will do the same in return.

Cheers!

Carsten Cumbrowski
http://www.sqlhunt.com/

Edited by - Cumbrowski.com on 05/26/2008 09:24:57
Go to Top of Page

billsox
Yak Posting Veteran

74 Posts

Posted - 05/26/2008 :  16:23:02  Show Profile  Reply with Quote
Will do. I'm surprised to hear how little response you got. I guess we're in a small minority of folks that need to know how to calculate past and future job execution dates/times. I tried to see how Microsoft calculates the "next_run_date" and "next_run_time" fields in the sp_help_job stored procedure. But it looks like it calls an extended stored procedure which calls a .dll file to perform the calculations. A dead end, as far as I'm concerned. I think I'm faced with either creating a much less elegant solution (which I'm dreading) or learning how the experts manage bitmapping and devising my own solution. Either way, I'll need some good luck!

If anyone else can help us solve this dilemma, please post! Many thanks in advance.

Bill
Go to Top of Page

Cumbrowski.com
Starting Member

USA
6 Posts

Posted - 05/26/2008 :  18:20:03  Show Profile  Visit Cumbrowski.com's Homepage  Send Cumbrowski.com an AOL message  Send Cumbrowski.com an ICQ Message  Reply with Quote
quote:
Originally posted by billsox

Will do. I'm surprised to hear how little response you got. I guess we're in a small minority of folks that need to know how to calculate past and future job execution dates/times. I tried to see how Microsoft calculates the "next_run_date" and "next_run_time" fields in the sp_help_job stored procedure. But it looks like it calls an extended stored procedure which calls a .dll file to perform the calculations. A dead end, as far as I'm concerned. I think I'm faced with either creating a much less elegant solution (which I'm dreading) or learning how the experts manage bitmapping and devising my own solution. Either way, I'll need some good luck!

If anyone else can help us solve this dilemma, please post! Many thanks in advance.

Bill



Hi Bill,

If you know T-SQL well and able to write stored procedures and user defined functions, you could write it yourself. Its a lot to program though.

I even offered money for somebody to write it. Not enough though. $100 and the author remains the resell rights. Fair deal for a student or a guy from india to do it in his spare time. I provided already everything that is needed to figure it out and write the code. I have some more in a word document, if you are interested. You can shoot me an email.

My contact information are available here
http://www.cumbrowski.com/contact

I have it in Word 2007 format, but can save it in a different format, if you don't have Office 2007.

Cheers!

Carsten Cumbrowski
http://www.sqlhunt.com/
Go to Top of Page

Cumbrowski.com
Starting Member

USA
6 Posts

Posted - 05/26/2008 :  18:26:27  Show Profile  Visit Cumbrowski.com's Homepage  Send Cumbrowski.com an AOL message  Send Cumbrowski.com an ICQ Message  Reply with Quote
Hi Bill,

Here are the links to the SQL Server Central and Experts-Exchange discussions. Just FYI. Maybe some of the comments are useful to you.
I think you need a full account for the Experts-Exchange details.

I can paste the stuff into a document as well, if you do not have an account and cannot access the full content.

http://www.sqlservercentral.com/Forums/Topic378529-149-1.aspx
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22393535.html

Cheers!

Carsten Cumbrowski
http://www.sqlhunt.com/
Go to Top of Page

SHardy
Starting Member

United Kingdom
35 Posts

Posted - 11/18/2008 :  10:17:13  Show Profile  Reply with Quote
I know this is an old topic, so apologies if you have already found the solution. I stumbled across this topic whilst trying to figure out a different issue. However, I have a function in place that I believe should give you the required results. Unfortunately I do not have the original source bookmarked and as a result I am unable to give credit to the original "author".

Run the following against your msdb to create a user function. When calling the function you will need to suplpy the start & end date/time for your required period.

USE [msdb]
GO
/****** Object:  UserDefinedFunction [dbo].[udfGetScheduleTimes]    Script Date: 11/18/2008 15:14:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfGetScheduleTimes]
(
	@startDate DATETIME,
	@endDate DATETIME
)

RETURNS @t TABLE (
		scheduleID INT NOT NULL,
		serverName SYSNAME NOT NULL,
		jobName SYSNAME NOT NULL,
		jobDescription NVARCHAR(512) NOT NULL,
		scheduleName SYSNAME NOT NULL,
		categoryName SYSNAME NOT NULL,
		infoDate DATETIME NOT NULL,
		startTime DATETIME NOT NULL,
		endTime DATETIME NOT NULL,
		jobEnabled INT NOT NULL,
		scheduleEnabled INT NOT NULL)

AS

BEGIN

-- Create a tally table. If you already have one of your own please use that instead.
DECLARE	@tallyNumbers TABLE
		(
			num SMALLINT PRIMARY KEY CLUSTERED
		)

DECLARE	@index SMALLINT

SET	@index = 1

WHILE @index <= 8640
	BEGIN
		INSERT	@tallyNumbers
			(
				num
			)
		VALUES	(
				@index
			)

		SET	@index = @index + 1
	END

-- Create a staging table for jobschedules
DECLARE	@jobSchedules TABLE
		(
			rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			serverName SYSNAME NOT NULL,
			jobName SYSNAME NOT NULL,
			jobDescription NVARCHAR(512) NOT NULL,
			scheduleName SYSNAME NOT NULL,
			scheduleID INT NOT NULL,
			categoryName SYSNAME NOT NULL,
			freq_type INT NOT NULL,
			freq_interval INT NOT NULL,
			freq_subday_type INT NOT NULL,
			freq_subday_interval INT NOT NULL,
			freq_relative_interval INT NOT NULL,
			freq_recurrence_factor INT NOT NULL,
			startDate DATETIME NOT NULL,
			startTime DATETIME NOT NULL,
			endDate DATETIME NOT NULL,
			endTime DATETIME NOT NULL,
			jobEnabled INT NOT NULL,
			scheduleEnabled INT NOT NULL
		)


-- Popoulate the staging table for JobSchedules with SQL Server 2005 and SQL Server 2008
INSERT		@JobSchedules
		(
			serverName,
			jobName,
			jobDescription,
			scheduleName,
			scheduleID,
			categoryName,
			freq_type,
			freq_interval,
			freq_subday_type,
			freq_subday_interval,
			freq_relative_interval,
			freq_recurrence_factor,
			startDate,
			startTime,
			endDate,
			endTime,
			jobEnabled,
			scheduleEnabled
		)
SELECT		srv.srvname,
		sj.name,
		COALESCE(sj.description, ''),
		ss.name,
		ss.schedule_id,
		sc.name,
		ss.freq_type,
		ss.freq_interval,
		ss.freq_subday_type,
		ss.freq_subday_interval,
		ss.freq_relative_interval,
		ss.freq_recurrence_factor,
		COALESCE(STR(ss.active_start_date, 8), CONVERT(CHAR(8), GETDATE(), 112)),
		STUFF(STUFF(REPLACE(STR(ss.active_start_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
		STR(ss.active_end_date, 8),
		STUFF(STUFF(REPLACE(STR(ss.active_end_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':'),
		sj.enabled,
		ss.enabled
FROM		msdb..sysschedules AS ss
INNER JOIN	msdb..sysjobschedules AS sjs ON sjs.schedule_id = ss.schedule_id
INNER JOIN	msdb..sysjobs AS sj ON sj.job_id = sjs.job_id
INNER JOIN	sys.sysservers AS srv ON srv.srvid = sj.originating_server_id
INNER JOIN	msdb..syscategories AS sc ON sc.category_id = sj.category_id
WHERE		ss.freq_type IN (1, 4, 8, 16, 32)
ORDER BY	srv.srvname,
		sj.name,
		ss.name

-- Deal with first, second, third, fourth and last occurence
DECLARE	@tempStart DATETIME,
	@tempEnd DATETIME

SELECT	@tempStart = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @startDate), '19000101'),
	@TempEnd = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @endDate), '18991231')

DECLARE	@dayInformation TABLE
		(
			infoDate DATETIME PRIMARY KEY CLUSTERED,
			weekdayName VARCHAR(9) NOT NULL,
			statusCode INT NOT NULL,
			lastDay TINYINT DEFAULT 0
		)

WHILE @tempStart <= @tempEnd
	BEGIN
		INSERT	@dayInformation
			(
				infoDate,
				weekdayName,
				statusCode
			)
		SELECT	@tempStart,
			DATENAME(WEEKDAY, @tempStart),
			CASE
				WHEN DATEPART(DAY, @tempStart) BETWEEN 1 AND 7 THEN 1
				WHEN DATEPART(DAY, @tempStart) BETWEEN 8 AND 14 THEN 2
				WHEN DATEPART(DAY, @tempStart) BETWEEN 15 AND 21 THEN 4
				WHEN DATEPART(DAY, @tempStart) BETWEEN 22 AND 28 THEN 8
				ELSE 0
			END

		SET	@tempStart = DATEADD(DAY, 1, @tempStart)
	END

UPDATE		di
SET		di.statusCode = di.statusCode + 16
FROM		@dayInformation AS di
INNER JOIN	(
			SELECT		DATEDIFF(MONTH, '19000101', infoDate) AS theMonth,
					DATEPART(DAY, MAX(infoDate)) - 6 AS theDay
			FROM		@dayInformation
			GROUP BY	DATEDIFF(MONTH, '19000101', infoDate)
		) AS x ON x.theMonth = DATEDIFF(MONTH, '19000101', di.infoDate)
WHERE		DATEPART(DAY, di.infoDate) >= x.theDay

UPDATE		di
SET		di.lastDay = 16
FROM		@dayInformation AS di
INNER JOIN	(
			SELECT		DATEDIFF(MONTH, '19000101', infoDate) AS theMonth,
					MAX(infoDate) AS theDay
			FROM		@dayInformation
			GROUP BY	DATEDIFF(MONTH, '19000101', infoDate)
		) AS x ON x.theMonth = DATEDIFF(MONTH, '19000101', di.infoDate)
WHERE		di.infoDate = x.theDay

UPDATE	@dayInformation
SET	lastDay = DATEPART(DAY, infoDate)
WHERE	DATEPART(DAY, infoDate) BETWEEN 1 AND 4

-- Stage all individual schedule times
DECLARE	@scheduleTimes TABLE
		(
			rowID INT NOT NULL,
			infoDate DATETIME NOT NULL,
			startTime DATETIME NOT NULL,
			endTime DATETIME NOT NULL,
			waitSeconds INT DEFAULT 0
		)


-- Insert one time only schedules
INSERT	@scheduleTimes
	(
		rowID,
		infoDate,
		startTime,
		endTime
	)
SELECT	rowID,
	startDate,
	startTime,
	endTime
FROM	@jobSchedules
WHERE	freq_type = 1
	AND startDate >= @StartDate
	AND startDate <= @EndDate

-- Insert daily schedules
INSERT		@scheduleTimes
		(
			rowID,
			infoDate,
			startTime,
			endTime,
			waitSeconds
		)
SELECT		js.rowID,
		di.infoDate,
		js.startTime,
		js.endTime,
		CASE js.freq_subday_type
			WHEN 1 THEN 0
			WHEN 2 THEN js.freq_subday_interval
			WHEN 4 THEN 60 * js.freq_subday_interval
			WHEN 8 THEN 3600 * js.freq_subday_interval
		END
FROM		@jobSchedules AS js
INNER JOIN	@dayInformation AS di ON di.infoDate >= @startDate
			AND di.infoDate <= @endDate
WHERE		js.freq_type = 4
		AND DATEDIFF(DAY, js.startDate, di.infoDate) % js.freq_interval = 0

-- Insert weekly schedules
INSERT		@scheduleTimes
		(
			rowID,
			infoDate,
			startTime,
			endTime,
			waitSeconds
		)
SELECT		js.rowID,
		di.infoDate,
		js.startTime,
		js.endTime,
		CASE js.freq_subday_type
			WHEN 1 THEN 0
			WHEN 2 THEN js.freq_subday_interval
			WHEN 4 THEN 60 * js.freq_subday_interval
			WHEN 8 THEN 3600 * js.freq_subday_interval
		END
FROM		@jobSchedules AS js
INNER JOIN	@dayInformation AS di ON di.infoDate >= @startDate
			AND di.infoDate <= @endDate
WHERE		js.freq_type = 8
		AND 1 =	CASE
				WHEN js.freq_interval & 1 = 1 AND di.weekdayName = 'Sunday' THEN 1
				WHEN js.freq_interval & 2 = 2 AND di.weekdayName = 'Monday' THEN 1
				WHEN js.freq_interval & 4 = 4 AND di.weekdayName = 'Tuesday' THEN 1
				WHEN js.freq_interval & 8 = 8 AND di.weekdayName = 'Wednesday' THEN 1
				WHEN js.freq_interval & 16 = 16 AND di.weekdayName = 'Thursday' THEN 1
				WHEN js.freq_interval & 32 = 32 AND di.weekdayName = 'Friday' THEN 1
				WHEN js.freq_interval & 64 = 64 AND di.weekdayName = 'Saturday' THEN 1
				ELSE 0
			END
		AND (DATEDIFF(DAY, js.startDate, di.infoDate) / 7) % js.freq_recurrence_factor = 0

-- Insert monthly schedules
INSERT		@scheduleTimes
		(
			rowID,
			infoDate,
			startTime,
			endTime,
			waitSeconds
		)
SELECT		js.rowID,
		di.infoDate,
		js.startTime,
		js.endTime,
		CASE js.freq_subday_type
			WHEN 1 THEN 0
			WHEN 2 THEN js.freq_subday_interval
			WHEN 4 THEN 60 * js.freq_subday_interval
			WHEN 8 THEN 3600 * js.freq_subday_interval
		END
FROM		@jobSchedules AS js
INNER JOIN	@dayInformation AS di ON di.infoDate >= @startDate
			AND di.infoDate <= @endDate
WHERE		js.freq_type = 16
		AND DATEPART(DAY, di.infoDate) = js.freq_interval
		AND DATEDIFF(MONTH, js.startDate, di.infoDate) % js.freq_recurrence_factor = 0

-- Insert monthly relative schedules
INSERT		@scheduleTimes
		(
			rowID,
			infoDate,
			startTime,
			endTime,
			waitSeconds
		)
SELECT		js.rowID,
		di.infoDate,
		js.startTime,
		js.endTime,
		CASE js.freq_subday_type
			WHEN 1 THEN 0
			WHEN 2 THEN js.freq_subday_interval
			WHEN 4 THEN 60 * js.freq_subday_interval
			WHEN 8 THEN 3600 * js.freq_subday_interval
		END
FROM		@jobSchedules AS js
INNER JOIN	@dayInformation AS di ON di.infoDate >= @startDate
			AND di.infoDate <= @endDate
WHERE		js.freq_type = 32
		AND 1 =	CASE
				WHEN js.freq_interval = 1 AND di.weekdayName = 'Sunday' THEN 1
				WHEN js.freq_interval = 2 AND di.weekdayName = 'Monday' THEN 1
				WHEN js.freq_interval = 3 AND di.weekdayName = 'Tuesday' THEN 1
				WHEN js.freq_interval = 4 AND di.weekdayName = 'Wednesday' THEN 1
				WHEN js.freq_interval = 5 AND di.weekdayName = 'Thursday' THEN 1
				WHEN js.freq_interval = 6 AND di.weekdayName = 'Friday' THEN 1
				WHEN js.freq_interval = 7 AND di.weekdayName = 'Saturday' THEN 1
				WHEN js.freq_interval = 8 AND js.freq_relative_interval = di.lastDay THEN 1
				WHEN js.freq_interval = 9 AND di.weekdayName NOT IN ('Sunday', 'Saturday') THEN 1
				WHEN js.freq_interval = 10 AND di.weekdayName IN ('Sunday', 'Saturday') THEN 1
				ELSE 0
			END
		AND di.statusCode & js.freq_relative_interval = js.freq_relative_interval
		AND DATEDIFF(MONTH, js.startDate, di.infoDate) % js.freq_recurrence_factor = 0

-- Get the daily recurring schedule times
INSERT		@scheduleTimes
		(
			rowID,
			infoDate,
			startTime,
			endTime,
			waitSeconds
		)
SELECT		st.rowID,
		st.infoDate,
		DATEADD(SECOND, tn.num * st.waitSeconds, st.startTime),
		st.endTime,
		st.waitSeconds
FROM		@scheduleTimes AS st
CROSS JOIN	@tallyNumbers AS tn
WHERE		tn.num * st.waitSeconds <= DATEDIFF(SECOND, st.startTime, st.endTime)
		AND st.waitSeconds > 0

-- Present the result
INSERT @t (scheduleID,
		serverName,
		jobName,
		jobDescription,
		scheduleName,
		categoryName,
		infoDate,
		startTime,
		endTime,
		jobEnabled,
		scheduleEnabled)
SELECT		js.scheduleID,
		js.serverName,
		js.jobName,
		js.jobDescription,
		js.scheduleName,
		js.categoryName,
		st.infoDate,
		st.startTime,
		st.endTime,
		js.jobEnabled,
		js.scheduleEnabled
FROM		@scheduleTimes AS st
INNER JOIN	@jobSchedules AS js ON js.rowID = st.rowID
WHERE		js.jobEnabled = 1
		AND js.scheduleEnabled = 1

RETURN

END


Hope this is of use to you.

Regards,
Simon
Go to Top of Page

Cumbrowski.com
Starting Member

USA
6 Posts

Posted - 11/19/2008 :  03:10:02  Show Profile  Visit Cumbrowski.com's Homepage  Send Cumbrowski.com an AOL message  Send Cumbrowski.com an ICQ Message  Reply with Quote
quote:
Originally posted by SHardy

I know this is an old topic, so apologies if you have already found the solution. I stumbled across this topic whilst trying to figure out a different issue. However, I have a function in place that I believe should give you the required results. Unfortunately I do not have the original source bookmarked and as a result I am unable to give credit to the original "author".



Hi Simon,

thanks a lot for the code. I glimpsed over it and it seems exactly to do what I was looking for, or at least close enough that it would only require some minor tweaking.

I never got an answer anywhere so there are no apologies necessary for posting one late. :)

This is a thread that explains a problem in great detail, but remained without a useful answer. There are several threads, which have the same fate. People who have the problem and find this thread today will be glad, because it actually helps them.

The problem did not go away with SQL 2005 or SQL 2008 either, from what I can tell, so its also not obsolete at all.

Cheers!


Carsten Cumbrowski
MS SQL Server Resources Meta Search Engine
http://www.sqlhunt.com/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 11/19/2008 :  03:46:05  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SHardy, If you are going to ripp off my blog post, please post a link instead to this blog entry
http://weblogs.sqlteam.com/peterl/archive/2008/10/10/Keep-track-of-all-your-jobs-schedules.aspx



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SHardy
Starting Member

United Kingdom
35 Posts

Posted - 11/19/2008 :  05:35:13  Show Profile  Reply with Quote
Peso, I did not intentionally "rip off" your blog post. In fact, before posting, I did do quite a bit of Google-ing to try and find it so as to post the link and properly credit you. Unfortunately, I was unably to find it & could not remember what I had originally searched for to find your blog entry in the first place.

Hence my comment "Unfortunately I do not have the original source bookmarked and as a result I am unable to give credit to the original 'author'".

I made no attempt to pass it off as my own work, and made it quite clear that I had found the solution elsewhere.

However, as you are reading this topic, I would like to take the opportunity to thank you for that particular piece of code, and is was absolutely spot on for what I required. TBH, I was getting in a bit of a mess with some of the schedule options. Hopefully the OP and billsox will find your work just as useful.

Regards,
Simon
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 11/19/2008 :  05:37:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No worries. This guy is worse. Much worse
http://forums.asp.net/p/1339487/2754586.aspx

EDIT: Wow! 100 dollars for that piece of code?


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 11/19/2008 05:40:13
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/19/2008 :  08:23:01  Show Profile  Reply with Quote
Sorry Peso, but this is at least partly your own fault for not including your name in comments at the top of the code.
If they removed your credit before posting the code, that would be clearly wrong. But they may just be copying code they got from you a long time ago and may not know where the original source was.

If it is not practically useful, then it is practically useless.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 11/19/2008 :  08:25:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
October 10, 2008 is not that long ago.
But I do get your point!

EDIT: Blog post is edited to include source url


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 11/19/2008 08:34:37
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/19/2008 :  11:39:18  Show Profile  Reply with Quote
quote:
Originally posted by Peso

No worries. This guy is worse. Much worse
http://forums.asp.net/p/1339487/2754586.aspx

EDIT: Wow! 100 dollars for that piece of code?


E 12°55'05.63"
N 56°04'39.26"



That signature in the post seems very familiar to me within sqlteam forums.
Go to Top of Page

Cumbrowski.com
Starting Member

USA
6 Posts

Posted - 11/24/2008 :  17:31:49  Show Profile  Visit Cumbrowski.com's Homepage  Send Cumbrowski.com an AOL message  Send Cumbrowski.com an ICQ Message  Reply with Quote
Peso, adding a URL to the blog post or at least the blog itself would be smart too. You could remove parts of the extended documentation from the source code and make it available via a special blog post. The main code then simply includes a link to it. That keeps the code lean and clean, has a reference to an extended documentation and refers to the source by doing it.

Simon mentioned that he wanted to give credits, but was not able to find the source anymore.

What I find interesting is the fact that you Peso found out about the posting of the script and solution so quickly after it was posted here and SQLServerCentral, while the original threads with the open problem remained unanswered for good 1 1/2 years. A simple link to your blog post with the script a few weeks earlier would have been nice, but didn't happen.

Now credits were given properly, even enhancements suggested at the other forum, plus Simon apologized, so everybody should be happy now :)

Cheers! and no hard feelings.

Carsten Cumbrowski
MS SQL Server Resources Meta Search Engine
http://www.sqlhunt.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000