SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Parse @@VERSION		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 06/07/2007 :  16:48:07  Show Profile  Reply with Quote
This script parses the @@VERSION global variable into individual columns.

I developed it because I wanted to be able gather standard info on all versions.

I know there are functions for a lot of this, but only starting with SQL 2000.

It seems to work with all versions of SQL Server from 7.0 through 2005.

I haven't tested with 6.5 and before or 2008, because I don't have either available.

Please report any problems you see.

Edit: 2007-7-31
1. Changed SQL_SERVER_MAJOR_VERSION to varchar(20)
2. Added code to create a view named V_SQL_SERVER_VERSION
3. Added four new columns to the view:
SERVER_NAME, value from @@servername
SQL_SERVER_MAJOR_VERSION_NUMBER, Example: 9
SQL_SERVER_VERSION_NUMBER, Example: 8.0020390000
WINDOWS_VERSION_NAME, Example: 'Windows 2000'


Edit: 2007-8-2
Changed SQL_SERVER_MAJOR_VERSION to varchar(40)





select
	SQL_SERVER_MAJOR_VERSION = 
		convert(varchar(40),substring(L1,1,L1_BREAK_1-1)),
	SQL_SERVER_VERSION =
		convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))),
	SQL_SERVER_PLATFORM =
		convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))),
	SQL_SERVER_EDITION =
		convert(varchar(30),substring(L4,1,L4_BREAK_1-1)),
	WINDOWS_VERSION =
		convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))),
	WINDOWS_BUILD =
		convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))),
	WINDOWS_SERVICE_PACK =
		convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2)))
from
(
select
	L1_BREAK_1 = charindex(' - ',L1),
	L1_BREAK_2 = charindex(' (',L1),
	L1_BREAK_3 = charindex(')',L1),
	L4_BREAK_1 = charindex(' on Windows',L4),
	L4_BREAK_2 = charindex(' (',L4),
	L4_BREAK_3 = charindex(': ',L4),
	L4_BREAK_4 = charindex(')',L4),
	L1,
	L4
from
(
select
	L1 =
	convert(varchar(100),
	rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server','')))
	) ,
	L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))

from
(
select
	zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from
(

select
	yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from
(
select
	xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from
(
select VERSION = @@VERSION 
) a ) a1 ) a2 ) a3 ) a4 ) a4

Results:

SQL_SERVER_MAJOR_VERSION SQL_SERVER_VERSION   SQL_SERVER_PLATFORM  SQL_SERVER_EDITION             WINDOWS_VERSION      WINDOWS_BUILD        WINDOWS_SERVICE_PACK           
------------------------ -------------------- -------------------- ------------------------------ -------------------- -------------------- ------------------------------ 
2000                     8.00.2039            Intel X86            Standard Edition               Windows NT 5.0       Build 2195           Service Pack 4

(1 row(s) affected)






drop view [dbo].[V_SQL_SERVER_VERSION]
go
create view [dbo].[V_SQL_SERVER_VERSION]
as
select
	SERVER_NAME = @@servername,
	SQL_SERVER_MAJOR_VERSION,
	SQL_SERVER_VERSION,
	SQL_SERVER_MAJOR_VERSION_NUMBER =
		convert(int,floor(convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4)))),
	SQL_SERVER_VERSION_NUMBER=
		convert(numeric(20,10),(
		convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4))*1000000+
		convert(numeric(20,10),substring(SQL_SERVER_VERSION,6,30)))/1000000),
	SQL_SERVER_PLATFORM,
	SQL_SERVER_EDITION,
	WINDOWS_VERSION_NAME =
		convert(varchar(20),
		case
		when WINDOWS_VERSION = 'Windows NT 5.0'
		then 'Windows 2000'
		when WINDOWS_VERSION = 'Windows NT 5.1'
		then 'Windows XP'
		when WINDOWS_VERSION = 'Windows NT 5.2'
		then 'Windows 2003'
		else WINDOWS_VERSION
		end),
	WINDOWS_VERSION,
	WINDOWS_BUILD,
	WINDOWS_SERVICE_PACK
from
(
select
	SQL_SERVER_MAJOR_VERSION = 
		convert(varchar(40),substring(L1,1,L1_BREAK_1-1)),
	SQL_SERVER_VERSION =
		convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))),
	SQL_SERVER_PLATFORM =
		convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))),
	SQL_SERVER_EDITION =
		convert(varchar(30),substring(L4,1,L4_BREAK_1-1)),
	WINDOWS_VERSION =
		convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))),
	WINDOWS_BUILD =
		convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))),
	WINDOWS_SERVICE_PACK =
		convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2))),
	VERSION = VERSION
