January 20, 2011 at 8:16 pm
Comments posted to this topic are about the item SQL Server Port Number Identifier
Rudy
January 20, 2011 at 8:18 pm
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[/url]
Learn Extended Events
January 21, 2011 at 9:14 am
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
January 21, 2011 at 10:55 am
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
Rudy
January 21, 2011 at 11:00 am
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.
January 21, 2011 at 11:24 am
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
January 21, 2011 at 2:00 pm
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?
[font="Courier New"]____________________________________________________________________________________________
Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog[/font]
February 16, 2011 at 9:31 am
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.
February 16, 2011 at 12:21 pm
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
February 16, 2011 at 12:23 pm
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
Rudy
May 24, 2011 at 1:06 am
When I run on my instance (dynamic port) this gives null . Not much use
Following gives the port
declare @tcpport varchar(1000),@value varchar(250);set @tcpport = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\SuperSocketNetLib\Tcp';EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @tcpport, @value_name = 'tcpPort', @value = @value OUTPUT;select @value
May 24, 2011 at 1:32 am
M A Srinivas (5/24/2011)
When I run on my instance (dynamic port) this gives null . Not much useFollowing gives the port
declare @tcpport varchar(1000),@value varchar(250);set @tcpport = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@servicename + '\MSSQLServer\SuperSocketNetLib\Tcp';EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @tcpport, @value_name = 'tcpPort', @value = @value OUTPUT;select @value
Did you use the script for right version?
Have you given correct file paths. Also check the select @value
Try this
declare @tcpport varchar(1000),@value varchar(250);
set @tcpport = 'SOFTWARE\Microsoft\MSSQLServer\' + @@servicename + '\MSSQLServer\SuperSocketNetLib\Tcp';EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = @tcpport, @value_name = 'tcpPort', @value = @value OUTPUT;
select @value
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply