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

Are you aware of `wbemtest` utility? Open `Windows Run > Type wbemtest` — Rahul Nikate, May 20 '15 at 08:09
I really can't see how your method could return `null`. Are you sure is not returning an empty string? — MatteoSp, May 20 '15 at 08:17
This code is meant for 2008 version, probably it is different from 2012. — Vojtěch Dohnal, May 20 '15 at 08:20
I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, May 20 '15 at 08:36

Vojtěch Dohnal · Accepted Answer · 2015-05-20T08:36:39.053

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.

WMI method for returning sql server instances returns empty string

1 Answers1