john_jakob (8/29/2008)
I ran into this problem several times with SQL 2000 replication -- 2005 might be similar.First try deleting all rows in the table in question at the subscriber, and start the replication going again--see if that works.
If that doesn't work, you might have to reinitialize the publication from scratch. Not a pleasant thing from anyone's perspective.
- j
There's a much more precise way to solve this. If you don't care about data consistency at the subscriber then simply change the distribution agent profile to ignore data consistency errors, restart the agent, and move on.
If you want to know the command that's failing then start by looking at the error in Replication Monitor or by executing sp_helpsubscriptionerrors (in the distribution database on the distributor). Syntax, including required parameters, can be found here: http://technet.microsoft.com/en-us/library/ms173427.aspx... that'll get you the xact_seqno and command_id.
You also need the publisher database id which you can get by runing this query on the distributor (substitute values in quotes for your situation):
SELECT DISTINCT
subscriptions.publisher_database_id
FROM sys.servers AS [publishers]
INNER JOIN distribution.dbo.MSpublications AS [publications] ON publishers.server_id = publications.publisher_id
INNER JOIN distribution.dbo.MSarticles AS [articles] ON publications.publication_id = articles.publication_id
INNER JOIN distribution.dbo.MSsubscriptions AS [subscriptions] ON articles.article_id = subscriptions.article_id
AND articles.publication_id = subscriptions.publication_id
AND articles.publisher_db = subscriptions.publisher_db
AND articles.publisher_id = subscriptions.publisher_id
INNER JOIN sys.servers AS [subscribers] ON subscriptions.subscriber_id = subscribers.server_id
WHERE publishers.name = 'MyPublisher'
AND publications.publication = 'MyPublication'
AND subscribers.name = 'MySubscriber'
Now plug the xact_seqno, xact_seqno, and publisher_database_id into this query (again substituting the values with the ones you've gotten from the queries above):
EXECUTE distribution.dbo.sp_browsereplcmds
@xact_seqno_start = '0x0000001900001926000800000000',
@xact_seqno_end = '0x0000001900001926000800000000',
@publisher_database_id = 29,
@command_id = 1
Once you know what command is failing you can figure out what you need to change at the subscriber for replication to get past the error.