InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 05:07:00 GMT20http://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxThank you. Good discussion started, good job.Thu, 08 Sep 2011 10:03:05 GMTGAFhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxon 2K8: xp_ReadErrorLog 0, 1, 'listening'-- Parameters defined:-- Parameter 1 (int), is the number of the log file you want to read, default is "0" for current log. -- Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1.-- Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.-- Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULLThu, 08 Sep 2011 09:40:10 GMTnoelchttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxFigured I would throw one more method, albeit quite a bit more manual. You can also check the error log in found in the Log folder where your SQL Server is installed (for example, C:\Program Files\Microsoft SQL Server\MSSQL.1\Log). The latest log file will be named ERRORLOG (w/out the numbered extension). Using a standard text editor, like Notepad, you can do a quick search for the text 'listening'.Thu, 08 Sep 2011 08:21:44 GMTtskelleyhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxNice simple article. We could also get this information from SQL Server error log.Thu, 08 Sep 2011 05:12:00 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxYou can also use the following from the command line : [code="plain"]netstat -n -b[/code]You can then check the returned values for the executable you're interested in to see the port (or ports) being listened on. Thu, 08 Sep 2011 03:30:28 GMTSteve Smith-149662http://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxHow about using this for a TSQL based solution?:SELECT DISTINCT(local_tcp_port) FROM sys.dm_exec_connectionsWHERE net_transport='TCP'Thu, 08 Sep 2011 02:22:06 GMTLawrence Moorehttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxThanks for the tips; For info:I found out though that the only way that worked for me was to check the event details - although for that I had to restart the server to generate new events.With regards to the second option, I have no values displayed in that field, or any other fields labeled "Port" in that IP-Addresses tab.The third option had to be tinkered with a wee bit - @@SERVICENAME returns MSSS2008R2but the key is actually found under MSSQL10_50.MSSQLSERVER2008R2and then, just like for option 2, that tcp key value is empty anyway:Value DatatcpPort NULLthanks again for the tips,BThu, 08 Sep 2011 02:20:23 GMTbertrand.leroyhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxIf you are connected (using the instance name) you can also find it by query (SQl2000 and up):set nocount ongoDECLARE @SqlPort Nvarchar(10), @instance_name Nvarchar(30), @reg_key Nvarchar(500), @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\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 PortENDif 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 PortENDif 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_2000ENDThu, 08 Sep 2011 02:09:18 GMTd.majoorhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxI really liked this article. It's short and simple, and I learned something:-)Thu, 08 Sep 2011 01:27:39 GMTHåvardhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspx[quote][b]Avinash Barnwal (9/7/2011)[/b][hr]The third way doesn't seem to be option to find out which of the port SQL Server is listening, since if you are already connected to the SQL server, then [b]you already know the Port[/b].[/quote]Not completely true. You can connect using a named instance and never know the port you're attaching to.The SQL Browser service listens on port UDP 1434 and returns the port of the named instances in a datagram. The client application parses the datagram and connects to the port returned by the SQL Browser.Thu, 08 Sep 2011 01:15:54 GMTspaghettidbahttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxThe third way doesn't seem to be option to find out which of the port SQL Server is listening, since if you are already connected to the SQL server, then you already know the Port.Wed, 07 Sep 2011 23:21:40 GMTAvinash Barnwalhttp://www.sqlservercentral.com/Forums/Topic1171523-2918-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/networking/73238/">Find which port SQL Server is using to establish connections</A>[/B]Wed, 07 Sep 2011 23:20:11 GMTSQLDCH