Distribution agent failing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Distribution agent failing

Distribution agent failing

Rate Topic

Display Mode

Topic Options

Author

Message

sqldba-522622

Posted Thursday, October 23, 2008 2:00 PM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 21, Visits: 278

Error executing a batch of commands. Retrying individual commands,
Procedure or or function sp_MSins_tablename has too many arguments specified.

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000D2A7D0002A707001300000000, Command ID: 3)

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

Tried a numebr of things:
1. I have tried to drop the article to clear it up but to no avail.
2. Re-added the article to the replication but it still stucks at the same
error and did not even attempt to replace the subscriber table and procs.
3. tried to rename the tables and replication procs on subscriber but to no
avail,
The error changes to the following.
Procedure or or function sp_MSins_tablename not found.

3. Used the following procedures to identify and fix error transactions.
sp_helpsubscriptionerrors
sp_setsubscriptionxactseqno
But the error still not going away.

Any idea what's going on. Any help will be greatly appreciated.
All the transactions are holding up on the distribution database at the
moment.

Thanks a bunch
sqldba

Post #590767

Kendal Van Dyke

Posted Friday, October 24, 2008 7:06 AM

Old Hand

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:23 AM
Points: 361, Visits: 781

When you dropped and added the article back to the publication did you reapply the sp_MS stored procedures for that article? Maybe you could script out the procedures using sp_scriptpublicationcustomprocs and compare against what's on your subscriber. I have a blog post that describes how to script replication procs, and BOL has plenty of details as well.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/

Post #591167

sqldba-522622

Posted Friday, October 24, 2008 9:24 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 21, Visits: 278

Thanks for your resposne, I have already resolved it earlier by using sp_scriptpublicationcustomprocs.
The problem is still very unique and weird, two tables pointing to same replication (insert, update and delete procs). Had to drop both articles and added them back. Sounds simple but who can think of this thing could have happened. it can be a bug or a very very rare occurence.

Thanks!
sqldba

Post #591304

ConvolutedDBA

Posted Tuesday, October 28, 2008 1:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 02, 2009 5:22 PM
Points: 9, Visits: 29

I've had the same problem with a few of our publications, I currently have a case open with Microsoft...It's more and more pointing to a bug in either the schema configuration or the dist clean up, I'll post more details once i get further updates from the MSE

Post #593203

« Prev Topic | Next Topic »

Permissions