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.

4 Comments:

At 8:15 AM, Blogger Bowker Family said...

OUTSTANDING post, thank you.

 
At 11:01 AM, Blogger Kenny Younger 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

 

Post a Comment

<< Home