from
(
select
	VERSION,
	L1_BREAK_1 = charindex(' - ',L1),
	L1_BREAK_2 = charindex(' (',L1),
	L1_BREAK_3 = charindex(')',L1),
	L4_BREAK_1 = charindex(' on Windows',L4),
	L4_BREAK_2 = charindex(' (',L4),
	L4_BREAK_3 = charindex(': ',L4),
	L4_BREAK_4 = charindex(')',L4),
	L1,
	L4
from
(
select
	VERSION,
	L1 =
	convert(varchar(100),
	rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server','')))
	) ,
	L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))

from
(
select
	VERSION,
	zz = stuff(yy,charindex(Char(10),yy),1,'#3#')
from
(

select
	VERSION,
	yy = stuff(xx,charindex(Char(10),xx),1,'#2#')
from
(
select
	VERSION,
	xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#')
from
	( select VERSION = @@version ) a ) a1 ) a2 ) a3 ) a4 ) a4 ) a5
go
grant select on [dbo].[V_SQL_SERVER_VERSION] to public
go

select * from [dbo].[V_SQL_SERVER_VERSION]



CODO ERGO SUM
Edited by - Michael Valentine Jones on 08/02/2007 10:23:20

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/22/2007 :  21:08:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
	SQL_SERVER_MAJOR_VERSION = 
		convert(varchar(10 20),substring(L1,1,L1_BREAK_1-1)), -- Increase from 10 to 20 for Katmai


SQL_SERVER_MAJOR_VERSION 
------------------------
code name "Katmai"

Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 06/22/2007 21:09:00
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 06/23/2007 :  15:10:18  Show Profile  Reply with Quote
>> code name "Katmai"

Is that from the SQL Server 2008 CTP?





CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 06/23/2007 :  16:41:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. The one released last monday.

select @@version returns

Microsoft SQL Server code name "Katmai" - 10.0.1019.17 (Intel X86) May 24 2007 15:26:55 Copyright (c) 1988-2007 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

It is only 18 characters, but since you have a string pointer anyway, I couldn't see the harm of having 20 characters, just in case.


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 06/23/2007 16:42:53
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 06/25/2007 :  08:10:54  Show Profile  Reply with Quote
When are MS going to normalise this?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 06/25/2007 :  08:15:40  Show Profile  Visit nr's Homepage  Reply with Quote
>>When are MS going to normalise this?
There's xp_msver now too.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Edited by - nr on 06/25/2007 08:16:58
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 06/25/2007 :  11:57:46  Show Profile  Reply with Quote
In SQL Server 2000 and later, this info is available from function calls. The reason I did this script is to be able to gather info from servers that have unknown versions, like SQL 7. I have located about 300+ servers on our network, and want a unified way to gather this info. Crude but it works.




CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/01/2007 :  20:29:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
July CTP for SQL Server 2008
Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86) 
	Jul 25 2007 20:28:28 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
The new thing is the CTP part.

E 12°55'05.25"
N 56°04'39.16"
Edited by - SwePeso on 08/01/2007 20:29:59
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 08/02/2007 :  10:25:46  Show Profile  Reply with Quote
quote:
Originally posted by Peso

July CTP for SQL Server 2008
Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86) 
	Jul 25 2007 20:28:28 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
The new thing is the CTP part.

E 12°55'05.25"
N 56°04'39.16"


Can you see if the change I just made works with that version? I don't have 2008 installed.



CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/02/2007 :  15:40:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/02/2007 :  15:42:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
declare @s VARCHAR(8000)
set @s = 'Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86) 
	Jul 25 2007 20:28:28 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)'


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts
Posted - 08/02/2007 :  16:22:02  Show Profile  Visit dinakar's Homepage  Reply with Quote
Actually I got the same error when I ran it against my 2000 production server. 

---
Microsoft SQL Server  2000 - 8.00.789 (Intel IA-64) 
	Mar 27 2003 19:20:49 
	Copyright (c) 1988-2003 Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/30/2007 :  18:04:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

Can you see if the change I just made works with that version? I don't have 2008 installed.
No. Still same error.
declare @s VARCHAR(8000)
set @s = 'Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1049.14 (Intel X86) 
	Jul 25 2007 20:28:28 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)'


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 11/22/2007 :  16:52:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
declare @s VARCHAR(8000)

set @s = 'Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1075.23 (Intel X86) 
	Nov  8 2007 14:16:26 
	Copyright (c) 1988-2007 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)'


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 07/22/2009 :  16:34:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
SELECT  @@VERSION

Microsoft SQL Server 2008 (SP1) - 10.0.2723.0 (X64) 
	Jul  9 2009 23:46:07 
	Copyright (c) 1988-2008 Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7100: )


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 08/12/2009 :  06:02:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote 
Microsoft SQL Server 2008 R2 (CTP) - 10.50.1092.20 (X64) 
	Jul 22 2009 21:22:48 
	Copyright (c) Microsoft Corporation
	Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000