sql server replication: I have been able to reproduce this error twice. In general, the sp_MS*
procedures are working fine on all of the other tables that I have tested so
far, but for our PointOfSales table, the distribution agent tries to execute
the following:

{CALL sp_MSdel_PointOfSales (4952)}
Transaction sequence number and command ID of last execution batch are
0x0001431300005005000100000000 and 1.

and we receive the following error:

DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_AS_AuthorizedIPs_PointOfSales'. The conflict occurred in database 'Indy',
table 'AS_AuthorizedIPs', column 'PointOfSaleID'.
(Source: SDCSQL2 (Data source); Error number: 547)
---------------------------------------------------------------------------------------------------------------
The row was not found at the Subscriber when applying the replicated command.
(Source: SDCSQL2 (Data source); Error number: 20598)
---------------------------------------------------------------------------------------------------------------


We did not perform a delete command at the publisher and the row still
exists in the PointOfSales table at the publisher. We did perform an update
on a single column of this table when this error occurred.

Has anyone ever seen issues like this before?

Also, once this occurs, I have to disable replication and add it back in.
This is ok for the testing that I am doing because I'm checking each table
one at a time and watching the distribution agent for errors. The idea here
is to work out all of these issues before it's installed into production, but
I'm wondering if there is a white paper out there that might suggest how to
recover from these issues (e.g. if this had actually occurred in production,
I'm sure that there would be a lot of other transactions backed up due to the
error, it would be nice to clear the error, run the consistency check and
then perform a snapshot on the specific articles that are out of
sync...unfortunately, I don't know how to clear the issue without disabling
replication).

Thanks,

Larry Herbinaux

This applies to updates of columns which are part of a unique index and is
known as a deferred update. You'll know when it's happening as the procedure
sp_MSupdtablename never executes on the subscriber, only the
sp_MSdeltablename and sp_MSinstablename procedures (although this could also
occur if the snapshot commands tab shows 'NONE' for the update command!).
Implementing trace flag 8207 will replicate the update as an update on the
subscriber, but only if it is a singleton update. See KB Article
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Thanks for the info Paul,

The column that is being updated is not part of a unique index. As for the
second possibility, I'm not quite sure what you mean by "this could also
occur if the snapshot commands tab shows 'NONE' for the update command". We
don't do snapshot replication, rather on initial install, we have the exact
copies of the databases on both the publisher and subscriber. We generate
the sp_MS* stored procedures by running the sp_scriptpublicationcustomprocs
command on the publisher and running the results on the subscriber. In your
statement above, I think you are talking about the check box for the update
command on the commands tab for the default table article properties which is
checked to use the spMSupd* stored procedures. Is this what you are
referring to?

The symptoms that are occurring do sound like a deferred update. I
obviously would rather this occur as a normal update rather than a delete /
insert, but the most disturbing thing is that the delete part does not work
because it fails to perform the deletes on the table dependencies (e.g.
removing the associated rows in other tables that contain this table's
primary key as a foreign key). Is this a known issue of deferred updates?
Is there a work around if one decides not to use the -T8207 option?

I will try the work around. We are a candidate for this solution because we
are doing a one-way transactional replication from publisher to subscriber.
The publisher is our TX database server and our subscriber is our Reporting
database server (offloading reporting requests so that we have better
concurrency for transactions).

One other question that I have is if there are inconsistencies with some
table articles between the publisher and the subscriber, what is the best and
quickest way of resolving these inconsistencies. Is there a way to do this
on a per table article basis? The reason why I ask this is that our plan is
to truncate all tables that have to do with "application transactions" on the
TX database server so that we can guarantee a certain transaction latency no
matter how many transactions that we have processed. So in this case, the
tables that have to do with "application transactions" will contain less data
on the TX database server than on the Reporting database server and we don't
want to wipe out our historical transactions on the Reporting database
server. The method that I am going to try is to drop replication and setup
replication on all tables minus the "application transactions" tables,
perform a snapshot replication on the table articles and then add the other
tables as articles after the snapshot replication. This seems like a very
arduous process...any suggestions?

[quoted text, click to view]