CODEWITHSUNDEEP
sql-servervb.netinstallationinstancesql-server-express
Alex
Alex

Reputation: 4938

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

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?

Upvotes: 0

Views: 2121

Answers (2)

Alex
Alex

Reputation: 4938

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

Upvotes: 0

Steve
Steve

Reputation: 216243

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));
    }
}

Upvotes: 1

Related Questions