Why isn't my method returning any valid SQL Server instances?

Question

I have a piece of code that is supposed to return all instances of SQL Server installed on my machine. It looks like this:

DataTable dtLocalServers = SmoApplication.EnumAvailableSqlServers(true);

foreach (DataRow dServer in dtLocalServers.Rows)
{
   Console.WriteLine(dServer.ToString());
}

It works, compiles, and runs. The resulting datatable is not very useful, however. It looks like this:

Name    |  Server   |   Instance   |   IsClustered | Version   |   IsLocal
IAN-PC  |  IAN-PC   |              |   True        |           |   True

The problem is that although this returns a result, this doesn't give me any information about the server. Particularly, I'm trying to get the version (SQL Server 2008 Express R2 or SQL Server Express 2012), which I'm assuming should be in the 'Version' field. I know for a fact that I have two copies running locally for the sake of this test.

Is there anything else I can do to get better results?

EDIT: IAN-PC is the name of my computer. There are two running instances of SQL Server on my computer, IANSQLEXP (SQL Server 2008 Express R2), and SQLEXPRESS (SQL Server Express 2012). In the SQL Server Configuration Manager, there are also two Agents that are not running and a SQL Server Browser that is not running.

Your table has `Version` as a column. If the server is clustered it will not return a version. — Dustin Kingen, Apr 16 '13 at 18:34
I understand that part. Is there not another way to check on servers? We're looking to put a service on clients' computers, so we won't have control over whether or not they are clustered and would still like to check what is installed. That might be a dumb question, I'm not familiar with how SQL Server works. — ijb109, Apr 16 '13 at 18:39
Connect using Management Studio and run `SELECT @@SERVERNAME;` — Aaron Bertrand, Apr 17 '13 at 13:39
It seems to be a named instance, `IAN-PC\SQLEXPRESS`. It isn't showing up, though. — ijb109, Apr 17 '13 at 13:54

Dustin Kingen · Answer 1 · 2013-04-16T19:06:56.883

0

You can enumerate the servers and turn them into a List<Server>.

var servers =
    SmoApplication
        .EnumAvailableSqlServers()
        .AsEnumerable()
        .Select(s => new Server(s[0].ToString())) //Server name is first column
        .ToList();

You can use the Version property on Server to get the server version.

var unavailableServers= new List<Server>();
var activeServers = new List<Server>();
var tasks = new List<Task>();

foreach(var server in servers)
{
    var task =
        Task.Run(() =>
            {
                try
                {

                    server.ConnectionContext.Connect();
                    server.ConnectionContext.Disconnect();
                    activeServers.Add(server);
                }
                catch(ConnectionFailureException)
                {
                    unavailableServers.Add(server);
                }
            });

    tasks.Add(task);
}

Task.WaitAll(tasks.ToArray());

edited Apr 16 '13 at 19:06

answered Apr 16 '13 at 18:43

Dustin Kingen

20,677
7
52
92

When I do that, I get the same result as before (in server form), and when I try to get the version, I dig down in and get a message that says `base {System.SystemException} = {"Failed to connect to server IAN-PC."}`. I feel the clustering is the problem here. – ijb109 Apr 16 '13 at 18:52
You'll need to check whether you can access the server. Check my edit. – Dustin Kingen Apr 16 '13 at 19:02
I'm getting an empty active servers list. I'm not really sure what's going on here, but it seems like it's getting the computer name as the server, maybe that's the cluster. At any rate, I feel like this approach isn't working :( – ijb109 Apr 16 '13 at 19:15
You should try directly connecting to the server `var server = new Server("IAN-PC"); var version = server.VersionString;` and see if it throws or if you need authentication. – Dustin Kingen Apr 16 '13 at 19:30
It throws a `ConnectionFailureException`, so I guess that means it needs authentication. Also, I updated the description of the question to give some clarity on how this is all working. – ijb109 Apr 16 '13 at 19:57

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

The problem here is that the SQL Server Browser was deactivated on my computer. The post that I found the answer in was here. Another helpful link on from MSDN that helped is here. Thanks for the help, guys!

edited May 23 '17 at 12:04

Community

1
1

answered Apr 17 '13 at 14:51

ijb109

942
1
19
30

Why isn't my method returning any valid SQL Server instances?

2 Answers2