Fix broken transactional replication in SQL Server 2005

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)

  • getting output of sqlcmd query in to batch variable
  • Convert NSDate to Sql Server Datetime format
  • Affecting performance with SQL Collation
  • How to delete rows in tables that contain foreign keys to other tables
  • Strange behavior when reading xml using OPENXML WITH (schema)
  • TSQL and Soap Envelope
  • 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?

  • iPhone Sdk can Access SQL Server Directly
  • Summing a column up to a certain row (using GROUP BY and OVER)?
  • Incorrect Syntax near 'Contribution'
  • SQL select statement with if logic
  • SQL Query to store text data in a Varbinary(max)
  • SSIS package creation for integrating MSSQL and MySql dbs
  • 2 Solutions collect form web for “Fix broken transactional replication in SQL Server 2005”

    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.

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.