Transactional Replication failing - "The process could not bulk copy"

Question

Hello everybody,

I'm trying to setup transaction replication between 2 servers. This is a
one-way replication: Server A to Server B, not Server B to Server A.

I am able to replicate all the tables except one. I added
commands to the agent so that it would create an output file, possibly with
more or better information.

Here is a portion of the error causing the failure

Agent message code 20037. The process could not bulk copy into table
'"tblSuppContractFee"'.
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01,
0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 65,
Transaction Seqno = 000075400000ff9b000b00000002, Command ID = 6
Message: Replication-Replication Distribution Subsystem: agent
01sqlft003-EDGE-01SQLFT004-4 failed. The process could not bulk copy into
table

'"tblSuppContractFee"'.[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call
sp_MSadd_repl_alert(3, 4, 65, 14151, ?, 6, N'01sqlft003', N'EDGE',
N'01SQLFT004',

N'EDGE_REPLICATION', ?)}
ErrorId = 65, SourceTypeId = 1
ErrorCode = ''
ErrorText = ''
[5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_repl_error(65,
0, 1, ?, N'', ?)}

[5/5/2006 8:02:10 PM]01SQLFT004.EDGE_REPLICATION: exec
dbo.sp_MSupdatelastsyncinfo N'01sqlft003',N'EDGE', N'', 0, 6, N'The process
could not bulk copy into table
''"tblSuppContractFee"''.'

Can somebody help me in finding a solution for this error? I don't see any
Error Text and there are no resources available for the error code throwing up
in the log file.

Thanks in advance.

Answer 1

Can you rerun your distribution agent and add parameter -OutputVerboseLevel 3.  This will give more details about what statement is failing.

 

Answer 2

Have you found a solution?

---

-- Information is king!

Answer 3

Distribution agent basically uses the BCP utility to insert the records into tables during initial snapshot. It is failing during BCP.

Can you check the Distribution Agent profile? and check for the value of -BCPBatchSize. Try to reduce this value to 1000 or 10000 and run the distribution agent again. once you reduces the batchsize value, this should work.

To got to agent Profile: Right Click on Distribution Agent into replication monitor>Select Agent Profile > Click on three dot button (...) for the profile which is currently selecetd> it will open the profile values> Edit the -BCPBatchSize value> Click OK>Click OK and restart the Distribution agent.

---

Kindly mark the reply as answer if they help

Answer 4

Daniel - what is the exact error message you are getting?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 5

Daniel - what is the exact error message you are getting?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Hi Hilary,

The message I am getting is:

2012-08-12 23:27:57.416 Agent message code 20037. The process could not bulk copy into table '"dbo"."TABLE_XYZ"'.
2012-08-12 23:27:57.432 Category:NULL
Source:  Microsoft SQL Server Native Client 10.0
Number:  
Message: Data conversion failed
2012-08-12 23:27:57.448 Category:NULL
Source:  
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.
2012-08-12 23:27:57.448 Category:NULL
Source:  
Number:  20253
Message: bcp "DB"."dbo"."TABLE_XYZ" in "E:\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc\DISTRIBUTION\20120813010005\TABLE_XYZ_46.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -S MSSQL01 -T -w

I really have no idea where to start since it does work one day and not the other day.

---

-- Information is king!

Answer 6

There has been created a ticket at Microsoft Connect: http://connect.microsoft.com/SQLServer/feedback/details/125206/oracle-replication-distribution-job-fails-to-apply-data#tabs

Please vote if you have the same issue!

---

-- Information is king!

Answer 7

can you run that bcp command from a command prompt?

Are the table structures identical on both sides?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

can you run that bcp command from a command prompt?

Are the table structures identical on both sides?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Yes, I can run it with a BCP command from command promt, no errors at all. And the structures are the same at both sides. What I have done to resolve is is to use the BULK INSERT option by adding -UseInProcLoader in the job. Since the BULK INSERT works, I guess it is in the BCP command from Replications Services since it works when using the BCP in command promt.

Though I have not an answer on the issue, I have a solution which works: BULK INSERT.

Thank you!

---

-- Information is king!