Replicating DATE columns from SQL Server 2008 to SQL Server 2014

Replication
1

Has anyone had any luck replicating DATE columns from 2008 to 2014?

Publisher: SQL Server 2008
Distributor: SQL Server 2014
Subscriber: SQL Server 2014

All have the latest service packs.

I'm doing a concurrent snapshot to initialize. It won't apply the snapshot. It gives me a truncation error on the DATE column. If I go look in the file BCP generates I see the DATE value in plain text. When I go manually run BCP it has the DATE coumn in what I believe is native mode.

Has anyone had any luck with this?

Here's the raw error:

Error messages:
The process could not bulk copy into table '"dbo"."CompensationDetail"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Bulk load data conversion error (truncation) for row 1, column 9 (PaymentDate). (Source: MSSQLServer, Error number: 4863)
Get help: http://help/4863
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. (Source: MSSQLServer, Error number: 7399)
Get help: http://help/7399
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". (Source: MSSQLServer, Error number: 7330)
Get help: http://help/7330

My Google-foo has turned up some similar errors and a closed CONNECT item but no clever work-arounds.

2

It turns out it was exporting using bcp. And importing via the BULK INSERT statement. And they weren't exactly compatible. I removed the -UnInprocLoader option and DATE columns and VARCHAR(MAX) columns worked just fine. Much slower to load though.

3

I know nothing about this in the context of applying snapshots, but when we use BCP to transfer data from on SQL DB to another we always use Native mode. Any for of plain-text seems "fragile" to me for all sorts of reasons, although "truncated dates" would not have been on my expected-possible-outcomes list ... but it is now!

4

you can try to reinitialize and apply the snapshot again.

You may have a look here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c62545f7-0211-49c4-958f-8bd039d086e3/the-process-could-not-bulk-copy-into-table-dbocomplaint-source-mssqlrepl-error-number?forum=sqlreplication