Come out, come out, wherever you are: Finding SSNS instances using T-SQL
As database professionals, we may responsible for dozens, if not scores, of SQL Servers throughout our department or enterprise. Now that Microsoft has announced that Notification Services will not ship as part of the SQL Server 2008 product, how can you readily identify which of the servers in your charge have SSNS instances installed?
Fortunately for us it's rather easy. SSNS 2005 registers each installed instance in the msdb system database. The following query returns a list of every SSNS instance for the SQL Server instance.
SELECT
*
FROM
msdb.NS90.NSInstanceInfo
*
FROM
msdb.NS90.NSInstanceInfo
To retrieve a list of all SSNS applications for the given SQL Server instance, you can run the following query.
SELECT
*
FROM
msdb.NS90.NSApplicationInfo
*
FROM
msdb.NS90.NSApplicationInfo
As you make preparations for Katmai, you can run these scripts using your mechanism of choice to examine each server in your environment.
Cheers!
Joe
