SMO ManagedComputer.ServiceInstances is empty

Posted by Mark J Miller on Stack Overflow See other posts from Stack Overflow or by Mark J Miller
Published on 2010-05-04T22:05:51Z Indexed on 2010/05/04 22:08 UTC
Read the original article Hit count: 435

Filed under:
|

I am trying to use SMO (VS 2010, SQL Server 2008) to connect to SQL Server and view the server protocol configuration. I can connect and list the Services and ClientProtocols as well as the account MSSQLSERVER service is running under. However, the ServerInstances collection is empty. The only instance on the target server is the default (MSSQLSERVER), shouldn't that be in the collection? How can I get an instance of it so I can inspect the ServerProtocols collection? Here's the code I'm using:

    class Program
{
    static void Main(string[] args)
    {
        //machine hosting installed sql server instance
        ManagedComputer host = new ManagedComputer("dev-it-db01.dev.interbankfx.lcl");
        //ManagedComputer host = new ManagedComputer("MRW-IT-DTP69");

        if (host.ServerInstances.Count != 0)
        {
            //why is this 0? Is it because only the DEFAULT instance exists?
            Console.WriteLine("/////////////// INSTANCES ////////////////");
            foreach (ServerInstance inst in host.ServerInstances)
            {
                Console.WriteLine(inst.Name);
            }
        }

        Console.WriteLine("/////////////// SERVICES ////////////////");
        // enumerate sql services (looking for MSSSQLSERVER)
        foreach (Service svc in host.Services)
        {
            Console.WriteLine(svc.Name);
        }

        Console.WriteLine("/////////////// DETAILS ////////////////");

        // get name of MSSQLSERVER instance from user (pick from list above)
        Service mssqlserver = host.Services["MSSQLSERVER"];

        // print service account: .\{account} == "local account", "LocalSystem", "NetworkService", {domain}\{account} == "domain account"
        Console.WriteLine("Service Account: {0}", mssqlserver.ServiceAccount);

        // get client protocols
        foreach (ClientProtocol cp in host.ClientProtocols)
        {
            Console.WriteLine("{0} {1} ({2})", cp.Order, cp.DisplayName, cp.IsEnabled ? "Enabled" : "Disabled");
        }

    }
}

I've also tried:

            Urn u = new Urn("ManagedComputer[@Name=dev-it-db01.dev.interbankfx.lcl]/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']");
        ServerProtocol tcp = host.GetSmoObject(u) as ServerProtocol;
        if (tcp != null)
        {
            Console.WriteLine("{0}", tcp.DisplayName);
        }

But I get an error message stating: "child expressions are not supported." Any ideas what's wrong?

© Stack Overflow or respective owner

Related posts about smo

Related posts about wmi