InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 14:47:18 GMT20http://www.sqlservercentral.com/Forums/Topic752494-1597-1.aspxHello everyone,Here is the code that will also scan SQL 2008 servers--SQL 2000/2005/2008 Versionset nocount ongoDECLARE @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'BEGINselect @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)if @instance_name is NULLBEGINset @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'ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@reg_key, @value_name='TcpDynamicPorts',@value=@SqlPort outputselect CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as PortEND--------------------------------------------------------------------if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'BEGINselect @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)if @instance_name is NULLBEGINset @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'ENDELSE BEGINset @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer\SuperSocketNetLib\Tcp'ENDEXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@reg_key, @value_name='TcpPort',@value=@SqlPort outputselect CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as PortENDif left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'BEGINCreate 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_2000select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Portdrop table #Port_2000ENDThu, 13 May 2010 09:03:23 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic752494-1597-1.aspxNice code. Any idea where to fine the port number for SQL 2008?Thu, 13 May 2010 08:09:05 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic752494-1597-1.aspx[quote][b]SQLGerman (7/20/2009)[/b][hr]I think we should start using the SQL CLR to accomplish all this going forward.[/quote]Not every one is capable of coding CLR modules and that's a lot of unnecessary overhead for simple info gathering, IMHO.Thu, 13 May 2010 07:08:17 GMTBlackHawk-17http://www.sqlservercentral.com/Forums/Topic752494-1597-1.aspxI think we should start using the SQL CLR to accomplish all this going forward. But, a good code though.Mon, 20 Jul 2009 05:11:37 GMTSQLGermanhttp://www.sqlservercentral.com/Forums/Topic752494-1597-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/67422/">Find SQL Server TCP/IP Port Number</A>[/B]Tue, 14 Jul 2009 01:05:42 GMTvijendran