More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
My distribution agent is failing with errors like:
Too many arguments were supplied for procedure sp_MSins_<Table_Name>
And
Too many arguments were supplied for procedure sp_MSupd_<Table_Name>
How to correct this?
You might see these errors if you have dropped some columns (Using ALTER TABLE… DROP COLUMN…) from the published table, before creating the publication. When you use ALTER TABLE …DROP COLUMN… the column will be dropped resulting in breaking the sequence of colid in syscolumns table. If the colid values are not contiguous, replication will not script the tables and stored procedures properly. As a result the distribution task will fail while replicating INSERT and UPDATE statements. DELETE statements will be replicated fine.
To verify if the colid values are contiguous or not, execute the following query on the published database.
Select colid from syscolumns where id = object_id(‘published_table_name’) order by colid
To avoid this situation, if you have to drop a column, use the UI provided by Enterprise Manager (Right click on the table and select ‘Design Table’) to drop the columns.
If your distribution task is failing with the above mentioned error, the best workaround is to unsubscribe, unpublish, recreate the published table, republish and resubscribe.
Use the following steps to recreate the table.
1. Do a SELECT...INTO out of the original table to create a new table
2. SELECT...INTO will not create any of the following dependent objects: indexes, constraints, defaults, rules and triggers. So, make sure you script these out. You can run sp_depends on the original table to get a list of these dependent objects.
3. Drop the original table (If there are foreign key constraints which refer this table, you
have to drop them now. Before dropping, script them, so that you can recreate them later)
4. Use sp_rename to rename the new table as the original table.
5. Recreate all the dependent objects that you have scripted out before in step 2 and 3.
6. Publish the table
7. Subscribe to the publication
(Continued on next question...)
