Menu
I have been developing a C# / SQL Server application on a Windows 8.1 device for a while, using .NET 4.5.1 and SQL Server 2012 Express. It all worked flawless. Recently, Visual Studio 2015 has been released and I decided to upgrade. I also decided to upgrade my SQL Server 2012 as well, so I backed everything up, uninstalled it and installed SQL Server 2014 Express. After configuring everything and restoring the database backups everything still worked locally as the database connection data was still present in the stored application settings.
When I tested the application on another device I found out that it could no longer connect to the SQL Server instance and it couldn't detect it either using
System.Data.DataTable instances = SqlDataSourceEnumerator.Instance.GetDataSources();
The application couldn't detect the SQL Server instance on my development machine either, which I think is rather strange as it's running local. Several other devices could, however, detect the SQL Server so I don't think it's a firewall related program. The SQL engine service and the browser services were both active.
I decided to upgrade to Windows 10, reset it (clean install, did not install any SQL Server programs) and run my application again, as there are other computers in my network running SQL Server, hoping it would detect those. It didn't. The GetDataSources() method returns an empty DataTable within a split second, while other devices take a few seconds to detect the instances and return a correct list.
Even more stupid is that ODBC Data Source Administrator does detect those network instances, making me think it is a .NET related program.
Summary: two devices do not detect any SQL Server instances (even local), while the other devices in the network do.
207
Get-ChildItem cmdlet To list all the SQL instances, navigate to the root directory and run the Get-Childitem to get the list. Note: The path SQLSERVER:\SQL\<ServerName> has all the installed SQL instances of the listed server.
The following common scenarios can cause connectivity problems: Incorrect IP address for the Server field. Make sure that the IP address matches the entry in the SQL Server error log file. Incorrect server name in the Server field.
Make sure your server name is correct, e.g., no typo on the name. When you try to connect to an SQL Server instance on another server, make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true). Make sure SQL Browser service is running on the server.
I have decided to go with this ODBC implementation as it is working correctly and an async/await update can be implemented so it won't block the UI.
Before choosing this implementation, I also poked around with another project using the SqlDataSourceEnumerator class, which worked perfectly. Its Target framework property was set to .NET Framework 2.0. I discovered that the SqlDataSourceEnumerator gives the expected results (on my machine) when the .NET target version is 3.5 or lower. Something in the .NET implementation has probably changed over time, causing it to stop working in some specific conditions.
Edit: on my machine I have installed SQL Server 2012 again, and everything (except for the detection, will use ODBC) is working fine again. I want to thank everyone for their input.
125
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
