Major Version number

How can I get just the majore version number of SQL Server from SQL 7 thru
SQL 2005 SP1 ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netCheck out SERVERPROPERTY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
news:%23DGRbHbjGHA.1552@.TK2MSFTNGP03.phx.gbl...
How can I get just the majore version number of SQL Server from SQL 7 thru
SQL 2005 SP1 ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.net|||Thanks for Tom's input.
Hi Michael,
In addition to Tom's suggestion here is a knowledge base article which
demonstrate how to identify version info of SQL Server(applied for
sqlserver from v6.5 to v 9.0(2005))
#How to identify your SQL Server version and edition
http://support.microsoft.com/kb/321185/en-us
Hope this also helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||SERVERPROPERTY was introduced in SQL Server 2000.
HTH
Kalen Delaney, SQL Server MVP
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23n1iyObjGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Check out SERVERPROPERTY() in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
> news:%23DGRbHbjGHA.1552@.TK2MSFTNGP03.phx.gbl...
> How can I get just the majore version number of SQL Server from SQL 7 thru
> SQL 2005 SP1 ?
> --
> Michael Tissington
> http://www.oaklodge.com
> http://www.sqlview.net
>|||Hello Michael,
Have you got any further ideas on this issue or does my last reply also
help some? If there is still anything we can help, please feel free to post
here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Steven,
Unfortunatley this does not really work.
SERVERPROPERTY only works for SQL 2000 and later.
The @.@.Version used to work consistantly, however with SP1 of SQL 2005 and
double space in the formating has been changed to a single space so I can no
longer predict where the major version number is in the string.
Any more ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.net
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:hPQMgI8jGHA.764@.TK2MSFTNGXA01.phx.gbl...
> Hello Michael,
> Have you got any further ideas on this issue or does my last reply also
> help some? If there is still anything we can help, please feel free to
> post
> here.
> Regards,
> Steven Cheng
> Microsoft MSDN Online Support Lead
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello, Michael
Try the following query:
SELECT LTRIM(SUBSTRING(@.@.VERSION,CHARINDEX('-',@.@.VERSION)+1,
CHARINDEX('.',SUBSTRING(@.@.VERSION,CHARINDEX('-',@.@.VERSION),10))-2))
This query searches for the first dot after a dash in @.@.VERSION and
returns the trimmed string between them.
Razvan
PS. I tested the above query only on SQL Server 2000 and 2005, because
I don't have a 6.5 or 7.0 server available for testing.|||Hi Michael,
Have you tried the suggestion Razvan provided?
Please let me know the result. If there is still anything we can help,
please feel free to post here.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I do not see a solution by Razvan - where is this ?
I only see posts by Steven Cheng and Tom Moreau.
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.net
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:I6D0ufdlGHA.4948@.TK2MSFTNGXA01.phx.gbl...
> Hi Michael,
> Have you tried the suggestion Razvan provided?
> Please let me know the result. If there is still anything we can help,
> please feel free to post here.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Michael,
I have posted his suggestion here. Please have a check and let me know does
this help.
From: "Razvan Socol" <rsocol@.gmail.com>
Newsgroups: microsoft.public.sqlserver.programming
Subject: Re: Major Version number
Date: 20 Jun 2006 23:59:39 -0700
Hello, Michael
Try the following query:
SELECT LTRIM(SUBSTRING(@.@.VERSION,CHARINDEX('-',@.@.VERSION)+1,
CHARINDEX('.',SUBSTRING(@.@.VERSION,CHARINDEX('-',@.@.VERSION),10))-2))
This query searches for the first dot after a dash in @.@.VERSION and
returns the trimmed string between them.
Razvan
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.