July 14, 2009 at 1:05 am
Comments posted to this topic are about the item Find SQL Server TCP/IP Port Number
July 20, 2009 at 5:11 am
I think we should start using the SQL CLR to accomplish all this going forward. But, a good code though.
May 13, 2010 at 7:08 am
SQLGerman (7/20/2009)
I think we should start using the SQL CLR to accomplish all this going forward.
Not every one is capable of coding CLR modules and that's a lot of unnecessary overhead for simple info gathering, IMHO.
May 13, 2010 at 8:09 am
Nice code. Any idea where to fine the port number for SQL 2008?
Rudy
May 13, 2010 at 9:03 am
Hello everyone,
Here is the code that will also scan SQL 2008 servers
--SQL 2000/2005/2008 Version
set nocount on
go
DECLARE @SqlPort Nvarchar(10)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
Declare @value_name Nvarchar(20)
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),2) = '10'
BEGIN
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, @SqlPort as Port
END
--------------------------------------------------------------------
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
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, @SqlPort as Port
END
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
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, @SqlPort as Port
drop table #Port_2000
END
Rudy
May 19, 2016 at 7:00 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply