I have set up snapshot replications between two SQL Servers. There are multiple replication jobs, one for each database. One of subscriber jobs is failing with the error message
2024-07-29 06:05:55.516
Category: NULL
Source: Microsoft OLE DB Driver for SQL Server
Number: 4817
Message: Could not bulk load. The sorted column 'MsgTS' is not valid. The ORDER hint is ignored.
It seems that the job writes the timestamp column MsgTs into the .bcp file during the out operation, which is correct per Microsoft documentation. It is my understanding that this column should be ignored during bcp in operation, but it is not, causing this failure.
I have tried removing the column from the snapshot, but it would not let me, probably because it is part of the primary key on the table.
My question is - can the current implementation of the replication mechanism deal with a table which has a timestamp column as part of a primary key, or am I out of luck and must exclude the whole table from the replication job and come up with a custom solution to bring data across?
EDIT: So, the only solution found so far was to replace snapshot replication with a transactional one. Luckily for me, this particular database had a primary key on each table.
