How to reliably determine which components of SQL Server 2008 are installed, using C#?

Question

I want to achieve the following programatically using C#.

Query components installed in SQL Server 2008 e.g

1. Upgrade Tools
2. Debug Symbols
3. Replication Support
4. Books Online
5. Development Tools

I have tried using SMO API for SQL Server tried to query Information Collection as well Configuration collection but could not find the any of the above three points.

I need to be able to programmatically determine which SQL Server 2008 components are installed and which are not installed, just like SQL Server Installation setup which first checks to see which components are installed.

One way is to query SQL Server Installation Log File, but it is not helpful if SQL Server is completely no there or if someone has deleted that log file.

I need to reliably determine the installed SQL Server components

Please help.

Thanks

Steve

Answer 1

Your best bet is probably to query Win32_Product via WMI. You can use WMI Code Creator v1.0 to help you create an appropriate query. You can also see WQL (SQL for WMI) for information on the supported query syntax and ManagementObjectSearcher Class.

Something like this should do the trick:

using System;
using System.Management;
using System.Windows.Forms;

namespace WMISample
{
    public class MyWMIQuery
    {
        public static void Main()
        {
            try
            {
                ManagementObjectSearcher searcher = 
                    new ManagementObjectSearcher("root\\CIMV2", 
                    "SELECT * FROM Win32_Product WHERE Name LIKE '%SQL%'"); 

                foreach (ManagementObject queryObj in searcher.Get())
                {
                    Console.WriteLine("-----------------------------------");
                    Console.WriteLine("Win32_Product instance");
                    Console.WriteLine("-----------------------------------");
                    Console.WriteLine("InstallDate: {0}", queryObj["InstallDate"]);
                    Console.WriteLine("InstallLocation: {0}", queryObj["InstallLocation"]);
                    Console.WriteLine("Name: {0}", queryObj["Name"]);
                    Console.WriteLine("SKUNumber: {0}", queryObj["SKUNumber"]);
                    Console.WriteLine("Vendor: {0}", queryObj["Vendor"]);
                    Console.WriteLine("Version: {0}", queryObj["Version"]);
                }
            }
            catch (ManagementException e)
            {
                MessageBox.Show("An error occurred while querying for WMI data: " + e.Message);
            }
        }
    }
}

1. Upgrade Tools ends with Upgrade Advisor
2. Debug Symbols are pulled from a symbol server on demand by certain applications and don't show up in Win32_Products.
3. Replication Support does not appear to be detectable via a plain WMI query to Win32_Products
4. Books Online ends with Books Online (English)
5. Development Tools ends with BI Development Studio

You can find out about installed features by running the SQL Server Setup Discovery Report (requires admin privileges):

1. Go to the Start menu
2. Click All Programs
3. Click Microsoft SQL Server
4. Click Configuration Tools
5. Click SQL Server Installation Center
6. Click the Tools section link
7. Click Installed SQL Server features discovery report

You can also create the report from a command line by running setup.exe /ACTION=RUNDISCOVERY /Q (from C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2 in the case of SQL Server 2008 R2). The report files will typically be in C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\YYYYMMDD_HHmmSS, where YYYYMMDD_HHmmSS is the date and time the report was run. SqlDiscoveryReport.xml is probably the one most useful for consuming programmatically. Be aware that these locations can vary somewhat based on the install location the user chooses during the initial setup and the version of SQL Server.