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