We recently switched from Visual Studio 2019 to 2022 and upgraded our C# projects from .NET Framework 4.5.2 to .NET 6.0 using the upgrade assistant. The upgrades were successful, though we've encountered a bunch of build and runtime errors, which we expected we would need to take care of. However, this one has stumped me.
Before the upgrade, the following code worked just fine:
using Microsoft.SqlServer.Management.Smo;
...
DataTable servers = SmoApplication.EnumAvailableSqlServers(false);
But following the update to 6.0, it started throwing a FailedOperationException. When I drill down, the innermost exception said Could not load type 'System.Data.Sql.SqlDataSourceEnumerator' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
I tried using nuget to add the Microsoft.SqlServer.SqlManagementObjects package to the project. But it still throws a FailedOperationException, though this time the innermost exception says could not instantiate object Microsoft.SqlServer.Management.Smo.AvailableSQLServers. This message isn't really helpful, because I don't know what it's trying to instantiate (since EnumAvailableSqlServers is a static method) or why it can't instantiate it.
Before the upgrade, the project was referencing a local copy of Microsoft.SqlServer.Smo.dll. I removed it after adding the SqlManagementObjects package, but this had no effect.
I've searched for answers online but haven't yet found anything helpful. Any ideas of what to check?