I've set-up a transactional replication in SQL Server 2005 and I've disabled the DDL trigger that were created when setting up replication. After playing a bit (to understand what will happen) the transaction is broken, because I've changed a column on publisher to VARCHAR
that is INT
on subscriber. I've found two procedure, which will help to skip this buggy transaction
EXEC sp_helpsubscriptionerrors
@publisher='DB1',@publisher_db='repl_test',@publication='repl_test_table',
@subscriber='DB2',@subscriber_db='repl_test'
That gives me a resultset where I can see the error (failure on converting VARCHAR
to INT
)
id time [...] error_code error_text xact_seqno
24 2012-02-23 08:33:35.313 [...] 8114 Fehler beim Konvertieren des varchar-Datentyps in int. 0x00139791000CC79C000600000000
24 2012-02-23 08:33:35.310 [...] 8114 Fehler beim Konvertieren des varchar-Datentyps in int. 0x00139791000CC79C000600000000
[...]
and I use the xact_seqno
from result (0x00139791000CC79C000600000000) to execute following procedure in next step
EXEC sp_setsubscriptionxactseqno
@publisher='DB1',@publisher_db='repl_test',@publication='repl_test_table',
@xact_seqno=0x00139791000CC79C000600000000
which will say:
Error 20017 - The subscription doesn't exist on subscriber.
Huh?
Ahhhh, having a look at internal code of sp_setsubscriptionxactseqno
one can see, that this procedure has to be executed in the subscription database (and not in distribution
database, where sp_helpsubscriptionerrors
has to be executed)...but this isn't documented in MSDN.