Post reply

Transactional Replication to Azure getting "too many arguments" error on MSins

  • August 10, 2022 at 2:44 pm

    #4074866

    I'm running a 2019 (15.0.4102.2) Server with Transactional Replication out to a SQL Azure (12.0.2000.8) instance. The Subscriber is read-only and no updates are allowed to return. Short meaning, this is not a Merge Replication setup. However, that does not mean that changes cannot be made on the Subscriber, just that a reinitialization will just overwrite any schema or data changes.

    I'd like to add that this is happening with multiple Subscriptions with this same article. Not sure if that matters or not, but it is annoying.

    In SQL Replication Monitor I am seeing a few Publications go into an error state. The message I am seeing is:

    "MSins_%schemaArticle has too many arguments specified. (Source: MSSQLServer, Error number: 8144)"

    I have been searching for answers to this one for a while now. Here is what I know thus far:

    • Reinitializing the Subscription will resolve the problem for a period of time. Could be hours or days, it is not consistent.
    • I have compared the schema between the Publisher and Subscriber and there are no differences in the table structure. Though I can say I was not really expecting there to be any since reinitializing would have negated any changes made to the Subscriber. However, I can say that I completed this validation prior to reinitializing and still no differences.
    • I used sp_browsereplcmds to "harvest" the command and reviewed the argument count. It matches the number of columns and the datatypes for the columns. Likewise, there are not any instances where I would say that the values passed could be construed as accidentally passing multiple values (e.g. no extra commas).

    I have been reviewing Cumulative Updates to see if there might be anything that addresses this, but I have not found anything yet. Again, not surprising since the error seems to indicate that the issue is with a Stored Procedure simply passing too many arguments. The problem is that no one on the team would have written said Stored Procedure as it is part of SQL Replication generated by Microsoft.

    I'm looking for any ideas on this one that might help. I have considered dropping and re-adding the article from the Publication, but I don't see that resolving the error really since the entire table is replicated. Any theory or idea is welcome as well as any questions.

    • This topic was modified 2 years, 5 months ago by  Irish.
    • This topic was modified 2 years, 5 months ago by  Irish.

    Regards, Irish 

  • August 11, 2022 at 3:10 pm

    #4075257

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 11, 2022 at 9:03 pm

    #4075368

    UPDATE: I was able to figure out the problem.

    I set up a lot of Publications. So much so that I often recycle scripting. Typically I edit the file and save it under a different name and update the scripting for what is needed. I'm sure that I'm not the only one who does this.

    It is important to remember to replace all of the instances of an object name with the new/correct object name and/or use parameters. I had two Publications referencing the same set of insert, update, and delete stored procedures on the Subscription Database. The tables are completely different and this is what is leading to the errors.

    All fixed now.

    Regards, Irish 

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply