Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to replicate to a 4th subscriber on a remote site. 3 subscribers work fine locally. the other difference is this is a 2012 subscriber from a 2008 r2 publisher and remote distributor.

When delivering the bcp commands it fails with the following error.

The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)

I have tried raising the historyverbose level to 2, but this is the only error i get from the agent history.. I have attempted to recreate the snapshot, but this still fails at the same point...

nothing unusual about the table, and its 5 million rows, so not massive amounts of data...

Column_name Type    Computed    Length
FlightId    int no  4
OrigAirport char    no  3
DestAirport char    no  3
DeptDate    datetime    no  8
ArriveDate  datetime    no  8
Airline char    no  10
SupplierId  int no  4
UpdateRef   int no  4
HarvestUpdateKey    int no  4
ActiveFlag  bit no  1

Can anyone help me diagnose this to get a useful error or suggest what the problem might be?? the connection is very slow to this datacentre which is a issue we are attempt to resolve... could this simply be a timeout ???

UPDATE - I configured a seperated publication with just this table in and set the verbose logging to 2. I then get

Error messages:
The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)

An existing connection was forcibly closed by the remote host.
Unspecified error
Failed to send batch after max errors
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)

Communication link failure (Source: MSSQLServer, Error number: 10054)

so looks like the network between datacentres... strange that it would always die at the same point, would there be a reason for this ?

The error log on the target server
Error: 4014, Severity: 20, State: 13
A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).

share|improve this question
    
It should give you more information in the history of the replication monitor. Is there nothing more useful in there? –  mrdenny Nov 3 '12 at 15:06
    
im afraid not that initial message I pasted was from the monitor job history. –  DamagedGoods Nov 5 '12 at 7:42
    
Check the target ERRORLOG –  Remus Rusanu Nov 5 '12 at 9:29
    
Also check the actual job step history for the job which is running the replication agent. –  mrdenny Nov 9 '12 at 20:51
add comment

2 Answers 2

If the link is bad, you must overrride -QueryTimeOut and -KeepAliveMessageInterval parameters. Check the SQL Server Agent job, identify the replication Job, then right-click Properties, then Steps, Edit and at the end of Commend just add -LoginTimeOut 120 -QueryTimeOut 7200 -KeepAliveMessageInterval 600

share|improve this answer
add comment
up vote 0 down vote accepted

I couldn't find a reasonable explanation why this was happening as there are other, wider tables in that publication withmore data and even BLOB's in and they replicated fine. so I'm not really providing this as an answer, just a possible workaround for someone in a similar situation.

The workaround that I had to settle with was separating the article into its own publication, then whenever it failed it only had to loop through the 12 bcp files already delivered before getting to the timeout, then eventually after 9 hours overnight, it failed and retried enough times where it eventually crept through the remaining BCP files.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.