Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Port Number Identifier Expand / Collapse
Author
Message
Posted Thursday, January 20, 2011 8:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:25 AM
Points: 312, Visits: 1,108
Comments posted to this topic are about the item SQL Server Port Number Identifier


Post #1051292
Posted Thursday, January 20, 2011 8:18 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
Looks good. Thanks for posting the script.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1051294
Posted Friday, January 21, 2011 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:58 AM
Points: 39, Visits: 905
Loved the script, as we come across all types of environments and frequently need the extra info.

I added a slight tweak to include SQL Version as part of the output:

-- Show instance, SQL version, and port number for SQL 2000/2005/2008/2008R2 Version

set nocount on
go
DECLARE @SqlPort Nvarchar(10)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
DECLARE @value_name Nvarchar(20)
DECLARE @ProductVersion Nvarchar(10)


-- Scan for SQL 2008R2
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.50'
BEGIN

select @ProductVersion = '2008R2'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
--END
--ELSE BEGIN
--set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpDynamicPorts',
@value=@SqlPort output

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

END


-- Scan for SQL 2008
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),5) = '10.0.'
BEGIN

select @ProductVersion = '2008'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp'
--END
--ELSE BEGIN
--set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpDynamicPorts',
@value=@SqlPort output

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

END


-- Scan for SQL 2005
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN

select @ProductVersion = '2005'
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='TcpPort',
@value=@SqlPort output

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port

END


-- Scan for SQL 2000
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN

select @ProductVersion = '2000'
Create table #Port_2000 (value nvarchar(20),Data nVarchar(10))
insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Supersocketnetlib\tcp', 'tcpPort'
select @SqlPort = Data from #Port_2000
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @ProductVersion as SQLVersion, @SqlPort as Port
drop table #Port_2000

END






Post #1051627
Posted Friday, January 21, 2011 10:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:25 AM
Points: 312, Visits: 1,108
Nice addition My original version had SQL server version but I collect that information with another script so I removed it.

Glad to see that other find the code useful.

Rudy



Post #1051711
Posted Friday, January 21, 2011 11:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 12:57 PM
Points: 575, Visits: 692
Doesn't seem to work on clustered instances..at least not the 3 I tried.

Very handy in any case though for all non-clustered servers. Going to get much use out of this.
Post #1051714
Posted Friday, January 21, 2011 11:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:25 AM
Points: 312, Visits: 1,108
The code will not work on a cluster unless you run it from the node(s) and not from the virtual SQL server.

Give it a try and let us know.

Rudy



Post #1051729
Posted Friday, January 21, 2011 2:00 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 12:46 PM
Points: 438, Visits: 757
Rudy Panigas (1/21/2011)
The code will not work on a cluster unless you run it from the node(s) and not from the virtual SQL server.

Give it a try and let us know.

Rudy


Rudy, it seemed to work for me against my SQL 2000 cluster virtual... Maybe it doesn't work against newer versions?


____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
Post #1051826
Posted Wednesday, February 16, 2011 9:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 20, 2013 10:15 AM
Points: 1, Visits: 127
If you have access to SQL server error logs...which any DBA would....just check the time when the SQL server was last started, and in the startup parameters, it gives the port that the instance is listening on.
or open sql server configuration manager, and click on the tcp properties where you can set the port on which SQL should listen.
Post #1065093
Posted Wednesday, February 16, 2011 12:21 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:25 AM
Points: 312, Visits: 1,108
Thordog (1/21/2011)
Rudy Panigas (1/21/2011)
The code will not work on a cluster unless you run it from the node(s) and not from the virtual SQL server.

Give it a try and let us know.

Rudy


Rudy, it seemed to work for me against my SQL 2000 cluster virtual... Maybe it doesn't work against newer versions?


Glad it worked.



Post #1065215
Posted Wednesday, February 16, 2011 12:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 8:25 AM
Points: 312, Visits: 1,108
anand13685 (2/16/2011)
If you have access to SQL server error logs...which any DBA would....just check the time when the SQL server was last started, and in the startup parameters, it gives the port that the instance is listening on.
or open sql server configuration manager, and click on the tcp properties where you can set the port on which SQL should listen.


Good point. I was doing that before and thought that there must be an easier way. So I worked on some T-SQL code and that's how this code was created.

Thanks



Post #1065219
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse