T-SQL Query to find the SQL Server Protocols Status | SansSQL

Thursday, April 7, 2011

T-SQL Query to find the SQL Server Protocols Status

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.
DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @value_Out VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

CREATE TABLE #SQLServerProtocols
(ProtocolName nvarchar(25),
Value nvarchar(10),
Data bit)

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null


SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via'
Insert into #SQLServerProtocols (Value,Data)
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'Enabled'
EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey,
  @value_name = 'DisplayName',
  @value = @value_Out OUTPUT
UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null
END

SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled' 
ELSE 'Disabled' END FROM #SQLServerProtocols

DROP TABLE #SQLServerProtocols

Download this script from here.

3 comments:

Milton R. said...

Sandesh,

Many thanks for the script... very useful indeed.

Anonymous said...

Thank you!! Exactly what I needed at this very moment!

T. Johnson said...

Excellent script. I've never seen one like it before, either. Thanks!

Post a Comment