Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Versioning

By Andre Vigneau,

The question is why do you want to know which version of Microsoft SQL Server you're running?

In my case it is for compatibility purposes. Applications or scripts might not always be compatible with older versions of SQL Server. Also in the same way applications or scripts might not always be compatible with newer versions of SQL Server. I always have my applications check for compatibility first at startup before it's too late and some process or function does not complete properly or makes the application crash. It is so easy to have a message in place prompting the user to upgrade their application since the OS or SQL has been upgraded.

Another check is during installation when you should always check for other components' version compatibility to be sure it is the same as that which was tested with your application. This prevents other errors from occurring if you permit the application to continue.

I have developed an automated SQL Server administrative application that runs and uses many OS and SQL features that have a strong potential to change between versions. This makes sure the application gets upgraded to the appropriate version when the OS and/or SQL Server versions change.

-- DB & OS Version control START
DECLARE @SQLVersionMaj dec(4,2), @OSVersionMaj dec(4,2)
DECLARE @SQLVersionMin dec(4), @OSVersionMin dec(4)
CREATE TABLE #Version (IndexId int NOT NULL
			,Name varchar(60)
			,Internal_Value int
			,Character_Value varchar(255))
INSERT #Version exec master.dbo.xp_msver
SELECT @SQLVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))
, @SQLVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))
FROM #Version
WHERE Name = 'ProductVersion'
SELECT @OSVersionMaj = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))
, @OSVersionMin = CONVERT(dec(4),SUBSTRING(Character_Value,6,4))
FROM #Version
WHERE Name = 'WindowsVersion'

DROP TABLE #Version
-- DB & OS Version control END

From here you have all you need to compare your compatibility
select @SQLVersionMaj as SQLVersionMaj,@SQLVersionMin as SQLVersionMin
, @OSVersionMaj as OSVersionMaj, @OSVersionMin as OSVersionMin
If you really need to know the service pack number you can do and maintain the following:(You can do the same for the OS Versions)
DECLARE @SQLVersionSP dec(2)
, @SQLVersionPatch char(2)

CREATE TABLE #SQLVersion
(
 SQLVersionMaj dec(4,2)
,SQLVersionMin dec(4)
,SQLVersionSP dec(2)
,SQLVersionPatch char(2)
)

INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,760,3,'')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,532,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,384,1,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(8.00,194,0,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,1063,4,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,961,3,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,842,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,699,1,'')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(7.00,623,0,'')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,479,5,'a1')
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,416,5,'a') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,415,5,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,281,4,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,258,3,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,240,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,213,1,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.50,201,0,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,151,3,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,139,2,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,124,1,'') 
INSERT INTO #SQLVersion (SQLVersionMaj,SQLVersionMin,SQLVersionSP,SQLVersionPatch)
 VALUES(6.00,121,0,'')

SELECT * FROM #SQLVersion
WHERE SQLVersionMaj = @SQLVersionMaj AND SQLVersionMin = @SQLVersionMin
DROP TABLE #SQLVersion
This is to get the Edition version. In case you application needs special features available only in some edition. Or you really want to restrict it not to be installed on MSDE for example. The edition is also available at startup as it is written into the SQL Server errolog but there is no easy public way to get it.

The following will fail in Yukon. It seems Microsoft has forgotten to pad the information for the extra lines. You can use the above to bypass Yukon…
DECLARE @Edition varchar(255)
CREATE TABLE #ServerInfo
(
 ATTRIBUTE_ID int
,ATTRIBUTE_NAME varchar(60)
,ATTRIBUTE_VALUE varchar(255) 
)

INSERT INTO #ServerInfo exec sp_server_info 2

SELECT @Edition = SUBSTRING(ATTRIBUTE_VALUE,CHARINDEX('Microsoft Corporation',ATTRIBUTE_VALUE)+23
,CHARINDEX('Edition',ATTRIBUTE_VALUE)-24-CHARINDEX('Microsoft Corporation',ATTRIBUTE_VALUE))
FROM #ServerInfo
DROP TABLE #ServerInfo

SELECT @Edition

This, however, should work correctly:
---------------------------------------------------------
DECLARE @Edition varchar(255)
CREATE TABLE #ServerInfo
(
 ATTRIBUTE_ID int
,ATTRIBUTE_NAME varchar(60)
,ATTRIBUTE_VALUE varchar(255)
)

INSERT INTO #ServerInfo exec sp_server_info 2

SELECT @Edition = CASE WHEN CHARINDEX('Microsoft
Corporation',ATTRIBUTE_VALUE) = 0 
		THEN 'Yukon' ELSE
SUBSTRING(ATTRIBUTE_VALUE,CHARINDEX('Microsoft
Corporation',ATTRIBUTE_VALUE)+23
	
,CHARINDEX('Edition',ATTRIBUTE_VALUE)-24-CHARINDEX('Microsoft
Corporation',ATTRIBUTE_VALUE))
		END
FROM #ServerInfo
DROP TABLE #ServerInfo

SELECT @Edition
----------------------------------------------------------
Total article views: 5673 | Views in the last 30 days: 1
 
Related Articles
FORUM

Insert rows

insert

FORUM

Inserting foreign key value

Insert

FORUM

INSERT INTO FROM RETRIVED DATA

INSERT

FORUM

Insertion

Inserting large amount of data

FORUM

insert problem

insert problem

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones