Monday, November 06, 2006

T-SQL Script to find n/w port of SQL Server

T-SQL Script

set nocount on
DECLARE @test varchar(20), @key varchar(100)
if charindex('\',@@servername,0) <>0
set @key = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'
set @key = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@key,@value_name='Tcpport',@value=@test OUTPUT

SELECT 'Server Name: '+@@servername + ' Port Number:'+convert(varchar(10),@test)

The above T-SQL reads the SQL Server Port number from the registry for the current SQL Server instance.


At 8:15 AM, Blogger Kevin said...

OUTSTANDING post, thank you.

At 11:01 AM, Blogger kly3 said...

You're missing the directory 'Microsoft SQL Server' in the second part of the if statement. Otherwise - very helpful. Thanks.

At 1:09 PM, Blogger Hai Yan (Henry) Liang said...

Great stuff. Work perfect as is

At 11:48 PM, Blogger Ace said...

Great ......... Thanks

At 9:21 AM, Blogger Philips Huges said...

Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

Personal Installment Loans
Title Car loan
Cash Advance Loan


Post a Comment

<< Home