November 8, 2010 at 12:44 pm
This query runs fine on 2000 box, but fails on any other. Why isn’t it skipping where @sqlversion = '8' on any 2005+ server?
Declare @sqlversion char(1)
print @@version
Select @sqlversion = substring(@@version, 30, 1)
print @sqlversion
CREATE TABLE #SQLJob_Schedules (
[ServerName] [varchar](50),
[Job_ID] [uniqueidentifier],
[Schedule_Name] [sysname],
[Enabled] [tinyint]
)
If (@sqlversion = '8')
Begin
print '@sqlversion = 8'
Insert Into #SQLJob_Schedules
Select @@ServerName as Servername, a.Job_ID, b.Name as Schedule_Name, b.[Enabled]
From MSDB.dbo.sysjobs a (nolock)
Join MSDB.dbo.sysjobschedules b (nolock) on a.Job_ID = b.Job_ID
Order by Schedule_Name
End
If (@sqlversion <> '8')
Begin
print '@sqlversion <> 8'
Insert Into #SQLJob_Schedules
Select @@ServerName as Servername, a.Job_ID, c.name as Schedule_Name, c.[Enabled]
From MSDB.dbo.sysjobs a (nolock)
Join MSDB.dbo.sysjobschedules b (nolock) on a.Job_ID = b.Job_ID
Join MSDB.dbo.sysschedules c (nolock) on b.schedule_id = c.schedule_id
Order by Schedule_Name
End
Select * From #SQLJob_Schedules
Drop Table #SQLJob_Schedules
There is an exception to every rule, except this one...
November 8, 2010 at 12:59 pm
Because the error is a parse-time error (missing columns) and occurs before any execution has even begin.
If you want to do this you'll need dynamic SQL, because that's the only way you're going to have part of the code completely ignored (not even parsed) on other editions.
Or, better yet, create one version for SQL 2000 and one for 2005+ and have only the relevant version on the relevant server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 8, 2010 at 1:14 pm
Yep, replaced the part for 2k with a sql exec (@variable) and it worked, thanks a bunch!
There is an exception to every rule, except this one...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply