Make your own free website on Tripod.com

SQL Server 7.0 Replication FAQ and common problems
Last updated: April 14th '01 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New




Receive & Send money over the net easily!
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
How to subscribe with ‘NOSYNC’ to a transactional publication?

To subscribe with ‘no synchronization’, while pushing or pulling the subscription, you can choose ‘No, the Subscriber already has schema and data’ in the ‘Initialize Subscription’ screen of the subscription wizard. This will assume that the subscriber already has the tables with data and that the stored procedures required by replication are already created on the subscriber. This behavior is documented in Books Online. For more information see the page titled Transactional Replication at mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/r_model_4.htm

So, it’s the user’s responsibility to create the tables with data and create the stored procedures, before the distribution agent runs. If you haven’t created these stored procedures, distribution agent will fail with the any of the following errors based on the type of operation the distribution task is trying to apply on the subscriber:

Error 2812: Cannot find stored procedure sp_MSins_Table1
Or
Error 2812: Cannot find stored procedure sp_MSupd_Table1
Or
Error 2812: Cannot find stored procedure sp_MSdel_Table1

In the above messages, Table1 is the name of the table that you are trying to replicate.

For every table that is replicated, the user has to manually create 3 stored procedures on the subscriber, one for INSERT, one for UPDATE and one for DELETE. The stored procedure code can be easily generated using the following system stored procedures which can be found in the master database:

sp_scriptinsproc
sp_scriptdelproc
sp_scriptmappedupdproc

All these procedures require a single parameter @artid, which is the ID of the article in the publication. This article id can be obtained by querying syspublications and sysarticles tables present in the publishing database.

The following query will help you find out the article ids for a publication.

select artid
from sysarticles sa, syspublications sp
where
sp.name = ‘Your_Publication_Name’ and
sp.pubid = sa.pubid

Eg: sp_scriptinsproc 1

The above command will output the stored procedure script. You need to run that script against the subscribing database.
How to let merge replication use your own rowguid column?

When you create a merge publication, SQL Server adds a rowguid column to all the underlying tables for uniquely identifying a row across multiple copies of the same table. For more information see Merge Replication in Books Online at: mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/r_model_10.htm

If you want to add that column by your own and let SQL Server use it in merge replication, your rowguid column should have the following properties:

- Should be of Uniqueidentifier datatype
- Should have the ROWGUIDCOL property set
- Should not allow NULLs
- Should have a default of newid()

Here is an example:

create table table1
(
i uniqueidentifier rowguidcol not null default newid(),
j int
)

Disclaimer and terms of use
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.