I've gotten a number of e-mails asking how to detect if SQL Express is installed on a computer. I'm finally getting around to putting this in the Blog so I don't have to keep typing the answer. We've made discovery much easier in SQL Server 2005 with the addition of the SQL Server 2005 WMI Provider. This method will work for all editions of SQL Server 2005, but the example is tailored to SQL Express.

Why can't I just use the registry?

Do a little research on this topic and you'll find a number of examples on how to use the registry to detect SQL Server. Don't do it this way!

The problem with using the registry to detect SQL Server is that Microsoft doesn't make any promises that we won't change the registry without warning. (It's our registry, we can change it.) We changed the registry between SQL 2000 and SQL 2005 and it's a good bet well do the same between 2005 and what ever comes next. Enter the SQL WMI Provider, this provider abstracts the registry and allows you to discover information about SQL Server 2005. We will be maintaining the SQL WMI provider in future versions so that your detection code will continue to work on later versions.

Do I detect a catch?

Yes, you do. The SQL WMI Provider doesn't work for SQL Server versions prior to 2005. If you need to detect earlier versions, say 2000, you're pretty much back to your old registry tricks.

What else can the SQL WMI Provider do?

What, you want more? Fine. The SQL WMI Provider actually covers a bunch of different functionality beyond detection and is broken into two separate providers:

WMI Provider for Server Events - With this provider you can monitor events for a given instance of SQL Server.

WMI Provider of Configuration Management - With this provider you can detect and manage a given instance of SQL Server.

(If the links don't work for some reason, say MSDN completely restructures its content, just search for the topics in Books Online.)

Why not use SMO?

(Added in response to Jens' comment.) 

Many observent readers (OK, it was just Jens) pointed out that SMO contains the ManagementServer namespace which offers similar functionality. I chose not to use SMO for a couple reasons:

  • WMI does not require managed code or the .NET Framework. Yes, this is a C# sample, but the same WQL would work from VBScript on a computer without the framework installed. Sometimes it's important not to have a dependency on managed code.
  • The SQL WMI Provider calls seem to handle being run on computers without SQL Server 2005 installed where SMO did not in my hands. I just had problems getting SMO based code to fail correct when SQL wasn't installed. I'm no SMO genious, so it's likely this was pilot error.

WMI is the detection mechanism recommend by our Servicing and Lifecycle Platform team and it does not have as many prerequisite as SMO. If you can count on the correct prerequisites being on the computer where you're wanting to detect SQL Express, feel free to check out the SMO ManagementServer namespace. If you want to use the recommended method, stick with WMI.

Hey Explanation Boy, how about a sample?

Ok already, I'll get to the sample. It’s in C# if you didn’t notice.

using System;
using System.Management;

namespace WMISample
{
    // The WMI query for this class was created using the WMI Code Creator tool
    // that is available from
    // http://www.microsoft.com/downloads/details.aspx?FamilyID=2cc30a64-ea15-4661-8da4-55bbc145c30e&DisplayLang=en
    public class MyWMIQuery
    {
        public static void Main()
        {
            bool foo = isExpressInstalled();
            if (foo)
            {
                Console.WriteLine("You have SQL Express SP1. Sweet!!");
            }
            else
            {
                Console.WriteLine("No instances named SQLEXPRESS exists on this computer.");
            }
            Console.WriteLine("Hit Enter to continue.");
            Console.Read();
        }

        public static bool isExpressInstalled()
        {
            const string edition = "Express Edition";
            const string instance = "MSSQL$SQLEXPRESS";
            const int spLevel = 1;

            bool fCheckEdition = false;
            bool fCheckSpLevel = false;

            try
            {
                // Run a WQL query to return information about SKUNAME and SPLEVEL about installed instances
                // of the SQL Engine.
                ManagementObjectSearcher getSqlExpress =
                    new ManagementObjectSearcher("root\\Microsoft\\SqlServer\\ComputerManagement",
                    "select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and ServiceName = '" 
                    + instance + "' and (PropertyName = 'SKUNAME' or PropertyName = 'SPLEVEL')");

                // If nothing is returned, SQL Express isn't installed.
                if (getSqlExpress.Get().Count==0)
                {
                    return false;
                }
                
                // If something is returned, verify it is the correct edition and SP level.
                foreach (ManagementObject sqlEngine in getSqlExpress.Get())
                {
                    if (sqlEngine["ServiceName"].ToString().Equals(instance))
                    {
                        switch (sqlEngine["PropertyName"].ToString())
                        {
                            case "SKUNAME":
                                // Check if this is Express Edition or Express Edition with Advanced Services
                                fCheckEdition = sqlEngine["PropertyStrValue"].ToString().Contains(edition);
                                break;

                            case "SPLEVEL":
                                // Check if the instance matches the specified level
                                fCheckSpLevel = int.Parse(sqlEngine["PropertyNumValue"].ToString()) >= spLevel;
                                //fCheckSpLevel = sqlEngine["PropertyNumValue"].ToString().Contains(spLevel);
                                break;
                        }
                    }
                }

                if (fCheckEdition & fCheckSpLevel)
                {
                    
                    return true;
                }
                return false;
            }
            catch (ManagementException e)
            {
                Console.WriteLine("Error: " + e.ErrorCode + ", " + e.Message);
                return false;
            }
        }
    }
}

isExpressInstalled() is the worker bee here. This method runs a WQL query that specifically looks for instances of the SQL Server Engine (SQLServiceType = 1) where the Instance Name is SQLEXPRESS (ServiceName = MSSQL$SQLEXPRESS). In the sample, I happen to pass the Instance Name as a variable instance, but you can do it any way you want. I choose to look specifically for SQLEXPRESS for a couple reasons: It's the default Instance Name, Visual Studio Express and ClickOnce deployment use this Instance Name, and we'd like to see more applications pointing to a single Instance Name, so the default makes sense. Once it's established that an instance named SQLEXPRESS exists, a further check is made to ensure that it is actually one of the SQL Express Editions and that it is running at the correct service pack level, in this case, SP1

That's pretty much it, a few checks to see what happened, and the function returns either True or False to the calling routine. You can call this from where ever you want and adjust the parameters as appropriate. One final note, check out the WMI Code Creator as mentioned in the code comment. This is the tool that will help you explore the SQL Server 2005 WMI Provider to find other ways to use it and other properties that you can query.

Mike

Addendum

Barry Sumpter converted this code into VB.NET in a forum post and I thought I'd add it here so it's easier to find.

Imports System
Imports System.Management

Namespace WMISample

    Public Class MyWMIQuery

        Public Shared Sub Main()

            Dim foo As Boolean = isExpressInstalled()

            If foo Then

                Console.WriteLine("You have SQL Express SP1. Sweet!!")

            Else

                Console.WriteLine("No instances named SQLEXPRESS exists on this computer.")

            End If

            Console.WriteLine("Hit Enter to continue.")

            Console.Read()

        End Sub

        Public Shared Function isExpressInstalled() As Boolean

            Const edition As String = "Express Edition"

            Const instance As String = "MSSQL$SQLEXPRESS"

            Const spLevel As Integer = 1

            Dim fCheckEdition As Boolean = False

            Dim fCheckSpLevel As Boolean = False

            Try

                Dim getSqlExpress As ManagementObjectSearcher = New ManagementObjectSearcher("root\Microsoft\SqlServer\ComputerManagement", "select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and ServiceName = '" + instance + "' and (PropertyName = 'SKUNAME' or PropertyName = 'SPLEVEL')")

                If getSqlExpress.Get.Count = 0 Then

                    Return False

                End If

                For Each sqlEngine As ManagementObject In getSqlExpress.Get

                    If sqlEngine("ServiceName").ToString.Equals(instance) Then

                        Select Case sqlEngine("PropertyName").ToString

                            Case "SKUNAME"

                                fCheckEdition = sqlEngine("PropertyStrValue").ToString.Contains(edition)

                            Case "SPLEVEL"

                                fCheckSpLevel = Integer.Parse(sqlEngine("PropertyNumValue").ToString) >= spLevel

                        End Select

                    End If

                Next

                If fCheckEdition And fCheckSpLevel Then

                    Return True

                End If

                Return False

            Catch e As ManagementException

                Console.WriteLine("Error: " + e.ErrorCode + ", " + e.Message)

                Return False

            End Try

        End Function

    End Class

End Namespace