[RESOLVED] SqlDataSourceEnumerator.Instance

[Shaggy Hiker]

I'm trying to locate SQL Server (Express or not) instances on either a local system or a LAN. I have some code that does this written in VS2010. For anyone interested, the code looks like this:

        Dim dSourceInstanct = SqlDataSourceEnumerator.Instance
        mSourcesDT = dSourceInstanct.GetDataSources

        For Each dr As DataRow In mSourcesDT.Rows
          'Stuff done here
        Next

This is working fine on a couple computers. However, I just got a new system and installed VS2010 and VS2015. The same program in VS2010 returns an empty datatable, which means that it found no servers. I found this out when I tried to copy that code into a new program in VS2015 and found no servers. There are at least two local servers.

One of the possible issues has to do with Windows Firewall blocking certain ports. I've explicitly opened those ports, and tried with Windows Firewall OFF (though not a perfect test of the latter), without success. I was then searching the web looking for something else to try and found this page:

https://connect.microsoft.com/Visual...-framework-4-x

The title talks about this being an issue with Windows 10, which isn't my problem as I'm running Windows 7 on the new computer (we aren't allowed to have Win10). However, look at the comments. People are saying that installing VS2015 causes the enumeration method to fail. That's pretty bad, but I'm at a loss as to how to confirm this. If VS2015 is installing something that screws up the enumeration, then installing VS2015 would cause the working programs on my other computers that don't have VS2015 to fail. That would be a definitive test, it's just that I don't really want that to happen.

Does anybody have any information on this?

[Zach_VB6]

Relevant VS bug report: https://connect.microsoft.com/Visual...etails/1719418

Have you tried changing the Target Framework to .NET 3.5?

[Shaggy Hiker]

Good find there.

I haven't targeted 3.5, but both the old program and the new target 4.0 rather than 4.6.

I've gotten my hands on my old computer, which I can freely trash. I'm going to test that the code is working, then install VS2015 and try again.

[techgnome]

Doesn't surprise me... I've had that very issue with it since it was first introduced... all the way back to FW2.0 ... I think that's when I first used it... anyways, I found that on one run, it would pick up a bunch of servers/instances... and then on the very next run, nothing, or some of them... sometimes it would get all of the local, sometimes only one or two, sometimes none... never did figure it out.

-tg

[Zach_VB6]

Quoted from MSDN:

Due to the nature of the mechanism used by SqlDataSourceEnumerator to locate data sources on a network, the method will not always return a complete list of the available servers, and the list might not be the same on every call. If you plan to use this function to let users select a server from a list, make sure that you always also supply an option to type in a name that is not in the list, in case the server enumeration does not return all the available servers. In addition, this method may take a significant amount of time to execute, so be careful about calling it when performance is critical.

Shaggy - do me a favor and try running this PowerShell script. (Copy below code, Start->Run->type 'powershell'->right click in the blue area, press enter).

#stolen from http://goo.gl/VqTYP9
function Get-ServiceStatus ([string[]]$server){ 
 foreach ($s in $server)
 {
   if(Test-Connection $s -Count 2 -Quiet)
   {
    Get-WmiObject win32_Service -Computer $s |
     where {$_.DisplayName -match "SQL Server"} | 
     select SystemName, DisplayName, Name, State, Status, StartMode, StartName
   }
 }
}


Get-ServiceStatus localhost

Change localhost to the appropriate server name.


Let me know if this works, and if it does, we can convert this to VB

[TnTinMN]

Good find there.

I haven't targeted 3.5, but both the old program and the new target 4.0 rather than 4.6.

Are you aware that .Net 4.5 to .Net 4.X are in-place replacements for .Net 4.0. Thus is you install one of these new versions, targeting .Net 4.0 does not mean you are running .Net 4.0, but rather the new improved version.

See "Determine Which .NET Framework Versions Are Installed"

[Shaggy Hiker]

I'll have better information presently.

I was aware that detecting instances was fiddly and wouldn't always returns all the servers, but it has always returned the local instances, which was good enough for me. I got my hands on my old computer and tested that the method was returning the local instances. I'm now installing 2015, which will install FW 4.6. If the same code that did work now returns nothing, as I expect, then that pretty much confirms the posts that suggest that FW4.6 breaks the functionality. I'm not concerned about the fiddliness of the functionality, as the program already has a mechanism for dealing with that (and doesn't even use the method once it has identified the right DB). However, if the presence of FW4.6 on the system means that even local versions can't be seen...well, that would be a problem for a second program I'm working on, because I can't be certain what will be on the computers that will run the program.

@TnTinMN: No, I wasn't aware of that. I was aware that I had a mass of 4.x FW stuff installed, and assumed that they weren't all different versions, but I've been on VS2010 until a month or so back, so it wasn't even an issue.

