Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Get SQL server version + port #

Posted by Simon Cho on 04/11/2011

http://www.sqlservercentral.com/Forums/Topic912647-2621-1.aspx


DECLARE @Server AS VARCHAR(128)

DECLARE @KeyToInterogate AS VARCHAR(200)

DECLARE @PortNumber AS VARCHAR(8)

SET @Server = @@SERVERNAME

SET @KeyToInterogate = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’

IF Charindex(‘\’,@@SERVERNAME) > 0

BEGIN

SET @KeyToInterogate = ‘SOFTWARE\Microsoft\Microsoft SQL Server\’

SET @KeyToInterogate = @KeyToInterogate + Substring(@@SERVERNAME,Charindex(‘\’,@@SERVERNAME) + 1,Len(@@SERVERNAME)  Charindex(‘\’,@@SERVERNAME))

SET @KeyToInterogate = @KeyToInterogate + ‘\MSSQLServer\SuperSocketNetLib\Tcp’

END

 

EXEC Xp_regread

@rootkey = ‘HKEY_LOCAL_MACHINE’,

@key = @KeyToInterogate,

@value_name = ‘TcpPort’,

@value = @PortNumber OUTPUT

SELECT @@SERVERNAME AS hostname

, Serverproperty(‘Edition’) AS edition

, Isnull(Serverproperty(‘InstanceName’),) AS instancename

, Serverproperty(‘MachineName’) AS machinename

, Serverproperty(‘ProductVersion’) AS productversion

, CASE

WHEN CONVERT(VARCHAR(255),Serverproperty(‘ProductVersion’)) LIKE ‘8.0%’ THEN ‘2000’

WHEN CONVERT(VARCHAR(255),Serverproperty(‘ProductVersion’)) LIKE ‘9.0%’ THEN ‘2005’

WHEN CONVERT(VARCHAR(255),Serverproperty(‘ProductVersion’)) LIKE ‘10.0%’ THEN ‘2008’

WHEN CONVERT(VARCHAR(255),Serverproperty(‘ProductVersion’)) LIKE ‘10.5%’ THEN ‘2008 R2’

END

, CAST(@PortNumber AS VARCHAR) AS portno

 

 

Simon Cho
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: