SQL Server 2008 R2 Port? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL Server 2008 R2 Port?

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-02-09 : 13:59:38

Folks:

I have 2 instances running on 1 server and I am trying to find the Port Number for each instance using the below script and it displays me as NULL. I am able to connect to the server using Management Studio without any problems. Any Idea why the 2 SQL Instances are configured as NULL and I am still able to connect?

DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @tcp_port OUTPUT

select @tcp_port

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-09 : 14:24:57

Your key is incorrect:

DECLARE @tcp_port nvarchar(5)

EXEC xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.InstanceNameGoesHere\MSSQLServer\SuperSocketNetLib\Tcp\IPAll',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT

select @tcp_port

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-02-09 : 14:42:23

Still it reflects 'NULL'. Here are the parameters under IPAll in registry. There is nothing under TcpPort.

DisplayName Any IP Address
TcpDynamicPorts 49677
TcpPort

Thanks !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-09 : 15:03:32

I use a static port, so the key I posted works for me. You'll need to determine what is appropriate for a dynamic port.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources