April 11, 2008 at 10:28 am
Is there a system-object query that can give me the port number of a SQL Server instance?
I usually get this information from the SQL Log. Is there a better, more elegant way?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 11, 2008 at 10:38 am
Hi,
You can use extended sp Exec Xp_RegRead to get the port number.
Declare @PortNumber VarChar(7)
Exec Xp_RegRead
@RootKey='HKEY_LOCAL_MACHINE',
@key='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',
@Value_Name='TcpPort',
@Value=@PortNumber OutPut
Print 'Port Number:'
Print @PortNumber
April 11, 2008 at 11:31 am
thank you, but I get this error when running this:
RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
Port Number:
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 12, 2008 at 8:12 am
The exact registry key will depend on the version of SQL Server and whether or not it's a named instance. The example given was for a SQL Server 2000 default instance. Also, since what directory SQL Server will install in will differ based on installation order for SQL Server 2005 (and this carries over to the registry as well), typically what we do is hard code our named instances to known ports. Therefore, your best bet is probably to use the SQL Server log or the application event log.
K. Brian Kelley
@kbriankelley
April 12, 2008 at 9:12 am
To follow up on Brian's comment, the bolded part is the "servicename" for the instance:
'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
In a named instance that will usually be formatted as MachineName$instance.
So you'd have to dynamically change the instance name, or use consistent predictable ports. Or use aliases (which once set up wont require knowing port numbers), or run SQLBrowser. Lots of options!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 12, 2008 at 9:31 am
Matt Miller (4/12/2008)
To follow up on Brian's comment, the bolded part is the "servicename" for the instance:
'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'In a named instance that will usually be formatted as MachineName$instance.
So you'd have to dynamically change the instance name, or use consistent predictable ports. Or use aliases (which once set up wont require knowing port numbers), or run SQLBrowser. Lots of options!
This is true for 2000, but it's worse for 2005... you'll find 2005 putting the info in a registry key following the following format (where N in MSSQL.N is a whole number):
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.N\MSSQLServer\SuperSocketNetLib\TCP\IPAll
And depending you've statically configured the port or not, the port information will either be in:
TcpDynamicPorts
TcpPort
K. Brian Kelley
@kbriankelley
April 12, 2008 at 9:44 am
Thanks guys!
I have been able to locate the following path in the registry, but there is no "port" entry under there:
Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\
I wonder if I'm looking in the right place...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 12, 2008 at 10:01 am
Are there other MSSQL.N folders? The folders are numbered based on when the services were installed.
K. Brian Kelley
@kbriankelley
April 12, 2008 at 10:48 am
How about checking the active TCP endpoints external of SQL to get the port? Use something like TaskList to get the pid for the SQL service and Netstat to associte the pid with the port(s).
C:\TASKLIST /FO LIST /FI "IMAGENAME eq sqlservr.exe"
C:\NETSTAT -oan | FINDSTR /c:" <pid>"
April 14, 2008 at 10:48 am
K. Brian Kelley (4/12/2008)
Are there other MSSQL.N folders? The folders are numbered based on when the services were installed.
The root directory for my instance is: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.
I have no other MSSQL.N folders
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 14, 2008 at 12:29 pm
Marios Philippopoulos (4/12/2008)
Thanks guys!I have been able to locate the following path in the registry, but there is no "port" entry under there:
Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\
I wonder if I'm looking in the right place...
Did you check to see if you had a IPALL registry "folder" ? Meaning - one level below where you're looking?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply