BUG: REPL: "Too Many Arguments Were Supplied" Error When Inserting a Row

• Microsoft SQL Server version 7.0

SYMPTOMS

After inserting a row into a replicated table on the publisher, the distribution task may receive the following error message:

8144 Too many arguments were supplied for procedure sp_MSins_<tablename>

CAUSE

Every column in every table has a column ID (colid) number. This error is caused by non-contiguous column IDs in the publishing table. If the colid numbers are not in a consecutive sequence, then you have non-contiguous colids. This could have been caused if you had originally created the table with more columns and some of the columns were dropped.

For example, if you create a table with columns c1-c5, they will have colid values of 1-5. If you then use an ALTER TABLE statement to drop columns c3 and c4, the remaining colid sequence will be 1, 2, 5. If you have non-contiguous colid values for the columns of a table, only the columns with contiguous colid values will be replicated. In this example, only columns c1 and c2 would appear on the subscriber. If you then attempted to insert a row on the publisher with values in columns c1, c2, and c5, you would receive the error message described in the SYMPTOMS section of this article.

WORKAROUND

To work around this problem, you should drop the existing publishing table with non-contiguous colids, then re-create it so that the colids are contiguous. To do this, perform the following steps (note that this example uses table names "Table1" and "Table2"; you should replace these with your own table names):

1. Make sure you script any indexes, constraints, defaults, rules, triggers, and other objects so that they can be re-created after the table is dropped. Also, make a note of any dependencies on this table; you can run the sp_depends stored procedure to get a list of these.



2. Delete the subscription and publication on the publisher. Drop the table from the subscription database on the subscriber.



3. Make sure the Select Into/Bulk Copy option is enabled in the publishing database. To check this, do the following in Enterprise Manager:




1. Right-click the database name and click Properties on the shortcut menu.



2. On the Options tab, make sure there is a check in the Select Into/Bulk Copy check box.



4. On the publisher, execute the following statement:

   
   SELECT * INTO Table2 
   FROM Table1 

   This copies all the data into a new table. Also, all the columns will have a sequential colid. However, any indexes, triggers, constraints, and so on are not copied. This is why it is important to have these objects scripted, so that they can be re-created.



5. Drop the original table (that is, Table1) on the publisher.



6. Use the sp_rename stored procedure to rename the new table as the original table (for this example, rename Table2 to Table1).



7. Re-create any dependent objects that you had scripted in Step 1 of this procedure.



8. Re-create the publication and subscription.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

MORE INFORMATION

You can determine whether or not you have non-contiguous columns in your table by looking at the column ID (colid) number sequence for the table. Execute the following statement, replacing "TABLE1" with the name of your table:

SELECT name, colid 
FROM syscolumns 
WHERE id = object_id('TABLE1') 

Additional query words: repl col cols

Keywords : SSrvRep kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug