October 21, 2009 at 6:41 pm
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
October 21, 2009 at 8:45 pm
gk-411903 (10/21/2009)
HelloThe 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
October 22, 2009 at 11:00 am
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
October 22, 2009 at 12:41 pm
October 23, 2009 at 11:14 am
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.
October 28, 2009 at 9:49 am
gk-411903 (10/22/2009)
Hello SwarndeepWould 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.
November 9, 2009 at 12:32 pm
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