1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rabudde
  • 7,498
  • 6
  • 53
  • 91

2 Answers2

2

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.

rabudde
  • 7,498
  • 6
  • 53
  • 91
0

Did executing sp_setsubscriptionxactseqno on the subscriber work for you?

You can also use the Distribution Agent parameter -SkipErrors 8114 to temporarily skip this error type.

Skipping Errors in Transactional Replication

Brandon Williams
  • 3,695
  • 16
  • 19