Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Primary Key Violation - Trn Repl Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 6:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:24 PM
Points: 169, Visits: 419
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
Post #806909
Posted Wednesday, October 21, 2009 8:45 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:42 AM
Points: 481, Visits: 318
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
Post #806928
Posted Thursday, October 22, 2009 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:24 PM
Points: 169, Visits: 419
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
Post #807326
Posted Thursday, October 22, 2009 12:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
Check this link out..
http://technet.microsoft.com/en-us/library/ms151331.aspx


-Roy
Post #807388
Posted Friday, October 23, 2009 11:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:24 PM
Points: 169, Visits: 419
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.




Post #808074
Posted Wednesday, October 28, 2009 9:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 12:05 PM
Points: 64, Visits: 454
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.
Post #810077
Posted Monday, November 9, 2009 12:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 11:24 PM
Points: 169, Visits: 419
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.

Post #816048
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse