Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Port Number Identifier


SQL Server Port Number Identifier

Author
Message
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 1304
Comments posted to this topic are about the item SQL Server Port Number Identifier



SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22792 Visits: 18261
Looks good. Thanks for posting the script.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

tskelley
tskelley
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 1157
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







Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 1304
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



Derrick Smith
Derrick Smith
Say Hey Kid
Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)Say Hey Kid (700 reputation)

Group: General Forum Members
Points: 700 Visits: 715
Doesn't seem to work on clustered instances..at least not the 3 I tried. Sad

Very handy in any case though for all non-clustered servers. Going to get much use out of this.
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 1304
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



Thordog
Thordog
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 865
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

anand13685
anand13685
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 1304
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.



Rudy Panigas
Rudy Panigas
Mr or Mrs. 500
Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)Mr or Mrs. 500 (550 reputation)

Group: General Forum Members
Points: 550 Visits: 1304
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search