June 6, 2011 at 9:46 am
For the life of me I can't find any script that does that and I can't believe I'm the first one to encounter that problem...
Long story short the agent was shut down for almost 1 day and I need to figure out which jobs to restart and it what sequence.
I'm doing it manually this time but I'd like a script if anyone has 1 to pass along.
TIA.
June 6, 2011 at 10:40 am
Uggh, I can empathize. I have always just knocked these cases out manually.
Do you have any util UDFs in place to help with the INT to DATE/TIME conversions in MSDB? That can ease the pain a bit.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 10:44 am
Nope I don't have that UDF.
I find it very hard to believe that nobody, ever, tried to solve this with a script.
The "good" news is that I only have 15-20 jobs and that I can somewhat rebuild the sequence from memory but it's still not a fun day :pinch:.
June 6, 2011 at 11:07 am
Ninja's_RGR'us (6/6/2011)
Nope I don't have that UDF.
USE msdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.udf_convert_int_date')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.udf_convert_int_date ;
GO
CREATE FUNCTION dbo.udf_convert_int_date (@date_in INT)
RETURNS DATETIME
AS
BEGIN
IF @date_in = 0
RETURN NULL
DECLARE @date_out DATETIME
SET @date_out = CONVERT(DATETIME, CAST(@date_in AS CHAR(8)), 101)
RETURN @date_out
END
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.udf_convert_int_time_1')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.udf_convert_int_time_1 ;
GO
CREATE FUNCTION dbo.udf_convert_int_time_1 (@time_in INT)
RETURNS VARCHAR(8)
AS
BEGIN
IF @time_in = 0
RETURN NULL
DECLARE @time_out VARCHAR(8)
SELECT @time_out = CASE LEN(@time_in)
WHEN 6
THEN LEFT(CAST(@time_in AS VARCHAR(6)), 2) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 3, 2) + ':'
+ RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
WHEN 5
THEN '0' + LEFT(CAST(@time_in AS VARCHAR(6)), 1) + ':' + SUBSTRING(CAST(@time_in AS VARCHAR(6)), 2, 2) + ':'
+ RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
WHEN 4 THEN '00' + ':' + LEFT(CAST(@time_in AS VARCHAR(6)), 2) + ':' + RIGHT(CAST(@time_in AS VARCHAR(6)), 2)
ELSE '00:00:00' --midnight
END --AS converted_time
RETURN @time_out
END
GO
SELECT dbo.udf_convert_int_date(next_run_date),
dbo.udf_convert_int_time_1(next_run_time),
*
FROM dbo.sysjobschedules ;
GO
I find it very hard to believe that nobody, ever, tried to solve this with a script.
I am watching this thread intently 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 6, 2011 at 11:11 am
I know how to do that.... I was under the impression that you also did the time conversion?
As you might know... I don't run ALL my jobs at midnight FLAT :w00t:.
Where's the effing delete button! I just saw the rest of your code post :-P.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply