(Continued from previous part...)

Merge agent is failing with the error: 'The process could not initialize "database reconciler". Check to see if the component is registered correctly’. Where to look?

Again this is a problem with missing DLLs. Copy the any of the following missing DLLs from x86\Binn on the CD to “C:\Program files\Common files\Microsoft Shared\Database replication\” folder on the SQL Server computer:
rdistcom.dll replerrx.dll replprov.dll replrec.dll replres.dll sqldistx.dll sqlmergex.dll

How to change the schema of a published table?

In SQL Server 7.0, if you have to change the schema of a replicated table, first you have to unsubscribe to all the publications, which are based on this table. Then, delete all the publications. Do the schema change. Republish the table and resubscribe. You can do NOSYNC, because the data is already there at the subscriber.

You can script the replication in 7.0, so that you don’t have to recreate the publications and subscriptions manually, every time you change the schema. In Enterprise Manager, go to ‘Tools/Replication/Generate Replication Scripts…’ to generate replication scripts.

Note: Pull subscriptions cannot be scripted and can’t be traced in Profiler for security reasons.

The next release of SQL Server is going to support Schema Replication, where you don’t need to do all the above steps if you want to change the schema of a replicated table.

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 part...)