T-SQL: find SQL TCP port number - 0 views

• 
  #1 Sergey Leifer on 07 Aug 10

   

  --SQL 2000/2005 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),1) = '9'
  BEGIN
  
  
  select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
  
  if @instance_name is NULL
  BEGIN
  set @reg_key = 'SOFTWAREMicrosoftMSSQLServerMSSQlServerSuperSocketNetLibTcp'
  END
  ELSE BEGIN
  set @reg_key = 'SOFTWAREMicrosoftMicrosoft SQL Server' + @instance_name + 'MSSQLServerSuperSocketNetLibTcp'
  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', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerSupersocketnetlib cp', 'tcpPort'
  select @SqlPort = Data from #Port_2000
  select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port
  drop table #Port_2000
  
  END

   

  <div class="cArrow"> </div><div class="cContentInner">--SQL 2000/2005 Version <br /> <br /> set nocount on <br /> go <br /> DECLARE @SqlPort Nvarchar(10) <br /> DECLARE @instance_name Nvarchar(30) <br /> DECLARE @reg_key Nvarchar(500) <br /> Declare @value_name Nvarchar(20) <br /> <br /> if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9' <br /> BEGIN <br /> <br /> <br /> select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname) <br /> <br /> if @instance_name is NULL <br /> BEGIN <br /> set @reg_key = 'SOFTWAREMicrosoftMSSQLServerMSSQlServerSuperSocketNetLibTcp' <br /> END <br /> ELSE BEGIN <br /> set @reg_key = 'SOFTWAREMicrosoftMicrosoft SQL Server' + @instance_name + 'MSSQLServerSuperSocketNetLibTcp' <br /> END <br /> <br /> EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', <br /> @key=@reg_key, @value_name='TcpPort', <br /> @value=@SqlPort output <br /> <br /> select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port <br /> <br /> END <br /> <br /> <br /> if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8' <br /> BEGIN <br /> <br /> Create table #Port_2000 (value nvarchar(20),Data nVarchar(10)) <br /> insert into #Port_2000 exec master..xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWAREMicrosoftMSSQLServerMSSQLServerSupersocketnetlib cp', 'tcpPort' <br /> select @SqlPort = Data from #Port_2000 <br /> select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @SqlPort as Port <br /> drop table #Port_2000 <br /> <br /> END</div>

  ...

  Cancel

To Top