[Shaggy Hiker]

Well, it took some time, but VS2015 installed successfully on my old computer, 4.6 installed correctly as part of that....and the code still works on that system. The computer is still able to see the local databases without any issue. I really didn't expect that.

Right now, there are few differences between the system that the code works on and the system that it doesn't work on. I have SQL Server Express 2014 on the new system (along with SQL Server Express 2012), whereas the old system has 2008 R2 and 2012. If I wasn't able to see the 2014, that might mean something, but not being able to see either....well, it's not making much sense. I also checked the settings for SQL Server on the old system and didn't see anything different.

@Zach: I did try the powershell script and it returned all the instances correctly on the new computer. It does appear to be a different way to go, but it still leaves the puzzle as to why the code works on three computers but not on the fourth.

[Shaggy Hiker]

Actually, there is one fairly significant difference. Further testing commencing....

[TnTinMN]

Enumeration Limitations

All of the available servers may or may not be listed. The list can vary depending on factors such as timeouts and network traffic. This can cause the list to be different on two consecutive calls. Only servers on the same network will be listed. Broadcast packets typically won't traverse routers, which is why you may not see a server listed, but it will be stable across calls.

Listed servers may or may not have additional information such as IsClustered and version. This is dependent on how the list was obtained. Servers listed through the SQL Server browser service will have more details than those found through the Windows infrastructure, which will list only the name.

Have you tried restarting the "SQL Server Browser" service on the impacted machine?
Its worth a try.

[Shaggy Hiker]

Yeah, I did try that one...with high hopes, I might add. In fact, I tried that one before I really got looking into it.

I just realized that the VS2015 on the impacted machine is out of date and lacks Update 1. The new version I just installed on the old computer has Update 1. Since the issue reported in those links is quite recent, this could be the difference. It may well be an issue that MS already fixed and the whole problem is that the new computer is just out of date. Unfortunately, that's going to be a lengthy thing to fix because I have a mighty slow connection at home. Still, it's underway and I have other stuff to work on.

[Zach_VB6]

@Zach: I did try the powershell script and it returned all the instances correctly on the new computer. It does appear to be a different way to go, but it still leaves the puzzle as to why the code works on three computers but not on the fourth.

If you're looking for a solution, I would suggest running the PS script inside VB. You can also access WMI using ManagementObjectSearcher, but I find it's simpler this way.

Imports System.Management.Automation
    Public Shared Function GetSqlServers(server As String) As ObjectModel.Collection(Of PSObject)
        Dim commandStr As String =
            "if(Test-Connection " & server & " -Count 2 -Quiet) {
                Get-WmiObject win32_Service -Computer " & server & " |`
                where {$_.DisplayName -match ""SQL Server""} | `
                select SystemName, DisplayName, Name, State, Status, StartMode, StartName
            }"
        Dim command As PSCommand = New PSCommand()
        command.AddScript(commandStr)
        Using ps As PowerShell = PowerShell.Create()
            ps.Commands = command
            Dim results = ps.Invoke()
            Return results
        End Using
    End Function

[TnTinMN]

...

I just realized that the VS2015 on the impacted machine is out of date and lacks Update 1. The new version I just installed on the old computer has Update 1. Since the issue reported in those links is quite recent, this could be the difference. It may well be an issue that MS already fixed and the whole problem is that the new computer is just out of date. ...

Update 1 would include .Net 4.61, so that may be the ticket. I doubt the VS update itself would have any impact.

[Shaggy Hiker]

Well, the first attempt was fun. The update got part way through then gave me a fatal installation error and asked me if I wanted to quit. Since there weren't any other options offered, I felt that quitting was probably preferable to sitting there looking at the screen until I rotted away, so I accepted. That left VS corrupted. The failed install didn't roll back, it just quit and I couldn't even start VS2015 anymore. That was a hoot.

I believe I have it installing properly now.

[Shaggy Hiker]

Well, THAT was a tangled path to follow. The solution was simply to install FW 4.6.1. Under the mistaken belief that I could simply update VS2015 to Update 1, I tried that. As noted, it failed and corrupted the install entirely. That was fine, because I happened to have a disc that I had just used to install VS2015, along with Update 1, onto a different computer for testing. That disc just asked me if I wanted to Repair or add features. Repair seemed sufficient, since it was broken, so that's what I did.

To my surprise, that did nothing at all, aside from repairing the installation. By then, I was home on my slow connection, so I waited until today to simply download FW 4.6.1, which fixed the problem.

So, the solution to this issue....which may never have come up before on this forum, is that 4.6 broke the method and 4.6.1 fixed the method.

[Shaggy Hiker]

One further note is that Microsoft has done something that has broken the links from the first few posts in this thread. They all just go to Bing search, now.