Transactional replication, extra argument

Question

Procedure or function sp_MSupd_dboTableA has too many arguments specified. (Source: MSSQLServer, Error number: 8144)

Using EXEC distribution.dbo.sp_browsereplcmds, I was able to pull the commands.
--cmd 2
{CALL [sp_MSupd_dboTableA] (,,,,,,,,2021-08-30 22:13:03.847,,,,,,,,,,,,,,,,,,,,21099205,21125187,0x00010010)}
--cmd 3
{CALL [sp_MSupd_dboTableA] (,,,,,,,,,2021-08-30 22:13:03.997,,,,,,,,,,,,,,,,,,,,21125187,0x00020000)}
--cmd 4
{CALL [sp_MSupd_dboTableA] (,,,,,,,,2021-08-30 22:13:03.997,,,,,,,,,,,,,,,,,,,,,21125187,0x00010000)}

How do I resolve this issue?
What is the reason behind the extra arg, where could it be coming from?

Answer 1

Hi @Naga Perni ,

Did you have Subscriber that is getting data from two (or more) different sources (Publishers)? If TableA on Subscriber getting data from Publisher1 and Publisher2. By default replication procedure names will have the same name “sp_Msupd_dboTableA” , that means both publishers will be using exact same procedure for insert, updates and deletes. You are receiving request from DEV to modify that table. To be exact drop a column. If you deploy change to Publisher1 only the replication procedure will be changed and new version (without dropped column) of the procedure will be applied on subscriber. The new procedure won’t have that old column… but what about Publisher2? Publisher2 will be trying to execute procedure still with old column and you will receive “Procedure or function sp_MSins_dboTableA has too many arguments specified.”.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 2

This happens when you change the table outside of replication. It sounds like you have added a new field to the table with replication disabled.

You likely need to reinit your subscription.

Answer 3

I have verified, there are no changes to the schema on publisher and subscriber.

Answer 4

Hi @Naga Perni ,

The error indicates a mis-match in the number of parameters (columns) in the command stored in the Distribution DB compared to the number of columns in the Replication created stored procedure. We needed to see which one is correct.

First step was to compare the SCHEMA of the Published database to the Subscriber. Since the Subscriber was a backup of the Publisher, I suspected them to be the same, but you never know unless you check. We can execute sp_help on both the pub & sub to compare the schema. If it shows that they're both the same, please try to use the sp_scriptpublicationcustomprocs procedure on the publisher to generate the replication procs and recreate them on the subscriber. Once new create stored procedure scripts were executed on the subscriber, the Distribution Agent executed the correct commands with matching parameter list, no reinitialization required. Please refer to this blog to get detail steps about this.

By the way, what is the version of SQL server instance on publisher and subscriber?

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.

Answer 5

@CathyJi-MSFT

The schema is the same on the publisher and the subscriber.
The new stored procs fixed the issue but I am still curious to know what could have caused the old proc to contain the extra arg.

Thank you for the solution.