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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 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
30421 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
7020 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
30421 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
22859 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
7020 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
30421 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
7020 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
30421 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
30421 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
30421 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
30421 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
30421 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
30421 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

PSamsig
Constraint Violating Yak Guru

Denmark
384 Posts

Posted - 09/16/2013 :  12:33:38  Show Profile  Send PSamsig a Yahoo! Message  Reply with Quote
Sorry to reply to such an old topic, but I had a need for it today, and to be able to parse @@VERSION for newer versions of SQL Server (up to and including 2012), and Michaels brilliant code had a few shortcomings for newer versions (parentheses galore), and since this shows up in a Google search, I thought I better add my minor corrections.

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(50),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,
	L1_BREAK_1,
	L1_BREAK_2 = charindex(' (', L1, L1_BREAK_1),
	L1_BREAK_3 = charindex(')', L1, L1_BREAK_1),
	L4,
	L4_BREAK_1,
	L4_BREAK_2 = charindex(' (', L4, L4_BREAK_1),
	L4_BREAK_3 = charindex(': ', L4, L4_BREAK_1),
	L4_BREAK_4 = charindex(')', L4, L4_BREAK_1)
from (
select
	L1,
	L1_BREAK_1 = charindex(' - ',L1),
	L4,
	L4_BREAK_1 = charindex(' on Windows', 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 ) a ) a ) a ) a ) a ) a


EDIT: Fixed size of SQL_SERVER_EDITION ('Enterprise Edition: Core-based Licensing (64-bit)' is very long!)

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

Edited by - PSamsig on 12/01/2014 05:04:07
Go to Top of Page
  Previous Topic Topic Next 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