Query / stored procedure to find the port number

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 2000 Forums

Transact-SQL (2000)

Query / stored procedure to find the port number

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2004-08-04 : 12:51:07

We have lots of instances on our SQL Server.
Each of them therefore has to have its own port through which it communicates.

I wonder if there is a query or stored procedure that we can run to find out that port number for a given instance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 12:56:04

You can use xp_regread to get the port information from the registry.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 12:57:57

Here's how to get the default instance's port:

DECLARE @port varchar(5)

EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',
@value_name='TcpPort',
@value=@port OUTPUT

PRINT @port

The key will be slightly different for each instance, I believe.

Tara

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2004-08-04 : 14:00:55

thanks, i seem to be getting closer. but it did not work.
but not quite there, I will tweak the query a bit and get back to you folks. Thanks so much for quick turnaround

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 14:10:21

The named instances:

DECLARE @port varchar(5)

EXEC xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\MSSQLServer\SuperSocketNetLib\Tcp',
@value_name='TcpPort',
@value=@port OUTPUT

PRINT @port

Change InstanceName to the name of the instance.

Tara

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2004-08-04 : 15:02:35

This is the script
===============================
DECLARE @test varchar(20)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',
@value_name='TcpPort',
@value=@test OUTPUT
SELECT @test
==========================================
I got this error
==========================================
Msg 22001, Level 1, State 22001
RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

--------------------
NULL
===========================================
My question is can this be run remotely from my machine? pointing to the server in question?
what about hte ports for the instances?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 15:05:06

Yes it can be run remotely from any machine as long as you are connected to the database server in Query Analyzer.

I posted the query for the instances.

The code that you posted works fine on my machine which is pointing to a database server.

You might not have permissions to use xp_regread. Are you sysadmin on the SQL Server?

Make sure that the registry key is valid on the database server. It should be if you've got a default instance installed.

Tara

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2004-08-04 : 18:51:42

Thanks Tara, I had missed the sql instance part of it(they call me intelligent for nothing)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources