Guys,
This is a two-pronged question: Scenario:
I have a script to query MSDB and get me details of job schedules. Obviously, the tables differ from SQL 2000 to SQL 2005. Hence, I want to check the version running on the box and query accordingly. Now the questions:
Question 1:
This is what I am doing.
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'
BEGIN
PRINT 'SQL 2000'--Actual Code Goes Here
END
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'
BEGIN
PRINT 'SQL 2005'--Actual Code Goes Here
END
Is there a better way of doing this?
Question 2:
Though the above script runs fine on both 2000 and 2005 boxes, when I replace the "Print.." statements with my actual code, it runs fine on a 2000 box, but when executed on a 2005 box,tries to run the code block meant for 2000 and returns errors.
Here is the actual code:
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Check SQL Server Version
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'
BEGIN
SELECT @@SERVERNAME
,sysjobs.name
,dbo.udf_schedule_description(dbo.sysschedules.freq_type, dbo.sysschedules.freq_interval,
dbo.sysschedules.freq_subday_type, dbo.sysschedules.freq_subday_interval, dbo.sysschedules.freq_relative_interval,
dbo.sysschedules.freq_recurrence_factor, dbo.sysschedules.active_start_date, dbo.sysschedules.active_end_date,
dbo.sysschedules.active_start_time, dbo.sysschedules.active_end_time) AS [Schedule Description]
, CONVERT(CHAR(8), CASE WHEN LEN(msdb.dbo.sysschedules.Active_Start_Time) = 3
THEN CAST('00:0' + LEFT(msdb.dbo.sysschedules.Active_Start_Time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
2, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysschedules.Active_Start_Time) = 4
THEN CAST('00:'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
1, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
3, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysschedules.Active_Start_Time) = 5
THEN CAST('0' + LEFT(msdb.dbo.sysschedules.Active_Start_Time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
2, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
4, 2) AS VARCHAR(8))
WHEN msdb.dbo.sysschedules.Active_Start_Time = 0
THEN '00:00:00'
ELSE CAST(LEFT(msdb.dbo.sysschedules.Active_Start_Time, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
3, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.Active_Start_Time AS VARCHAR(6)),
5, 2) AS VARCHAR(8))
END, 108) AS Start_Time,
CONVERT(CHAR(8), CASE WHEN LEN(msdb.dbo.sysschedules.active_end_time) = 3
THEN CAST('00:0' + LEFT(msdb.dbo.sysschedules.active_end_time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
2, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysschedules.active_end_time) = 4
THEN CAST('00:'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
1, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
3, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysschedules.active_end_time) = 5
THEN CAST('0' + LEFT(msdb.dbo.sysschedules.active_end_time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
2, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
4, 2) AS VARCHAR(8))
WHEN msdb.dbo.sysschedules.active_end_time = 0
THEN '00:00:00'
ELSE CAST(LEFT(msdb.dbo.sysschedules.active_end_time, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
3, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysschedules.active_end_time AS VARCHAR(6)),
5, 2) AS VARCHAR(8))
END, 108) AS End_Time
,CAST(CASE WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 1
THEN CAST('00:00:0' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 1)AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 2
THEN CAST('00:00:' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 2)AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 3
THEN CAST('00:0' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
2, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 4
THEN CAST('00:'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
1, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
3, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 5
THEN CAST('0' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
2, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
4, 2) AS VARCHAR(8))
WHEN msdb.dbo.sysjobservers.last_run_duration = 0
THEN '00:00:00'
ELSE CAST(LEFT(msdb.dbo.sysjobservers.last_run_duration, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
3, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
5, 2) AS VARCHAR(8))
END AS VARCHAR(8)) AS LastRunDuration
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN
msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id LEFT OUTER JOIN
msdb.dbo.sysschedules ON msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE sysjobs.enabled = 1 AND msdb.dbo.sysschedules.Active_Start_Time IS NOT NULL
ORDER BY Start_time,sysjobs.name
END
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'
BEGIN
SELECT @@SERVERNAME
,sysjobs.name
,dbo.udf_schedule_description(sysjobschedules.freq_type, sysjobschedules.freq_interval,
sysjobschedules.freq_subday_type, sysjobschedules.freq_subday_interval, sysjobschedules.freq_relative_interval,
sysjobschedules.freq_recurrence_factor, sysjobschedules.active_start_date, sysjobschedules.active_end_date,
sysjobschedules.active_start_time, sysjobschedules.active_end_time) AS [Schedule Description]
, CONVERT(CHAR(8), CASE WHEN LEN(msdb.dbo.sysjobschedules.Active_Start_Time) = 3
THEN CAST('00:0' + LEFT(msdb.dbo.sysjobschedules.Active_Start_Time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
2, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobschedules.Active_Start_Time) = 4
THEN CAST('00:'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
1, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
3, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobschedules.Active_Start_Time) = 5
THEN CAST('0' + LEFT(msdb.dbo.sysjobschedules.Active_Start_Time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
2, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
4, 2) AS VARCHAR(8))
WHEN msdb.dbo.sysjobschedules.Active_Start_Time = 0
THEN '00:00:00'
ELSE CAST(LEFT(msdb.dbo.sysjobschedules.Active_Start_Time, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
3, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.Active_Start_Time AS VARCHAR(6)),
5, 2) AS VARCHAR(8))
END, 108) AS Start_Time,
CONVERT(CHAR(8), CASE WHEN LEN(msdb.dbo.sysjobschedules.active_end_time) = 3
THEN CAST('00:0' + LEFT(msdb.dbo.sysjobschedules.active_end_time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
2, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobschedules.active_end_time) = 4
THEN CAST('00:'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
1, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
3, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobschedules.active_end_time) = 5
THEN CAST('0' + LEFT(msdb.dbo.sysjobschedules.active_end_time, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
2, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
4, 2) AS VARCHAR(8))
WHEN msdb.dbo.sysjobschedules.active_end_time = 0
THEN '00:00:00'
ELSE CAST(LEFT(msdb.dbo.sysjobschedules.active_end_time, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
3, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobschedules.active_end_time AS VARCHAR(6)),
5, 2) AS VARCHAR(8))
END, 108) AS End_Time
,CAST(CASE WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 1
THEN CAST('00:00:0' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 1)AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 2
THEN CAST('00:00:' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 2)AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 3
THEN CAST('00:0' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
2, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 4
THEN CAST('00:'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
1, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
3, 2) AS VARCHAR(8))
WHEN LEN(msdb.dbo.sysjobservers.last_run_duration) = 5
THEN CAST('0' + LEFT(msdb.dbo.sysjobservers.last_run_duration, 1) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
2, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
4, 2) AS VARCHAR(8))
WHEN msdb.dbo.sysjobservers.last_run_duration = 0
THEN '00:00:00'
ELSE CAST(LEFT(msdb.dbo.sysjobservers.last_run_duration, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
3, 2) + ':'
+ SUBSTRING(CAST(msdb.dbo.sysjobservers.last_run_duration AS VARCHAR(6)),
5, 2) AS VARCHAR(8))
END AS VARCHAR(8)) AS LastRunDuration
FROM sysjobs LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id INNER JOIN
sysjobschedules ON sysjobs.job_id = sysjobschedules.job_id
WHERE sysjobs.enabled = 1
ORDER BY Start_time,sysjobs.name
END
This script requires a udf in MSDB. Here is the code for the function:
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_schedule_description] (@freq_type INT ,
@freq_interval INT ,
@freq_subday_type INT ,
@freq_subday_interval INT ,
@freq_relative_interval INT ,
@freq_recurrence_factor INT ,
@active_start_date INT ,
@active_end_date INT,
@active_start_time INT ,
@active_end_time INT )
RETURNS NVARCHAR(255) AS
BEGIN
DECLARE @schedule_description NVARCHAR(255)
DECLARE @loop INT
DECLARE @idle_cpu_percent INT
DECLARE @idle_cpu_duration INT
IF (@freq_type = 0x1) -- OneTime
BEGIN
SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + CONVERT(NVARCHAR, cast((@active_start_time / 10000) as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
RETURN @schedule_description
END
IF (@freq_type = 0x4) -- Daily
BEGIN
SELECT @schedule_description = N'Every day '
END
IF (@freq_type = 0x8) -- Weekly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
SELECT @loop = 1
WHILE (@loop <= 7)
BEGIN
IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
SELECT @loop = @loop + 1
END
IF (RIGHT(@schedule_description, 2) = N', ')
SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
END
IF (@freq_type = 0x10) -- Monthly
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
END
IF (@freq_type = 0x20) -- Monthly Relative
BEGIN
SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
SELECT @schedule_description = @schedule_description +
CASE @freq_relative_interval
WHEN 0x01 THEN N'first '
WHEN 0x02 THEN N'second '
WHEN 0x04 THEN N'third '
WHEN 0x08 THEN N'fourth '
WHEN 0x10 THEN N'last '
END +
CASE
WHEN (@freq_interval > 00)
AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
WHEN (@freq_interval = 08) THEN N'day'
WHEN (@freq_interval = 09) THEN N'week day'
WHEN (@freq_interval = 10) THEN N'weekend day'
END + N' of that month '
END
IF (@freq_type = 0x40) -- AutoStart
BEGIN
SELECT @schedule_description = FORMATMESSAGE(14579)
RETURN @schedule_description
END
IF (@freq_type = 0x80) -- OnIdle
BEGIN
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUPercent',
@idle_cpu_percent OUTPUT,
N'no_output'
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'IdleCPUDuration',
@idle_cpu_duration OUTPUT,
N'no_output'
SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
RETURN @schedule_description
END
-- Subday stuff
SELECT @schedule_description = @schedule_description +
CASE @freq_subday_type
WHEN 0x1 THEN N'at ' + CONVERT(NVARCHAR, cast(
CASE WHEN LEN(cast((@active_start_time / 10000)as varchar(10)))=1
THEN '0'+cast((@active_start_time / 10000) as varchar(10))
ELSE cast((@active_start_time / 10000) as varchar(10))
END
as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2))
WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
END
IF (@freq_subday_type IN (0x2, 0x4, 0x8))
SELECT @schedule_description = @schedule_description + N' between ' +
CONVERT(NVARCHAR, cast(
CASE WHEN LEN(cast((@active_start_time / 10000)as varchar(10)))=1
THEN '0'+cast((@active_start_time / 10000) as varchar(10))
ELSE cast((@active_start_time / 10000) as varchar(10))
END
as varchar(10)) + ':' + right('00' + cast((@active_start_time % 10000) / 100 as varchar(10)),2) )
+ N' and ' +
CONVERT(NVARCHAR, cast(
CASE WHEN LEN(cast((@active_end_time / 10000)as varchar(10)))=1
THEN '0'+cast((@active_end_time / 10000) as varchar(10))
ELSE cast((@active_end_time / 10000) as varchar(10))
END
as varchar(10)) + ':' + right('00' + cast((@active_end_time % 10000) / 100 as varchar(10)),2) )
RETURN @schedule_description
END
I have got this far and have spent too much time trying to find out what the problem is. Please help.
Thanks, Raj