How to verify if SQL Express (2014) is installed AND which Instance

Question

Goal

I want to determine if the user has SQL Server Express 2014 installed. The version is important to me. I then want to make sure this user has the instance "SQLEXPRESS" on his 2014 server.

Current code

I have a function that returns a boolean value if SQLEXPRESS is installed, but does not take into consideration the version (2008/2010/2012/2014)

Private Function SQLExpressInstalled() As Boolean
    Try
        Using key As RegistryKey = Registry.LocalMachine.OpenSubKey("Software\\Microsoft\\Microsoft SQL Server\\", False)
            If key Is Nothing Then Return False

            Dim strNames() As String
            strNames = key.GetSubKeyNames

            'If we cannot find a SQL server registry key, we don't have SQL Server Express installed
            If strNames.Length = 0 Then Return False
            If strNames.Contains("SQLEXPRESS") Then
                Return True
            End If
        End Using
    Catch ex As Exception
        'MsgBox(ex.Message)
    End Try

    Return False
End Function

Is there a way to pinpoint the version as well as which instance is installed on the given version?

@Steve I see. In order to connect to the correct one (lets say I have 2008 and 2010 installed) I must have the proper instance. How would I know that the instance SQLEXPRESS is not on 2010? — Alex, Apr 14 '16 at 14:11

Steve · Answer 1 · 2016-04-14T14:38:28.433

1

I could show a little example on how to do it using the SqlDataSourceEnumerator but I am not sure if it works good for you. I let you test it

using System.Data.Sql;

SqlDataSourceEnumerator sqe = SqlDataSourceEnumerator.Instance;
DataTable dt = sqe.GetDataSources();

// Here the DataTable has a column called Version, 
// but in my tests it is always null, so let's go with
// the SELECT @@version approach

foreach (DataRow row in dt.Rows)
{
    SqlConnectionStringBuilder scb = new SqlConnectionStringBuilder();
    scb.DataSource = row.Field<string>("ServerName");
    if(!row.IsNull("InstanceName"))
        scb.DataSource += "\\" + row.Field<string>("InstanceName");


    // Another major problem is the Authetication rules for the 
    // current instance, I just assume that IntegratedSecurity works also for you
    // scb.UserID = "xxxx";
    // scb.Password = "xxxx";
    scb.IntegratedSecurity = true;
    scb.InitialCatalog = "master";
    using (SqlConnection cnn = new SqlConnection(scb.ConnectionString))
    using (SqlCommand cmd = new SqlCommand("SELECT @@Version", cnn))
    {
        Console.WriteLine("Version for: " + row.Field<string>("ServerName"));
        cnn.Open();
        string result = cmd.ExecuteScalar().ToString();

        // Now a bit of parsing will be required to isolate the information needed
        Console.WriteLine(result));
    }
}

edited Apr 14 '16 at 14:38

answered Apr 14 '16 at 14:29

Steve

213,761
22
232
286

Okay, since I'm doing this locally I guess I can just compare the user's computer name to the servers found using your code? Once the computer name matches the local server, I know I'm on the users local server... Right? – Alex Apr 14 '16 at 14:47
I think so, but at the moment I have just two network instances of Sql Server to test so... let's wait if someone has a better approach to this – Steve Apr 14 '16 at 14:50
For the moment this is helpful though, I'll try to work something out with this technique – Alex Apr 14 '16 at 14:56
From what I noticed is that it isn't able to fetch the "InstanceName". It seems to always be null. Is there a reason for this? – Alex Apr 14 '16 at 15:03
No idea, I haven't any SQLEXPRESS or named instance to test – Steve Apr 14 '16 at 15:11

score 0 · Accepted Answer · edited May 23 '17 at 11:59

I found another solution from one of marc_s' posts here

The code being:

Private Sub SQLInformation()
    ' open the 64-bit view of the registry, if you're using a 64-bit OS
    Dim baseKey As RegistryKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64)

    ' find the installed SQL Server instance names
    Dim key As RegistryKey = baseKey.OpenSubKey("SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL")

    ' loop over those instances
    For Each sqlInstance As String In key.GetValueNames()
        Console.WriteLine("SQL Server instance: {0}", sqlInstance)

        ' find the SQL Server internal name for the instance
        Dim internalName As String = key.GetValue(sqlInstance).ToString()
        Console.WriteLine(vbTab & "Internal instance name: {0}", internalName)

        ' using that internal name - find the "Setup" node in the registry
        Dim instanceSetupNode As String = String.Format("SOFTWARE\Microsoft\Microsoft SQL Server\{0}\Setup", internalName)

        Dim setupKey As RegistryKey = baseKey.OpenSubKey(instanceSetupNode, False)

        If setupKey IsNot Nothing Then
            ' in the "Setup" node, you have several interesting items, like
            ' * edition and version of that instance
            ' * base path for the instance itself, and for the data for that instance
            Dim edition As String = setupKey.GetValue("Edition").ToString()
            Dim pathToInstance As String = setupKey.GetValue("SQLBinRoot").ToString()
            Dim version As String = setupKey.GetValue("Version").ToString()

            Console.WriteLine(vbTab & "Edition         : {0}", edition)
            Console.WriteLine(vbTab & "Version         : {0}", version)
            Console.WriteLine(vbTab & "Path to instance: {0}", pathToInstance)
        End If
    Next
End Sub

How to verify if SQL Express (2014) is installed AND which Instance

Goal

Current code

2 Answers2