Agent Jobs

Get The SQL Server Port Number With This Simple Query

Get the SQL Server Port Number with this Simple Query

use master;
set nocount on

declare @key	varchar(255)
declare	@port	varchar(50)

-- get the port number using the serverproperty function and xp_regread
if charindex('\',convert(char(255), serverproperty('servername')),0) <>0
	begin
		set @key = 'software\microsoft\microsoft sql server\' + @@servicename + '\mssqlserver\supersocketnetlib\tcp'
	end
else
	begin
		set @key = 'software\microsoft\mssqlserver\mssqlserver\supersocketnetlib\tcp'
	end
exec master..xp_regread
	@rootkey	= 'hkey_local_machine'
,	@key		= @key
,	@value_name	= 'tcpport'
,	@value		= @port output

select
	'ServerName'	= @@servername
,	'Port'		= convert(varchar(10), @port)
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