Replication --Procedure or function sp_MSadd_snapshot_agent has too many arguments specified.

chrisrdba 431 Reputation points
2023-11-17T21:22:18.5033333+00:00

Our current environment is OLTP and replication both on windows 2016, SQL on 2019 (15.0.4198).

We want to upgrade to upgrade to windows 2022 for OLTP only (long winded reasons), and have already built the OLTP server. When I installed SQL I naturally installed the latest CU that's mentioned above, and am experiencing this issue. We're scheduled two weekends from now.

Now when trying to create the Publication I get the error:

"Procedure or function sp_MSadd_snapshot_agent has too many arguments specified."

Someone in this post mentioned it may be either an outdated distribution server or the specified CU on OLTP.

The final crack in the chin is we dont replicate non production, so I have no place to test anything.

All said I think my two options are

1.     Uninstall the latest CU from my new OLTP, and drop it down to the same version that's on current OLTP. The problem here is that means that testing hasnt been done on the real version. I think this is a small risk, but still there.

2.     Upgrade the CU on replication to this higher level. The problem there is finding available time, as well as not being able to test first since we dont replicate in non Prod.

Im going w option 1 trying to uninstall the KB, but halfway through it's complaining that I need to produce sql_engine_core_inst.msi in the form of a CD, which I don't have. What it is is described here, but involves going further down the rabbit hole Im trying to avoid, at least before I know if Im on the right track or not.

I have a lot going on here. I've got a not very documented replication issue, and and unable to complete the one workaround I can find for it. Can anyone assist with either issue?

Thanks!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,007 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Kevin Ricords 5 Reputation points
    2023-12-12T21:11:04.1833333+00:00

    Configuring publication on a database >=2022cu7(16.0.4065.3), with a distribution database <2022cu7(16.0.4065.3) will result in the above issue.

    cu7/2506726 adds 3 parameters ( @distributor_security_mode, @distributor_login, @distributor_password ) to sp_addpublication_snapshot, sp_MSadd_snapshot_agent and several related SPs.

    When configuring on the publication database the 3 new params are included in the internal call to the distribution database (which does not have those 3 new parameters).


  2. chrisrdba 431 Reputation points
    2024-01-03T21:47:59.3833333+00:00

    Is there a way for me to tell what acceptable CU will work without trying all of them? Like when was this bug introduced?

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.