tsql to know if the server has integration,reporting,analysis,notification services

Question

Hi,

I want to know if a server has integration,reporting,analysis,notification services irrespective of the version(2000 or 2005).

Currently, i am using

xp_cmdshell 'net start'

but it has too much information.

Any help?

Answer 1

Here's 2005/2008 code. For 2000, you'll have to grovel in the registry yourself, because it's stored elsewhere I believe. Also, you'll have to change it from a table variable to an actual temp table.

Enjoy.

Declare @Instances Table
(InstanceName SysName, RegKey SysName, InstanceType Character Varying(50), 
 Version Character Varying(4), Features National Character Varying(2000))

Insert Into @Instances(InstanceName, RegKey)
Execute Master.dbo.xp_RegEnumValues N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
Update @Instances Set InstanceType = 'SQL Server' Where InstanceType Is Null

Insert Into @Instances(InstanceName, RegKey)
Execute Master.dbo.xp_RegEnumValues N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\RS'
Update @Instances Set InstanceType = 'Reporting Services' Where InstanceType Is Null

Insert Into @Instances(InstanceName, RegKey)
Execute Master.dbo.xp_RegEnumValues N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\OLAP'
Update @Instances Set InstanceType = 'Analysis Services' Where InstanceType Is Null

Declare 
 @More Bit, @CRegKey SysName, @RegPath National Character Varying(2000),
 @Features National Character Varying(2000), @VersionString National Character Varying(500) 

Declare CInstance Cursor
For
Select RegKey From @Instances

Open CInstance
Set @More = 1

While (@More = 1)
Begin
 Fetch Next From CInstance Into @CRegKey
 If (@@Fetch_Status != 0)
  Set @More = 0
 Else
     Begin
   Set @RegPath = N'Software\Microsoft\Microsoft SQL Server\' + @CRegKey + '\Setup'

   Execute Master.dbo.xp_RegRead N'HKEY_LOCAL_MACHINE', @RegPath, N'FeatureList', @Features Output, 'no_output'
   Execute Master.dbo.xp_RegRead N'HKEY_LOCAL_MACHINE', @RegPath, N'PatchLevel', @VersionString Output, 'no_output'
   -- \' ignore this, it's just to get the formatting right 
   Update @Instances
   Set
    Features = @Features,
    Version = (Case When Left(@VersionString, 1) = '9' Then '2005'
                    When Left(@VersionString, 2) = '10' Then '2008'
                    Else '????' End)
   Where Current Of CInstance
  End
End    

Close CInstance
Deallocate CInstance

Update @Instances Set InstanceName = '(local)' Where InstanceName = 'MSSQLServer'

Select InstanceName, InstanceType, Version, Features 
From @Instances