Search code examples
sql-serversql-server-2014replicationbcptransactional-replication

SQL Server Transactional replication - The process could not bulk copy into

So I have setup T-replication from Publisher (SQL Server 2014) Distributor (SQL Server 2014) Subscriber (SQL Server 2008 R2) and initialized it using a snapshot.

Checking in the replication monitor I find that the Snapshot agent has completed successfully and Log Reader agent is running.

Now in 'Distributor to Subscriber History' tab just beside the 'Undistributed Commands' Tab

I get the following error:

The process could not bulk copy into table '"dbo"."BEAMDATA"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
End of file reached, terminator missing or field data incomplete
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)
Get help: http://help/20253
bcp "LOWIS_BUCT"."dbo"."BEAMDATA" in "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\ReplData\unc\LOWISBUCT_CSSQLDB_BUCT_CSSQLDB_BUCT_ALL_TABLES\20160826064516\BEAMDATA_34#1.bcp" -e "errorfile" -t"\n\n" -r"\n<,@g>\n" -m10000 -SLOWISTSTSQL -T -w (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253

I thought this could be some kind of data overflow and hence checked the schema of the table at both Publisher and Distributor and they match exactly.

I cleaned the whole replication setup completely and re did it but still stuck at the very same place for the same table.

Has anyone encountered this before? Ask me if you need more information from my end which I can furnish.

Solution

  • I found the reason for this. It was due to the schema mismatch at the publisher and subscriber. In the said table, column had the datatype (date(datetime) and when replication scripted the schema for this table it was scripted as date hence at subscriber when the snapshot was applied, the field had the data type of date.

    When data was being copied from datetime to date field it resulted in the said error.

    I did the necessary changes in the data type at the subscriber end and things got fixed,