Tuesday, March 27, 2012

Easy SQL Server Version Identification

I know about @.@.version and SERVERPROPERTY, but I am looking for a query that will determine between SQL Server 2000 and 2005. I need to write an if/then statement and don't want to have to add all of the 9.xxx.xx versions for 2005 and keep that updated with each hotfix that comes out.
-Kyle

Use the following query..

Tested in all the versions..

Code Snippet

Declare @.version as Varchar(10);

Set @.version = Cast(serverproperty('ProductVersion') as Varchar)

if charindex('10.',@.version) = 1

Print 'It is SQL Server 2008'

if charindex('9.',@.version) =1

Print 'It is SQL Server 2005'

if charindex('8.',@.version) =1

Print 'It is SQL Server 2000'

|||The final query looked like this...thanks for your help.

IF CONVERT(char(1),SERVERPROPERTY('ProductVersion'))>='9' --pre SQL 2005
BEGIN
DECLARE @.Server [nchar] (100)
SET @.Server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))

INSERT INTO [tempdb].[dbo].[User_Auditing] (Server, UserName, WinAuth, SQL_Auth_UserName, PassPolicyOn)
SELECT @.Server, s.name, isntuser, q.name, is_policy_checked
FROM sys.syslogins s FULL OUTER JOIN sys.sql_logins q
ON (s.name = q.name)
END

-Kyle

No comments:

Post a Comment