CODEWITHSUNDEEP
c#sql-server
JD.
JD.

Reputation: 15531

Check if SQL server (any version) is installed?

I need to find if SQL server is installed on a machine. It could be any version of SQL server (7, 2005,8, sql express etc). We need to know this information as we are writing an installer and need to show to the user that if SQL server has not been found, the installation cannot proceed.

I have seen versions that use the registry, wmi, SMO or simply just connect to SQL server instance (although would not help here as we do not know the server name).

We are using the Wix Installer.

What is the correct way to do this?

JD

Upvotes: 22

Views: 63606

Answers (6)

Ahmed Fekry
Ahmed Fekry

Reputation: 104

Add a reference to System.ServiceProcess

then we can query all the services and get any service with SQL string

ServiceController[] sc = ServiceController.GetServices();

        foreach (ServiceController item in sc)
        {
            if (item.ServiceName.Contains("SQL"))
            {
                MessageBox.Show($@"Service Name: {item.ServiceName}"+"\n"+$@" Status: {item.Status}");
            }
        }

Upvotes: 0

Joseph Wambura
Joseph Wambura

Reputation: 3396

Another helpful but, late (10 years ago) answer:

public static bool CheckSQLInstalled()
    {
        bool isOk1 = false;
        bool isOk2 = false;
        RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;
        if (Environment.Is64BitOperatingSystem)
        {
            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
            {
                RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
                if (instanceKey != null)
                {
                    foreach (var instanceName in instanceKey.GetValueNames())
                    {                           
                        isOk2 = true;
                        break;
                    }
                }
            }
        }
        using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
        {
            RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
            if (instanceKey != null)
            {
                foreach (var instanceName in instanceKey.GetValueNames())
                {
                    isOk1 = true;
                    break;
                }
            }
        }
        return isOk1 || isOk2;
    }

    public static bool CheckInstanceInstalled()
    {
        bool isOk1 = false;
        bool isOk2 = false;
        RegistryView registryView = Environment.Is64BitOperatingSystem ? RegistryView.Registry64 : RegistryView.Registry32;
        if (Environment.Is64BitOperatingSystem)
        {
            using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
            {
                RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
                if (instanceKey != null)
                {
                    foreach (string instanceName in instanceKey.GetValueNames())
                    {
                        if (instanceName.ToUpperInvariant() == "DATABASE_NAME")
                        {
                            isOk2 = true;
                            break;
                        }
                    }
                }
            }
        }
        using (RegistryKey hklm = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, registryView))
        {
            RegistryKey instanceKey = hklm.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL", false);
            if (instanceKey != null)
            {
                foreach (var instanceName in instanceKey.GetValueNames())
                {
                    if (instanceName.ToUpperInvariant() == "DATABASE_NAME")
                    {
                        isOk1 = true;
                        break;
                    }
                }
            }
        }
        return isOk1 || isOk2;
    }

Upvotes: 1

Mário Meyrelles
Mário Meyrelles

Reputation: 1792

Another simple alternative would be to use the following command line inside your installer:

sc queryex type= service | find "MSSQL"

The command above simply lists the all the services containing the MSSQL part, listing named and default SQL Server instances. This command returns nothing if nothing is found. It returns something like this:

SERVICE_NAME: MSSQL$SQLEXPRESS

Hope this helps.

Upvotes: 7

GarethOwen
GarethOwen

Reputation: 6133

I needed something similar, to discover a local SQLServer instance to perform automated tests against.

The SmoApplication was perfect for this requirement - my code looks like this:

public static string GetNameOfFirstAvailableSQLServerInstance()
{
    // Only search local instances - pass true to EnumAvailableSqlServers
    DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
    DataRow firstRow = dataTable.Rows[0];
    string instanceName = (string)firstRow["Name"];
    return instanceName;
}

Upvotes: 2

M4N
M4N

Reputation: 96541

A simple way to list all SQL Servers on the network is this:

using System.Data;
using System.Data.Sql;
using System;

...

SqlDataSourceEnumerator sqldatasourceenumerator1 = SqlDataSourceEnumerator.Instance;
DataTable datatable1 = sqldatasourceenumerator1.GetDataSources();
foreach (DataRow row in datatable1.Rows)
{
    Console.WriteLine("****************************************");
    Console.WriteLine("Server Name:"+row["ServerName"]);
    Console.WriteLine("Instance Name:"+row["InstanceName"]);
    Console.WriteLine("Is Clustered:"+row["IsClustered"]);
    Console.WriteLine("Version:"+row["Version"]);
    Console.WriteLine("****************************************");
}

Taken from this blog post.

Upvotes: 17

M4N
M4N

Reputation: 96541

Have a look at this question: How can I determine installed SQL Server instances and their versions?

One of the answers lists the registry keys you could check to determine the installed SQL Server version(s).

Or check this codeproject article if you need to find any SQL Servers in the local network: http://www.codeproject.com/KB/database/locate_sql_servers.aspx

Upvotes: 2

Related Questions