WMI method for returning sql server instances returns empty string

Question

I'm trying to create a WMI method to return all server instances but the GetCorrectWmiNameSpace(); returns an empty string. I use sql server 2012, any idea why is it returning an empty string?

public bool  EnumerateSQLInstances()
{

    string _instanceName = string.Empty;
    string _serviceName = string.Empty;
    string _version = string.Empty;
    string _edition = string.Empty;
    string _correctNamespace = GetCorrectWmiNameSpace();
    if (string.Equals(_correctNamespace, string.Empty))
    {
        return false;
    }

    string query = string.Format("select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and PropertyName = 'instanceID'");
    ManagementObjectSearcher getSqlEngine = new ManagementObjectSearcher(_correctNamespace, query);
    if (getSqlEngine.Get().Count == 0)
    {
        return false;
    }
    foreach (ManagementObject sqlEngine in getSqlEngine.Get())
    {
        _serviceName = sqlEngine["ServiceName"].ToString();
        _instanceName = GetInstanceNameFromServiceName(_serviceName);
        _version = GetWmiPropertyValueForEngineService(_serviceName, _correctNamespace, "Version");
        _edition = GetWmiPropertyValueForEngineService(_serviceName, _correctNamespace, "SKUNAME");
    }

    txtResponse.Text += _serviceName.ToString() + ", " + _instanceName.ToString() + ", " + _version.ToString() + ", " + _edition.ToString();

    return true;
}

public static string GetCorrectWmiNameSpace()
{
    String wmiNamespaceToUse = "root\\Microsoft\\sqlserver";
    List<string> namespaces = new List<string>();
    try
    {
        // Enumerate all WMI instances of
        // __namespace WMI class.
        ManagementClass nsClass =
            new ManagementClass(
            new ManagementScope(wmiNamespaceToUse),
            new ManagementPath("__namespace"),
            null);
        foreach (ManagementObject ns in
            nsClass.GetInstances())
        {
            namespaces.Add(ns["Name"].ToString());
        }
    }
    catch (ManagementException e)
    {
        Console.WriteLine("Exception = " + e.Message);
    }
    if (namespaces.Count > 0)
    {
        if (namespaces.Contains("ComputerManagement10"))
        {
            //use katmai+ namespace
            wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement10";
        }
        else if (namespaces.Contains("ComputerManagement"))
        {
            //use yukon namespace
            wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement";
        }
        else
        {
            wmiNamespaceToUse = string.Empty;
        }
    }
    else
    {
        wmiNamespaceToUse = string.Empty;
    }
    return wmiNamespaceToUse;
}

Answer 1

You will have to modify the method GetCorrectWmiNameSpace() to support later versions of SQL Server, for 2012 and 2014 it will be:

       if (namespaces.Count > 0)
        {
            if (namespaces.Contains("ComputerManagement10"))
            {
                //use katmai+ namespace
                wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement10";
            }
            else if (namespaces.Contains("ComputerManagement"))
            {
                //use yukon namespace
                wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement";
            }
            else if (namespaces.Contains("ComputerManagement11"))
            {
                //use 2012 + namespace
                wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement11";
            }
            else if (namespaces.Contains("ComputerManagement12"))
            {
                //use 2014 + namespace
                wmiNamespaceToUse = wmiNamespaceToUse + "\\ComputerManagement12";
            }
            else
            {
                wmiNamespaceToUse = string.Empty;
            }
        }

Or you can write more flexible code to be prepared for 13, 14 ... etc. versions of SQL Server.

The problem is that you have used code that is meant for SQL Server 2008 and older versions. This method GetCorrectWmiNameSpace() then returns string.Empty even when SQL Server 2012 or 2014 is installed.