Distribution agent failing with “procedure was called with an incorrect number of parameters”.

Sateesh Yele
Microsoft SQL Server Support

Transactional replication allows you to specify how data changes are propagated from the Publisher to Subscribers. For more information, please check this link. Specifying How Changes Are Propagated for Transactional Articles

Recently, I worked on a case where the distribution agent failed with procedure was called with an incorrect number of parameters when executing an update procedure at the subscriber. We collected the distribution agent verbose log and see that the procedure is getting called with 4 parameters but the procedure is defined with 5 parameters. After carefully analyzing the procedure call, we see that the distribution agent is not passing in the bitmask parameter. The logreader agent reads the sysarticles table to get the replication stored procedure format for each article and then generates the commands in that format. In our case, the update procedure format in the sysarticles was CALL format but the procedure is defined as SCALL format at the subscriber. So we knew that the stored procedure at the subscriber is incorrect.

How to manually generate replication stored procedures.

To manually generate all INSERT, UPDATE, and DELETE procedures for all table articles in a publication in which the auto-generate custom procedure schema option is enabled execute sp_scriptpublicationcustomprocs on the publisher.  The resulting script can be executed on the subscriber and is particularly useful for setting up subscriptions for which the snapshot is applied manually.

To manually create individual replication stored procedures, use the procedures below on the Publisher database to generate the missing or incorrect replication procedures and compile them on the subscriber. To check, what formats each article is using, query the sysarticles table on the publisher database. As you can see in the below screenshot, update is using SCALL format while inserts and deletes are using CALL format.

CALL for Insert:

exec sp_scriptinsproc @artid int — sysarticles.artid

CALL for Delete:

exec sp_scriptdelproc @artid int — — sysarticles.artid

CALL for Update:

exec sp_scriptupdproc @artid int — — sysarticles.artid

SCALL for update:

exec sp_scriptsupdproc @artid int — sysarticles.artid,@mode tinyint = 1 — 1 = static scripting, 2 = dynamic scripting

MCALL for update:

exec sp_scriptdynamicupdproc @artid int — sysarticles.artid

XCALL for Del:

exec sp_scriptxdelproc @artid int — sysarticles.artid

XCALL for update:

exec sp_scriptxupdproc @artid int — sysarticles.artid,@mode tinyint = 1 — 1 = static scripting, 2 = dynamic scripting

–posted by: Chris Skorlinski