Primary Key Violation - Trn Repl

  • Hello

    The following is the error i am encoutering.

    2009-10-12 22:45:19.579 Agent message code 20037. The process could not bulk copy

    into table '"dbo"."MY_TABLE"'.

    2009-10-12 22:45:19.683 Category:NULL

    Source: Microsoft SQL Server Native Client 10.0

    Number:

    Message: Batch send failed

    2009-10-12 22:45:19.716 Category:NULL

    Source: Microsoft SQL Server Native Client 10.0

    Number: 2627

    Message: Violation of PRIMARY KEY constraint 'MYTABLE_PKEY'. Cannot insert duplicate key

    in object 'dbo.MY_TABLE'.

    2009-10-12 22:45:19.741 Category:NULL

    Source: Microsoft SQL Server Native Client 10.0

    Number: 20253

    Message: To obtain an error file with details on the errors encountered when initializing

    the subscribing table, execute the bcp command that appears below. Consult the BOL for

    more information on the bcp utility and its supported options.

    2009-10-12 22:45:19.766 Category:NULL

    Source:

    Number: 20253

    Message: bcp "LDTST"."dbo"."MY_TABLE" in

    "\\025edddb1\SQLREP\unc\SRCSERVER_LDTST_PUB_LDTST\20091012145537\MY_TABLE#1.bcp"

    -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SDESTSRVR -T -w

    I referred the following URL / website and introduced the -SkipErrors option. Does not seem to be helping.

    Skipping Errors in Transactional Replication

    http://msdn.microsoft.com/en-us/library/ms151331(SQL.90).aspx

    The -SkipErrors Parameter

    Also, when i created the Articles i chose the "keep existing object unchanged" option.

    Any help would be appreciated.

    Thanks

  • gk-411903 (10/21/2009)


    Hello

    The following is the error i am encoutering.

    2009-10-12 22:45:19.579 Agent message code 20037. The process could not bulk copy

    into table '"dbo"."MY_TABLE"'.

    2009-10-12 22:45:19.683 Category:NULL

    Source: Microsoft SQL Server Native Client 10.0

    Number:

    Message: Batch send failed

    2009-10-12 22:45:19.716 Category:NULL

    Source: Microsoft SQL Server Native Client 10.0

    Number: 2627

    Message: Violation of PRIMARY KEY constraint 'MYTABLE_PKEY'. Cannot insert duplicate key

    in object 'dbo.MY_TABLE'.

    2009-10-12 22:45:19.741 Category:NULL

    Source: Microsoft SQL Server Native Client 10.0

    Number: 20253

    Message: To obtain an error file with details on the errors encountered when initializing

    the subscribing table, execute the bcp command that appears below. Consult the BOL for

    more information on the bcp utility and its supported options.

    2009-10-12 22:45:19.766 Category:NULL

    Source:

    Number: 20253

    Message: bcp "LDTST"."dbo"."MY_TABLE" in

    "\\025edddb1\SQLREP\unc\SRCSERVER_LDTST_PUB_LDTST\20091012145537\MY_TABLE#1.bcp"

    -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SDESTSRVR -T -w

    I referred the following URL / website and introduced the -SkipErrors option. Does not seem to be helping.

    Skipping Errors in Transactional Replication

    http://msdn.microsoft.com/en-us/library/ms151331(SQL.90).aspx

    The -SkipErrors Parameter

    Also, when i created the Articles i chose the "keep existing object unchanged" option.

    Any help would be appreciated.

    Thanks

    you mentioned that you selected keep existing objects unchanged. that means, replication will not drop and recreate the destination tables. It will start inserting rows into the destination tables without any validation and if the rows already exists, you may see these errors.

    The workaround to get rid of these error is to set the option "Continue on Data Consistency error". It will ignore data consistency error and continue on replication.

    Alternation solutions:

    The permanent solution could be, if you can run the snapshot agent and recreate the destination tables from snapshot, however if you have any persisted data in destination table that does not exists in source table anymore, you will lose that persisted data.

    -or-

    Pause the replication, manually compare source and destination tables using joins, remove any consistency issues manually from destination table.

    Swarndeep

    http://talksql.blogspot.com

  • Hello Swarndeep

    Would you mind telling me where / how to set "Continue on Data Consistency error" option? In Bcp or the Destination agent job; I know of mentioning the Error code to skip but i tried it once and it did not seem to work. May be i missed something obvious.

    Your feedback would be appreciated.

    Thanks

  • Check this link out..

    http://technet.microsoft.com/en-us/library/ms151331.aspx

    -Roy

  • Roy

    Thank you for the link.

    @publisher='MyPubServer',@publisher_db='MyPubDB',@publication='MyPublication',@subscriber='MySubServer',@subscriber_db='MySubDB' shows the error codes correctly.

    I tried -SkipErrors in the Distribution , Subscription & Publishing agent run for error codes 2621;2627;20053. That does not seem to be helping. I have opened a ticket with MS Support. Will keep you posted once the issue is resolved. I am of the opinion that i missed something obvious. I am yet to try the other option sp_setsubscriptionxactseqno. I prefer to go one at a time since this is only Pre-Production testing.

  • gk-411903 (10/22/2009)


    Hello Swarndeep

    Would you mind telling me where / how to set "Continue on Data Consistency error" option? In Bcp or the Destination agent job; I know of mentioning the Error code to skip but i tried it once and it did not seem to work. May be i missed something obvious.

    Your feedback would be appreciated.

    Thanks

    Not sure if this is applicable anymore, but Continue on Data Consistency error can be found by right clicking any of the subscribers in Replication Monitor and choosing Agent Profile.

  • Roy

    Thanks for the ideas.The following needed to be done to over come the issue.

    1. SkipError 2627

    2. On the Publisher i had to run sp_addsubscription with @sync_type = N'replication support only'; This did the magic.

    The default is 'Automatic' which did not like the table being there.

    Also,

    3. The structure need to exist at the destination and the data be pre-populated. Then any further DML will be replicated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply