CODEWITHSUNDEEP
sqlsql-server-2005t-sql
misctp asdas
misctp asdas

Reputation: 1023

How to verify listening port number on SQL server 2005?

I would like to find out the listening port number that is on the SQL server. This have to be done via transact sql. Also, is there any way to know whether is this a static or dynamic port?

I have tried google thus far but have no success thus far.

Running T-SQL scripts returned the following error:

RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

Upvotes: 2

Views: 1560

Answers (1)

A G
A G

Reputation: 22559

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

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)

OR

http://sql-articles.com/blogs/how-to-find-tcpip-port-via-t-sql/

DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)

IF (SERVERPROPERTY('INSTANCENAME')) IS NULL    
BEGIN    
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'    
END    
ELSE    
BEGIN    
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME')) + '\MSSQLServer\SuperSocketNetLib\Tcp\'    
END    
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'

Both executed and returns the current TCP/IP Port number.

Upvotes: 2

Related Questions