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
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=@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.

5 Comments:

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