better way to fix broken replication?

If replication ever blows up with an error like:
"The row was not found at the Subscriber when applying the replicated
command."
There is only one way I know to fix it:
Read the Last Command in the "Distribution Agent Error Details" box:
{CALL sp_MSupd_AcctHistory
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-01-01 00:00:00,7,0x0001)}
Then do a:
exec sp_browsereplcmds
and look for that same line from above. Grab the xact_seqno. Will this
always be the first row in the result set as in my testing?
Then do a:
select * from MSRepl_commands
where xact_seqno = 0x0000002C000000440001
and then delete those rows.
Is there a better way?
SQL2K SP3
TIA, ChrisR
no, insert a dummy record in the subscriber with the specific pk. Then
restart your agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:umzuGUlKFHA.3132@.TK2MSFTNGP12.phx.gbl...
> If replication ever blows up with an error like:
> "The row was not found at the Subscriber when applying the replicated
> command."
> There is only one way I know to fix it:
>
> Read the Last Command in the "Distribution Agent Error Details" box:
> {CALL sp_MSupd_AcctHistory
> (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-01-01 00:00:00,7,0x0001)}
> Then do a:
> exec sp_browsereplcmds
> and look for that same line from above. Grab the xact_seqno. Will this
> always be the first row in the result set as in my testing?
> Then do a:
> select * from MSRepl_commands
> where xact_seqno = 0x0000002C000000440001
> and then delete those rows.
> Is there a better way?
>
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Far too easy! ;-)
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#mogudlKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> no, insert a dummy record in the subscriber with the specific pk. Then
> restart your agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <bla@.noemail.com> wrote in message
> news:umzuGUlKFHA.3132@.TK2MSFTNGP12.phx.gbl...
>
|||What about other scenarios where thats not an option. Say the Publisher has
a column that allows NULL's, but for some lame reason the Subscriber
doesn't? A row with a NULL gets Inserted into the Publisher, but chokes out
replication when it tries to insert into the Subscriber.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#mogudlKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> no, insert a dummy record in the subscriber with the specific pk. Then
> restart your agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <bla@.noemail.com> wrote in message
> news:umzuGUlKFHA.3132@.TK2MSFTNGP12.phx.gbl...
>
|||use your replication stored procedures to test for this condition and
possibly use coalesce to insert a value.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:OhvcallKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> What about other scenarios where thats not an option. Say the Publisher
has
> a column that allows NULL's, but for some lame reason the Subscriber
> doesn't? A row with a NULL gets Inserted into the Publisher, but chokes
out[vbcol=seagreen]
> replication when it tries to insert into the Subscriber.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:#mogudlKFHA.1476@.TK2MSFTNGP09.phx.gbl...
00:00:00,7,0x0001)}
>