CODEWITHSUNDEEP
.netsql-serverinstance
Chris Hayes
Chris Hayes

Reputation: 4037

How do you list instance names for SQL Server when you have the server name?

there can be multiple instances of sql server on a machine. I'm trying to identify every instance of sql server engine running on any given REMOTE machine. all the other answers in other questions ONLY address local machine.

I have seen SqlDataSourceEnumerator and it searches a network, I know the computer I want to search.

I have seen SmoApplication.EnumAvailableSqlServers but it requires the 'instance name' which is what I'm looking for

How do you interrogate a machine for SQL Server instance info with .net?

Upvotes: 0

Views: 156

Answers (2)

Hadi
Hadi

Reputation: 37313

Try the following

Dim dt as dataTable= SqlDataSourceEnumerator.Instance.GetDataSources()

For each dr as datarow in dt.Select("[ServerName] = '" & strServername & "'")

Msgbox(string.Concat(dr("ServerName"), "\\", dr("InstanceName"))))

NEXT

Where strServername is the machine name

Or try using Linq

Dim lst as List(of string) = SqlDataSourceEnumerator.Instance.GetDataSources().where(Function(x) x.servername = strServername).Select(Function(y) y.Servername & "\" & y.Instancename).ToList()

Upvotes: 0

Chris Hayes
Chris Hayes

Reputation: 4037

This blog shows how to list instances of sql server installed on a machine using System.ServiceProcess;

https://blogs.msdn.microsoft.com/sqlserverfaq/2009/03/07/how-to-detect-sql-server-instances-features-installed-on-a-machine/

the missing key for me was that the ServiceController.GetService() static method has an overload that takes a string 'servername'

class InstanceInfoRetriever
    {
        public IEnumerable<string> GetInstanceNames(string serverName)
        {
            var servicenames = new[] { "MSSQL", "SQL Server" };
            var services = ServiceController.GetServices(serverName);
            var sqlservices = services.Where(s => servicenames.Any(n => s.ServiceName.Contains(n)));
            return sqlservices.Select(s => s.ServiceName);

        }
    }

Upvotes: 1

Related Questions