Tuesday, March 20, 2012

Applying the snapshot for Merge rep fails everytime because Connection is reset?

Hi guys, Im desperately hoping someone here can help me. Ive been trying to set up merge replication between 2 servers all weekend and it keeps failing. Im setting this up as follows
The Publisher/Distributor is a SQL Server 2005 machine, the database being replicated is 4gb, and the only subscriber is a SQL Server 2000 SP4 machine. I set up my publication normally, and set up the push subscription, adn set it to initialize immediately. It weill then start bulk copying and go on for about 3-4 hours before it fails and gives me the errors:

Error messages:
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
The process could not bulk copy into table '"dbo"."CONTENT"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
One or more BLOB columns could not be sent to the server, attempt to recover from the problem failed. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Communication link failure (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Unspecified error (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Failed to send batch after max errors (Source: MSSQLServer, Error number: 0)

This is driving me crazy as I havent slept in ages trying to get this fixed. Anyone have any idea whats going on? Its always one of 2 tables so far that keep failing, ContactLog and Content, what can I do?

For anyone who finds an error similar to this one, its the line "forcibly closed by the remote host" that was the most interesting. Well, found the solution here, in section 4.1.2, http://support.microsoft.com/kb/910228
Basically, SP1 for Win 2K3 includes a lovely feature to prevent DOS attacks, and rather cleverly it thinks SQL Server applying a snapshot is a DOS attack so closes the connection after a set amount of Connect/Disconnects. Jeebus wept. 1 simple registry key cost me my weekend :-|

Hopefully this will save someone the same heartache!

Keep up the good work guys
|||Hello Shane,

Did you find the solution to your problem if yes then please let me know also because i am also facing the same problem from last two weeks and i am not able to solve it and its causing a headache for me please tell me if u have the solution.
my email id is dr_hunaindurrani@.hotmail.com.

Thanks in advance.

No comments:

Post a Comment