SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Primary Key Violation - Trn Repl


Primary Key Violation - Trn Repl

Author
Message
gk-411903
gk-411903
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 420
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
Swarndeep
Swarndeep
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1155 Visits: 377
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
gk-411903
gk-411903
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 420
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
Roy Ernest
Roy Ernest
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13754 Visits: 6904
Check this link out..
http://technet.microsoft.com/en-us/library/ms151331.aspx

-Roy
gk-411903
gk-411903
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 420
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.
thecodeguys
thecodeguys
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 456
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.
gk-411903
gk-411903
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1303 Visits: 420
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search