Error when programming RMO for SQL 2019: MissingMethodException

Question

Error when programming RMO for SQL 2019: MissingMethodException

Stefan Adriaenssen 86

Hello,

I'm trying to execute the following lines of C# code:

var connection = new Microsoft.Data.SqlClient.SqlConnection(@"Server=.;Database=master;Integrated Security=true;");
var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(connection);
var replicationServer = new Microsoft.SqlServer.Replication.ReplicationServer(serverConnection);

bool installed = replicationServer.DistributorInstalled;

But this code crashes on the last line, where the DistributorInstalled property is being read. I'm getting a MissingMethodException:

Method not found: 'System.Data.SqlClient.SqlDataReader Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(System.String)'.

I added the following references to this project:

NuGet package 'Microsoft.SqlServer.SqlManagementObjects' version 161.44091.28 (this is the last version at the time of writing)
C:\Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SqlServer.Rmo.dll
C:\Program Files\Microsoft SQL Server\150\SDK\Assemblies\Microsoft.SqlServer.Replication.dll

I also tried switching the Microsoft.SqlServer.Rmo and Microsoft.SqlServer.Replication libraries with versions from the GAC but this doesn't help. I keep getting the same error message.

Can anyone provide some help please?

Thanks,

Stefan Adriaenssen

Accepted answer

4 additional answers

Your answer

Answer 1

The Replication libraries installed as part of the SQL Server install need to be paired with an older, compatible version of SMO. The version of SMO you are using has switched from the .NET Framework SqlClient (System.Data.SqlClient) to the newer standalone one (Microsoft.Data.SqlClient).

So downgrade the SMO NuGet package to 150.18208.0, and change the code to :

    var connection = new System.Data.SqlClient.SqlConnection(@"Server=.;Database=master;Integrated Security=true;");
    var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(connection);
    var replicationServer = new Microsoft.SqlServer.Replication.ReplicationServer(serverConnection);

    bool installed = replicationServer.DistributorInstalled;

Answer 2

Olaf Helper 47,436

If I pass the connection info as string to the ServerConnection constructor it works for me; no error message.

 var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(".");
 var replicationServer = new Microsoft.SqlServer.Replication.ReplicationServer(serverConnection);

 bool installed = replicationServer.DistributorInstalled;

Answer 3

Stefan Adriaenssen 86

Hello,
Thanks for looking into it. No, it doesn't work for me actually, I still get a MissingMethodException. Which version of SQL are you running? I'm at:
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Regards,
Stefan

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-12-04T22:46:20.853+00:00

As a matter of principle, I like to point that you are using SQL 2019 RTM without any Cumulative Update applied. I recommend you to download and install CU8 which is the most recent CU8. Beware that you should run sp_sqlrefreshmodule on all your scalar user-defined functions, as they have done fixes in that area.

However, I don't expect this to resolve your actual issue, which appears to be a client side problem, which I can unfortunately cannot assist with, as I am not acquainted with RMO.
Stefan Adriaenssen 86 Reputation points

2020-12-07T06:56:32.273+00:00

Hello,

I have installed CU8, but this didn't fix the problem. I'm now running version:

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Also, I'm running the sample code locally on the SQL server itself, so there is no separate client machine.

I've read about the sys.sp_refreshsqlmodule procedure, but it expects parameters, and I wouldn't know what to pass in as a value. I'm not explicitly executing any functions myself, just the code you see above. Maybe these statements execute functions internally, but I wouldn't know about that.

Regards,
Stefan
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-12-07T22:19:09.977+00:00
Sorry, for the confusion. I tried to say that I did not find it likely that CU8 would address your RMO problem. I was only pointing out that you were running a version you should not be running. This to save you from being victim to bugs that already have been fixed.

The thing about sp_sqlrefreshmodule has absolutely nothing to do with RMO, but relates to scalar user-defined functions, which tend to exist in most databases. Microsoft did some big changes around scalar user-defined functions in SQL 2019, so that they now can perform better, providing that they fulfil some conditions. The exact conditions have changed with the first few CUs, and for this reason you should do:

SELECT 'EXEC sp_sqlrefreshmodule ' + quotename(s.name) + '.' + quotename(o.name) FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.type = 'FN'

And the execute the result.

As for your RMO problem, I trust that David Browne is on track.

Answer 4

CathyJi-MSFT 22,396 Microsoft External Staff

Hi @Stefan Adriaenssen ,

I am not familiar with RMO and C#, please check if below blog could help you.

Programming SQL Server with SQL Server Management Objects Framework

Check if the suggestion from SQLServerSteve could help you.
https://stackoverflow.com/questions/59544293/method-not-found-while-restoring-sql-database-c-sharp

Best regards,
Cathy

Answer 5

Stefan Adriaenssen 86

Thanks a lot! That fixed the issue!

One question though; how would I know which version of SMO matches which version of SQL? Is there a page somewhere on MSDN that describes compatibility, or am I to conclude that SMO version '150' matches SQL version '15.0' by naming convention?

Regards,
Stefan

David Browne - msft 3,851 Reputation points

2020-12-08T16:46:48.093+00:00

Microsoft.SqlServer.Replication.DLL has an assembly reference to version 15 of SMO.

// Microsoft.SqlServer.Smo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 (unresolved)

You can see it in IlSpy or similar.

You then compare that with the Assembly Version of Microsoft.SqlServer.Smo.dll.

The relationship to the NuGet Package version is purely by convention. But in this case both are related to the SQL Server version, as SQL Server 2019 is version 15 or 150.

Share via

Error when programming RMO for SQL 2019: MissingMethodException

4 additional answers

Your answer