确定服务器产品版本并相应执行SQL的最佳方法?

时间:2021-04-09 08:24:00

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:

我有一个脚本来查询MSDB并获取作业计划的详细信息。显然,表格从SQL 2000到SQL 2005不同。因此,我想检查框中运行的版本并进行相应的查询。现在的问题是:

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.

虽然上面的脚本在2000和2005的盒子上运行正常,但当我用我的实际代码替换“Print ..”语句时,它在2000盒子上运行正常,但是当在2005盒子上执行时,尝试运行代码块意味着2000并返回错误。

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:

此脚本需要MSDB中的udf。这是函数的代码:

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.

我有这么远,并花了太多时间试图找出问题所在。请帮忙。

3 个解决方案

#1


the errors are compile time (I ran on 2005):

错误是编译时间(我在2005年运行):

Msg 207, Level 16, State 1, Line 106
Invalid column name 'freq_type'.
Msg 207, Level 16, State 1, Line 106
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 107
Invalid column name 'freq_subday_type'.
Msg 207, Level 16, State 1, Line 107
Invalid column name 'freq_subday_interval'.
Msg 207, Level 16, State 1, Line 107
Invalid column name 'freq_relative_interval'.
Msg 207, Level 16, State 1, Line 108
Invalid column name 'freq_recurrence_factor'.
Msg 207, Level 16, State 1, Line 108
Invalid column name 'active_start_date'.
Msg 207, Level 16, State 1, Line 108
Invalid column name 'active_end_date'.
Msg 207, Level 16, State 1, Line 109
Invalid column name 'active_start_time'.
Msg 207, Level 16, State 1, Line 109
Invalid column name 'active_end_time'.
Msg 207, Level 16, State 1, Line 110

I added PRINTs and they never appear.

我添加了PRINT,它们从未出现过。

your code has problems because the column names are not compatible with the database you are running. SQL Server 2005 does not have a "sysjobschedules.freq_type" column.

您的代码有问题,因为列名与您正在运行的数据库不兼容。 SQL Server 2005没有“sysjobschedules.freq_type”列。

Make a stored procedure XYZ, put the 2000 version in the 2000 database, put the same XYZ procedure on the 2005 machine and put the 2005 version in it. No IF necessary...

创建一个存储过程XYZ,将2000版本放在2000数据库中,在2005机器上放置相同的XYZ过程并将2005版本放入其中。没有必要......

EDIT

run this code:

运行此代码:

PRINT 'Works'

now run this code

现在运行此代码

PRINT 'will not see this'
error

try this:

PRINT 'will not see this'
SELECT xyz from sysjobschedules

now try running this, but only highlight the PRINT line:

现在尝试运行它,但只突出显示PRINT行:

PRINT 'you can see this'  --only select this line of code and run it
SELECT xyz from sysjobschedules

see how compile errors prevent anything from running

看看编译错误如何阻止任何运行

EDIT

you might try something like this...

你可以尝试这样的东西......

DECLARE @Query varchar(max)

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'
BEGIN
    SET @Query=......
END
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'
BEGIN
    SET @Query=......
END

EXEC (@Query)

#2


The original question is only specific to 2000,2005 but here's some code that should work on 2000, 2005, 2008 and onwards

最初的问题仅针对2000,2005,但这里的一些代码应该适用于2000年,2005年,2008年及以后

DECLARE @ver NVARCHAR(128)
DECLARE @majorVersion int
SET @ver = CAST(SERVERPROPERTY('productversion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver,1,CHARINDEX('.',@ver)-1)
SET @majorVersion  = CAST(@ver AS INT)
IF @majorVersion < 11
    PRINT 'Plesae Upgrade'
ELSE
    PRINT @majorVersion 

#3


I tried the code on 2008 and it ran fine. are you in the msdb database when running it?

我在2008年尝试了代码并运行良好。你在运行它时在msdb数据库中吗?

to get the version you can also do this

要获得该版本,您也可以这样做

SELECT 
PARSENAME(CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')),4) 
 AS SqlServerVersion

#1


the errors are compile time (I ran on 2005):

错误是编译时间(我在2005年运行):

Msg 207, Level 16, State 1, Line 106
Invalid column name 'freq_type'.
Msg 207, Level 16, State 1, Line 106
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 107
Invalid column name 'freq_subday_type'.
Msg 207, Level 16, State 1, Line 107
Invalid column name 'freq_subday_interval'.
Msg 207, Level 16, State 1, Line 107
Invalid column name 'freq_relative_interval'.
Msg 207, Level 16, State 1, Line 108
Invalid column name 'freq_recurrence_factor'.
Msg 207, Level 16, State 1, Line 108
Invalid column name 'active_start_date'.
Msg 207, Level 16, State 1, Line 108
Invalid column name 'active_end_date'.
Msg 207, Level 16, State 1, Line 109
Invalid column name 'active_start_time'.
Msg 207, Level 16, State 1, Line 109
Invalid column name 'active_end_time'.
Msg 207, Level 16, State 1, Line 110

I added PRINTs and they never appear.

我添加了PRINT,它们从未出现过。

your code has problems because the column names are not compatible with the database you are running. SQL Server 2005 does not have a "sysjobschedules.freq_type" column.

您的代码有问题,因为列名与您正在运行的数据库不兼容。 SQL Server 2005没有“sysjobschedules.freq_type”列。

Make a stored procedure XYZ, put the 2000 version in the 2000 database, put the same XYZ procedure on the 2005 machine and put the 2005 version in it. No IF necessary...

创建一个存储过程XYZ,将2000版本放在2000数据库中,在2005机器上放置相同的XYZ过程并将2005版本放入其中。没有必要......

EDIT

run this code:

运行此代码:

PRINT 'Works'

now run this code

现在运行此代码

PRINT 'will not see this'
error

try this:

PRINT 'will not see this'
SELECT xyz from sysjobschedules

now try running this, but only highlight the PRINT line:

现在尝试运行它,但只突出显示PRINT行:

PRINT 'you can see this'  --only select this line of code and run it
SELECT xyz from sysjobschedules

see how compile errors prevent anything from running

看看编译错误如何阻止任何运行

EDIT

you might try something like this...

你可以尝试这样的东西......

DECLARE @Query varchar(max)

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'
BEGIN
    SET @Query=......
END
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'
BEGIN
    SET @Query=......
END

EXEC (@Query)

#2


The original question is only specific to 2000,2005 but here's some code that should work on 2000, 2005, 2008 and onwards

最初的问题仅针对2000,2005,但这里的一些代码应该适用于2000年,2005年,2008年及以后

DECLARE @ver NVARCHAR(128)
DECLARE @majorVersion int
SET @ver = CAST(SERVERPROPERTY('productversion') AS NVARCHAR)
SET @ver = SUBSTRING(@ver,1,CHARINDEX('.',@ver)-1)
SET @majorVersion  = CAST(@ver AS INT)
IF @majorVersion < 11
    PRINT 'Plesae Upgrade'
ELSE
    PRINT @majorVersion 

#3


I tried the code on 2008 and it ran fine. are you in the msdb database when running it?

我在2008年尝试了代码并运行良好。你在运行它时在msdb数据库中吗?

to get the version you can also do this

要获得该版本,您也可以这样做

SELECT 
PARSENAME(CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')),4) 
 AS SqlServerVersion