How to query instance ports.

• Duran

  SSChampion

  Points: 13259

  More actions

  March 12, 2009 at 10:37 am

  #131815

  Hello,

  I was just wondering how someone would find out what ports sql instances are using. Say you had 3 instances and you didnt know the ports, how would a person find out? I know the default is 1433, would all other named instances use 1434?

  Thanks for any help!

  Regards,

  D.

• Lowell

  SSC Guru

  Points: 323546

  More actions

  March 12, 2009 at 10:41 am

  #958500

  the info is in the registry...i've got this script saved in my snippets, it used to work well:

  CREATE TABLE #GetPort

  (

  token varchar(100),

  value varchar(20))

  go

  DECLARE @inst varchar(200)

  DECLARE @inst1 varchar(100)

  --Determine registry path and key

  IF(charindex('\',@@servername) > 0)

  BEGIN

  SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)

  SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'

  --SELECT @inst1 = 'TcpDynamicPorts'

  SELECT @inst1 = 'TcpPort'

  END

  ELSE

  BEGIN

  if SUBSTRING(@@VERSION,23,1) = '7'

  begin

  SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'

  SELECT @inst1 = 'DefaultServerPort'

  end

  else

  begin

  SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'

  SELECT @inst1 = 'DefaultPort'

  end

  END

  print @inst + '\\\\////'+ @inst1

  INSERT #GetPort

  EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

  SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

  DROP TABLE #GetPort

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!