Check SQL Version

Question

Check SQL Version

samtwilliams

SSC Journeyman

Points: 93
More actions
September 18, 2012 at 10:01 am

#281904

Can someone tell me how i can report a 1 or 0 depending on whether a sql version is 2008 or above, i have the code for 2005 but cant work out what needs changing.
declare @isSqlServer2005 bit
select @isSqlServer2005 = case when CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion'))))) < 9 then 0 else 1 end
select @isSqlServer2005

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

SQL Server 2008 and 2008R2 have a major revision number of 10 and SQL Server 2012 has a major revision number of 11. Does that help?

Abu Dina SSChampion Points: 14155 More actions · Answer 2

Why not use @@VERSION instead?

declare @isSqlServer2005 bit

declare @version varchar(50)

select @version = @@version

select @isSqlServer2005 = CASE when left(@version, charindex('-', @version)-1) like '%2008%' then 1 else 0 end

select @isSqlServer2005

Added LEFT string function to get the first bit from the version info.

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

samtwilliams SSC Journeyman Points: 93 More actions · Answer 3

I am this far, i just need to stop a script if it is below 08

DECLARE @isSqlServer2008 BIT
SELECT @isSqlServer2008 = CASE WHEN CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion'))))) < 10 THEN 0 ELSE 1 END
IF (SELECT @isSqlServer2008 = 0)
BEGIN
RAISERROR('Youc an only run this on 2008 or above.', 16, 1)
END

enmadrid76 SSC Journeyman Points: 91 More actions · Answer 4

You can save the output of a sproc into a temp table. xp_msver has a record that lists the version number. You can then parse the first portion of the value and get the major version number. This worked on both 2005 and 2008.

create table #temp_tbl (

Idxsmallint,

Namenvarchar(50),

Internal_Value nvarchar(50),

Character_Value nvarchar(200)

)

-- insert sproc output into temp table

insert into #temp_tbl execute master..xp_msver

-- parse major version number

select *, substring(Character_Value, 1, charindex('.', Character_Value, 0) - 1) as Major_Version_Number

from #temp_tbl

where Name = 'ProductVersion'

Let me know if this works for you.

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

you can take the variable out of it and just have the check for version in the If clause

IF (CONVERT(int, SUBSTRING(CONVERT(varchar(15), SERVERPROPERTY('productversion')), 0, CHARINDEX('.', CONVERT(varchar(15), SERVERPROPERTY('productversion'))))) < 10)

BEGIN

RAISERROR('Youc an only run this on 2008 or above.', 16, 1)

END

ELSE

BEGIN

Print 'This is where your script goes'

End

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I believe this would be safe and simple to validate it's 2008 or higher. Is it possible to use SQL Server 1? 😛

IF CONVERT(int, CONVERT(char(1), SERVERPROPERTY('productversion'))) = 1

BEGIN

RAISERROR('Youc an only run this on 2008 or above.', 16, 1)

END

ELSE

BEGIN

Print 'This is where your script goes'

End

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ScottPletcher SSC Guru Points: 101100 More actions · Answer 7

I would use @@VERSION for that, because you can get the SQL version year, and then it's more self-evident about what you're checking:

DECLARE @isSqlServer2008 bit

SET @isSqlServer2008 = CASE WHEN SUBSTRING(@@VERSION, PATINDEX('%[2][0][0-9][0-9]%', @@VERSION), 4) >= '2008' THEN 1 ELSE 0 END

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".