I am attempting to set up pull transactional replication for a single database. As far as i can tell every table is replicating correctly except one. The replication monitor shows a fail, the table is named Counters. All of the errors are below. The main error is that there is a primary key violation, however there isn't. It's only a 20 row table, and i confirmed 1) there are no duplicate keys, and 2) it fully replicated all 20 rows anyways. So the question is, if it correctly replicated it, why isn't it realizing it?
As a test i did a DELETE on the subscriber table and waited 5 min, and sure enough it copied all 20 rows in but is still erroring in the replication log. You'll see at the end of the errors that it suggests running the bcp command, however when i ran it it said "Unexpected EOF encountered in BCP data-file". I confirmed the data that ends up in the table is good, and i tried copying the replication snapshot files directly to the server and it gives the same error. They are not 0 byte files or anything, they appear to have good data.
Any help is appreciated, the actual errors are below. I have tried completely trashing the subscriber database and starting over, as well as doing new snapshots and recreating the subscriptions.
Errors as follows:
The process could not bulk copy into table "dbo"."Counters"
The process could not bulk copy into table '"dbo"."Counters"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Batch send failed
Violation of PRIMARY KEY constraint 'PK_Counters'. Cannot insert duplicate key in object 'dbo.Counters'. (Source: MSSQLServer, Error number: 2627)
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. (Source: MSSQLServer, Error number: 20253)
bcp "xxxxxx"."dbo"."Counters" in "\\unc\to\repl\data\Counters_114.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -Sxxxxxxx -T -w (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
The strange part is that the table is incredibly simple. It is literally two columns, one primary key (auto identity), no extra indexes, no triggers, nothing special. It even only has 20 rows of data.
I ended up removing it from the publication and the replication went through fine, so i'm not sure if maybe the source table was corrupted or not. I meant to run dbcc on it but hadn't gotten around to it. I was using replication to migrate from one server to another, so it was easy enough to just recreate the table when i throw the switch.