Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

server information include port #

Posted by Simon Cho on 08/20/2012

http://smartypeeps.blogspot.com/2006/11/t-sql-script-to-find-nw-port-of-sql.html

declare @Server as varchar(128) 
declare @KeyToInterogate as varchar(200)
declare @Version as varchar (512) 
declare @PortNumber as varchar(8) 

set @Server = @@ServerName 
set @Version = left(@@Version, 38) 
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 

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'ProductName'

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'CSDVersion'
                    
SELECT @@servername AS hostname
, SERVERPROPERTY('Edition') AS Edition
, ISNULL(SERVERPROPERTY('InstanceName'),'') AS InstanceName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') as ProductLevel 
, @@VERSION
, 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)
, (select top 1 local_net_address from sys.dm_exec_connections
where local_net_address is not null) as IP_address

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;

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: