Use CASE to find out the version

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Use CASE to find out the version

Author

Topic

sundevilscott
Starting Member

12 Posts

Posted - 2009-04-15 : 10:50:05

I am working on a job schedule query that is different from 2000 to 2005. so I want to know if I can do something like.

SELECT CASE @@VERSION WHEN LIKE '%Microsoft SQL Server 2000%' Then (Insert Select statement here) ELSE (Insert different Select Statement here) END

Thank you for you help ahead of time.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-15 : 11:02:35

yes -- you can do that. However you might be better off with


sp_dbcmptlevel '<DBNAME>'

As that will return the compat level for the database in question.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sundevilscott
Starting Member

12 Posts

Posted - 2009-04-15 : 11:27:03

Thank you Charlie. Could you help me with what the proper syntax would be with the Case statement above. Thank you in advance.

dsindo
Starting Member

45 Posts

Posted - 2009-04-15 : 14:40:08

try this

select case when left(@@version,26)='Microsoft SQL Server 2000' then.. else.. end

sundevilscott
Starting Member

12 Posts

Posted - 2009-04-15 : 17:05:43

So I could not use the CASE because it comes back with more then 1 result which is not allowed and I decided to try an IF ELSE statement.

Now my understanding is that when you use the IF it determines which are it uses. Like this for example if it is ran on a 2005 machine.

IF (SELECT @@VERSION) LIKE '%Microsoft SQL Server 2000%'
BEGIN
SELECT 2000
END
ELSE
BEGIN
SELECT 2005
END

It should return 2005. So my understanding is that it will skip the first end since the @@version is not equal but that is not happening on my real Job Schedule query which is made to run one query if it is 2000 and another for 2005. What is happening is I am getting an error telling me that I have invalid column names since it was designed for 2000. My top query is for 2000 but it seems like it is not skipping it.

Can anyone give me an idea on this?

IF ((SELECT @@VERSION) LIKE '%Microsoft SQL Server 2000%')

BEGIN
--Run the Job Schedule on a SQL 2000 Machine

select convert(varchar(40), j.name) as job_name
, case s.freq_interval & 2 when 2 then 'MON' else '' END + ',' +
case s.freq_interval & 4 when 4 then 'TUE' else '' end + ',' +
case s.freq_interval & 8 when 8 then 'WED' else '' end + ',' +
case s.freq_interval & 16 when 16 then 'THU' else '' end + ',' +
case s.freq_interval & 32 when 32 then 'FRI' else '' end + ',' +
case s.freq_interval & 64 when 64 then 'SAT' else '' end + ',' +
case s.freq_interval & 1 when 1 then 'SUN' else '' end as freq_interval
, substring(right(stuff(' ', 1, 1, '000000')
+ convert(varchar(6), s.active_start_time), 6), 1, 2)
+ ':'
+ substring(right(stuff(' ', 1, 1, '000000') + convert(varchar(6), s.active_start_time), 6), 3, 2)
+ ':'
+ substring(right(stuff(' ', 1, 1, '000000') + convert(varchar(6), s.active_start_time), 6), 5, 2) as start_at

, SUBSTRING(RIGHT('00000' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)),6), 1, 2)
+ ':'
+ SUBSTRING(RIGHT('00000' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 3, 2) + ':'
+ SUBSTRING(RIGHT('00000' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 5, 2) AS duration
from msdb..sysjobs j
join msdb..sysJobSchedules s on j.job_id = s.job_id
JOIN msdb..sysjobsteps st ON j.job_id = st.job_id
WHERE j.enabled = 1

GROUP BY j.NAME, s.active_start_time, s.freq_interval

END

ELSE

BEGIN
select convert(varchar(40), j.name) as job_name
, case s.freq_interval & 2 when 2 then 'MON' else '' END + ',' +
case s.freq_interval & 4 when 4 then 'TUE' else '' end + ',' +
case s.freq_interval & 8 when 8 then 'WED' else '' end + ',' +
case s.freq_interval & 16 when 16 then 'THU' else '' end + ',' +
case s.freq_interval & 32 when 32 then 'FRI' else '' end + ',' +
case s.freq_interval & 64 when 64 then 'SAT' else '' end + ',' +
case s.freq_interval & 1 when 1 then 'SUN' else '' end as freq_interval
, substring(right(stuff(' ', 1, 1, '000000')
+ convert(varchar(6), s.active_start_time), 6), 1, 2)
+ ':'
+ substring(right(stuff(' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6), 3, 2)
+ ':'
+ substring(right(stuff(' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6), 5, 2) as start_at

, SUBSTRING(RIGHT('00000' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)),6), 1, 2)
+ ':'
+ SUBSTRING(RIGHT('00000' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 3, 2) + ':'
+ SUBSTRING(RIGHT('00000' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 5, 2) AS duration
from msdb..sysjobs j
JOIN msdb..sysjobschedules js ON j.job_id = js.job_id
join msdb..sysSchedules s on js.schedule_id = s.schedule_id
JOIN msdb..sysjobsteps st ON j.job_id = st.job_id
WHERE j.enabled = 1
--AND s.freq_interval NOT like '0'

GROUP BY j.NAME, s.active_start_time, s.freq_interval
END

Msg 207, Level 16, State 1, Line 31
Invalid column name 'active_start_time'.
Msg 207, Level 16, State 1, Line 31
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'freq_interval'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'freq_interval'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 04:18:19

Try

declare @sql varchar(8000)
IF ((SELECT @@VERSION) LIKE '%Microsoft SQL Server 2000%')

select @sql='convert(varchar(40), j.name) as job_name
, case s.freq_interval & 2 when 2 then ''MON'' else '''' END + '','' +
case s.freq_interval & 4 when 4 then ''TUE'' else '''' end + '','' +
case s.freq_interval & 8 when 8 then ''WED'' else '''' end + '','' +
case s.freq_interval & 16 when 16 then ''THU'' else '''' end + '','' +
case s.freq_interval & 32 when 32 then ''FRI'' else '''' end + '','' +
case s.freq_interval & 64 when 64 then ''SAT'' else '''' end + '','' +
case s.freq_interval & 1 when 1 then ''SUN'' else '''' end as freq_interval
, substring(right(stuff('' '', 1, 1, ''000000'')
+ convert(varchar(6), s.active_start_time), 6), 1, 2)
+ '':''
+ substring(right(stuff('' '', 1, 1, ''000000'') + convert(varchar(6), s.active_start_time), 6), 3, 2)
+ '':''
+ substring(right(stuff('' '', 1, 1, ''000000'') + convert(varchar(6), s.active_start_time), 6), 5, 2) as start_at

, SUBSTRING(RIGHT(''00000'' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)),6), 1, 2)
+ '':''
+ SUBSTRING(RIGHT(''00000'' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 3, 2) + '':''
+ SUBSTRING(RIGHT(''00000'' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 5, 2) AS duration
from msdb..sysjobs j
join msdb..sysJobSchedules s on j.job_id = s.job_id
JOIN msdb..sysjobsteps st ON j.job_id = st.job_id
WHERE j.enabled = 1

GROUP BY j.NAME, s.active_start_time, s.freq_interval'

ELSE

select @sql='convert(varchar(40), j.name) as job_name
, case s.freq_interval & 2 when 2 then ''MON'' else '''' END + '','' +
case s.freq_interval & 4 when 4 then ''TUE'' else '''' end + '','' +
case s.freq_interval & 8 when 8 then ''WED'' else '''' end + '','' +
case s.freq_interval & 16 when 16 then ''THU'' else '''' end + '','' +
case s.freq_interval & 32 when 32 then ''FRI'' else '''' end + '','' +
case s.freq_interval & 64 when 64 then ''SAT'' else '''' end + '','' +
case s.freq_interval & 1 when 1 then ''SUN'' else '''' end as freq_interval
, substring(right(stuff('' '', 1, 1, ''000000'')
+ convert(varchar(6), s.active_start_time), 6), 1, 2)
+ '':''
+ substring(right(stuff('' '', 1, 1, ''000000'') + convert(varchar(6), active_start_time), 6), 3, 2)
+ '':''
+ substring(right(stuff('' '', 1, 1, ''000000'') + convert(varchar(6), active_start_time), 6), 5, 2) as start_at

, SUBSTRING(RIGHT(''00000'' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)),6), 1, 2)
+ '':''
+ SUBSTRING(RIGHT(''00000'' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 3, 2) + '':''
+ SUBSTRING(RIGHT(''00000'' + CAST(SUM(st.last_run_duration) AS VARCHAR(6)), 6), 5, 2) AS duration
from msdb..sysjobs j
JOIN msdb..sysjobschedules js ON j.job_id = js.job_id
join msdb..sysSchedules s on js.schedule_id = s.schedule_id
JOIN msdb..sysjobsteps st ON j.job_id = st.job_id
WHERE j.enabled = 1
--AND s.freq_interval NOT like ''0''

GROUP BY j.NAME, s.active_start_time, s.freq_interval'

print @sql

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-16 : 05:07:30

And obviously -- you'd also want to EXEC (@sql) that!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 06:26:03

quote:
Originally posted by Transact Charlie

And obviously -- you'd also want to EXEC (@sql) that!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Yes. I just want to make sure that OP is getting correct query

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources