Find SQL Server TCP/IP Port Number

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Vijendran R » Find SQL Server TCP/IP Port Number

Find SQL Server TCP/IP Port Number

Rate Topic

Display Mode

Topic Options

Author

Message

vijendran

Posted Tuesday, July 14, 2009 1:05 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 22, 2013 9:46 AM
Points: 84, Visits: 421

Comments posted to this topic are about the item Find SQL Server TCP/IP Port Number

Post #752494

SQLGerman

Posted Monday, July 20, 2009 5:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 02, 2011 2:13 AM
Points: 5, Visits: 22

I think we should start using the SQL CLR to accomplish all this going forward. But, a good code though.

Post #755659

BlackHawk-17

Posted Thursday, May 13, 2010 7:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:50 AM
Points: 41, Visits: 384

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.

Post #921225

Rudy Panigas

Posted Thursday, May 13, 2010 8:09 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 306, Visits: 1,016

Nice code. Any idea where to fine the port number for SQL 2008?

Post #921286

Rudy Panigas

Posted Thursday, May 13, 2010 9:03 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 306, Visits: 1,016

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

Post #921369

« Prev Topic | Next Topic »

Permissions