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.

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.

Applying the Schema Snapshot and Partitioned Snapshot to a NewSubscriber

If client is synchronizing for first time with server at that time
Snapshot is applied to the client database. If meanwhile application
crashes or network goes down then snapshot is partially applied. When
i am restarting my application and trying to synchronize again my
server database gets corrupted (i.e. some rows get deleted from
central server)
So when a new subsciber is synchronising for first time and meanwhile
the process crashes then what will happen and how to resolve it.
On Nov 15, 12:07 pm, Manish <jainmani...@.gmail.com> wrote:
> If client is synchronizing for first time with server at that time
> Snapshot is applied to the client database. If meanwhile application
> crashes or network goes down then snapshot is partially applied. When
> i am restarting my application and trying to synchronize again my
> server database gets corrupted (i.e. some rows get deleted from
> central server)
> So when a new subsciber is synchronising for first time and meanwhile
> the process crashes then what will happen and how to resolve it.
Currently I done following and problem disappeared. But pls suggest
other options.
When i observed replication related system tables i decided to kill
processed to related to
string sql = "select spid from master.dbo.sysprocesses WHERE
program_name like '" + getMergeAgentProgramName() + "%'"; where
getMergeAgentProgramName() is as follows
private string getMergeAgentProgramName()
{
return mPublisher + "-" +
mPublicationDatabase + "-" +
mPublication + "-" +
mSubscriber;
}
the query will get processes related to replication
after that i killed each process selected under above sql query.
foreach (string spid in spIds)
{
sql = "kill " + spid;
publisherConn.ExecuteNonQuery(sql);
}
but the solution seems un scalable and may be unreliable.

Applying Snapshot Offline

I have snapshot files on CD , how can apply to
subscriber offline , without connecting to publisher
pl guide thorugh example or link
thanks
Fatiya
Fatiya,
if you step through the pull subscription wizard, there is the option to
browse to an alternative snapshot location.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

applying snapshot from alternate location

Hi

We are using HTTPS merge replication - is it possible to specify an alternative location when applying the initial snapshot ?

Thanks
Bruce

yes, it's possible. Is it not working in your case?|||

I'm talking about where the subscriberType is anonymous if that helps...

Our setup is as follows...

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

I can't see anything in the doco about specifying alternate snapshot location in this case (where subscription.SyncType = SubscriptionSyncType.None)

At the moment we are persisting with downloading the initial snapshot over the 'net - which for some of our clients in regional Australia takes forever - they are on horrible dial-up connections - it's the darkages in parts of this country !

So if I can download the initial snapshot locally to get them going it'll be a relief !

Thanks
Bruce

|||

Hi Bruce,

From your posts, I gather that you are using a no-sync subscription.

You can specify an alternate snapshot location. BOL has more info on that.

However in such a scenario, I do not see the need to download all the files because it is a no-sync subscription and need only few files. But due to a bug, we indeed do download all the files from the snapshot location.

So even if you do provide an alternate snapshot location, the files will still be downloaded even though all may not be applied.

I have seen this happen internally concurrently and I happened to read your post today.

I hope that we can fix it in the Service Pack 2 timeframe. Till then, you will have to bear ths burden of downloading the files.

applying snapshot from alternate location

Hi

We are using HTTPS merge replication - is it possible to specify an alternative location when applying the initial snapshot ?

Thanks
Bruce

yes, it's possible. Is it not working in your case?|||

I'm talking about where the subscriberType is anonymous if that helps...

Our setup is as follows...

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

I can't see anything in the doco about specifying alternate snapshot location in this case (where subscription.SyncType = SubscriptionSyncType.None)

At the moment we are persisting with downloading the initial snapshot over the 'net - which for some of our clients in regional Australia takes forever - they are on horrible dial-up connections - it's the darkages in parts of this country !

So if I can download the initial snapshot locally to get them going it'll be a relief !

Thanks
Bruce

|||

Hi Bruce,

From your posts, I gather that you are using a no-sync subscription.

You can specify an alternate snapshot location. BOL has more info on that.

However in such a scenario, I do not see the need to download all the files because it is a no-sync subscription and need only few files. But due to a bug, we indeed do download all the files from the snapshot location.

So even if you do provide an alternate snapshot location, the files will still be downloaded even though all may not be applied.

I have seen this happen internally concurrently and I happened to read your post today.

I hope that we can fix it in the Service Pack 2 timeframe. Till then, you will have to bear ths burden of downloading the files.

applying snapshot

i want to prevent some referential integrity scripts to be deployed at the
subscriber *before* the snapshot is applied , is there a way to interfere
with initial snapshot programmatically?
thanks in advance
There are a couple of ways. You can use the Pre- and Post snapshot commands
to run extra scripts eitehr before or after (or both). You can stop the
distribution task and hand-edit the snapshot after it is created. Note that
the second method will have to be redone on every new snapshot.
As for Referential Integrity, they are not automatically applied since you
may choose to replicate child tables without the parent tables. You can add
them on, but it is not recommended since they tend to interfere with
snapshot application.
Geoff N. Hiten
Microsoft SQL Server MVP
"uykusuz" <yb> wrote in message
news:ewKbjm9gFHA.3568@.TK2MSFTNGP10.phx.gbl...
>i want to prevent some referential integrity scripts to be deployed at the
> subscriber *before* the snapshot is applied , is there a way to
> interfere
> with initial snapshot programmatically?
>
> thanks in advance
>

Applying of snapshot for transaction replication with DTS transfor

I created transaction replication with DTS transformation. Next I want to
move all data from publisher to subscriber - apply snapshot to subscriber.
Subscriber have different schema but no data. How it is possible to do? I
expected snapshot will use same DTS, but it not use it. Publisher DB is 24/7
system.
Have a look at transformable subscriptions. I would also advise you to have
a look at custom sync objects and encapsulating the data mapping in the
replication stored procedures as opposed to use DTS transforms due to
performance reasons.
Here is an article explaining how to do this.
http://www.dbazine.com/sql/sql-artic...rm=replicating
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:53CF05CA-FA93-4AAE-A4AA-37347B3060E7@.microsoft.com...
>I created transaction replication with DTS transformation. Next I want to
> move all data from publisher to subscriber - apply snapshot to subscriber.
> Subscriber have different schema but no data. How it is possible to do? I
> expected snapshot will use same DTS, but it not use it. Publisher DB is
> 24/7
> system.

Applying Initial Snapshot

Hi,
I have configured my system as a Publisher & Distributor. I have also
added another machine in my network as the Subscriber. I have used
'Transactional Replication' method. So when I am trying to initialize the
Replication, the Subscriber, in Status says that, 'Searching for the Initial
Snapshot' and after that fails. Then I later came to know that, to apply
Transactional Snapshot, initially we need to apply the Snapshot Replication.
But how to do this.
Now I have created both Snapshot and Transactional Replication in my
system. But when I am trying to apply the Snapshot, the subscriber is not
updated and instead in the Status, it says that 'FAILED'. I have given the
full rights to the Replication Folders.
So how can I solve this? I want to update the subscribers.
If you did a normal transactional publication and selected the option Yes
Initialize the Schema and Data a snapshot will be generated and distributed.
What is the error message you are getting?
"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:07B0439D-AAF2-445E-8DA5-F2B765C06979@.microsoft.com...
> Hi,
> I have configured my system as a Publisher & Distributor. I have also
> added another machine in my network as the Subscriber. I have used
> 'Transactional Replication' method. So when I am trying to initialize the
> Replication, the Subscriber, in Status says that, 'Searching for the
> Initial
> Snapshot' and after that fails. Then I later came to know that, to apply
> Transactional Snapshot, initially we need to apply the Snapshot
> Replication.
> But how to do this.
> Now I have created both Snapshot and Transactional Replication in my
> system. But when I am trying to apply the Snapshot, the subscriber is not
> updated and instead in the Status, it says that 'FAILED'. I have given the
> full rights to the Replication Folders.
> So how can I solve this? I want to update the subscribers.
>

Apply snapshot regularly

I am planning to do a snapshot regularly once a month to make sure that the
publshing database and subscriber database are always in sycn. Our system
is 24x7 but night time and weekend are relatively quiet. Is the correct
process for this is to do the 'Reinitialize All subscriptions' in Replication
Monitor or just start the Snapshot Agents? Do I need to shut down the
Distribution Agent and/or Log Reader Agent first before I do anyone of these
options?
Also, will the tables in the publishing database that are being transferred
over to the subscribing server be locked by SQL server during the transfer?
wingman
There is no point to do this unless you know you are out of sync. I would
run monthly or weekly validations to see if you are out of sync. Then if you
are reinitialize and then manually start the snapshot agent. The log reader
and distribution agents run fine when you are simultaneously generating a
snapshot.
For named subscriptions the snapshot will only be generated when you have a
new subscription, or when you have reinitialized a subscription or
subscriptions. For anonymous subscriptions a snapshot will be run each time
your snapshot agent runs.
While the snapshot agent is being run the published tables will be locked.
After the snapshot has finished the locks will be released, while the
distribution and log reader agents are running they will not hold any locks
on the published tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:B03834F3-CA1F-4CC8-9A79-E59593C3CD33@.microsoft.com...
>I am planning to do a snapshot regularly once a month to make sure that the
> publshing database and subscriber database are always in sycn. Our
> system
> is 24x7 but night time and weekend are relatively quiet. Is the correct
> process for this is to do the 'Reinitialize All subscriptions' in
> Replication
> Monitor or just start the Snapshot Agents? Do I need to shut down the
> Distribution Agent and/or Log Reader Agent first before I do anyone of
> these
> options?
> Also, will the tables in the publishing database that are being
> transferred
> over to the subscribing server be locked by SQL server during the
> transfer?
> wingman
|||Thanks for the great detail. I appreciate that a lot. Just one quick
follow-up question, say I only have one subscription, what is the difference
between 'reinitialize all subscriptions' and running the 'snapshot' again?
Wing
"Hilary Cotter" wrote:

> There is no point to do this unless you know you are out of sync. I would
> run monthly or weekly validations to see if you are out of sync. Then if you
> are reinitialize and then manually start the snapshot agent. The log reader
> and distribution agents run fine when you are simultaneously generating a
> snapshot.
> For named subscriptions the snapshot will only be generated when you have a
> new subscription, or when you have reinitialized a subscription or
> subscriptions. For anonymous subscriptions a snapshot will be run each time
> your snapshot agent runs.
> While the snapshot agent is being run the published tables will be locked.
> After the snapshot has finished the locks will be released, while the
> distribution and log reader agents are running they will not hold any locks
> on the published tables.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:B03834F3-CA1F-4CC8-9A79-E59593C3CD33@.microsoft.com...
>
>
|||If you run the snapshot again the snapshot will only be generated if there
is a subscription that needs it or if you are using anonymous subscriptions.
Note that if you are using anonymous subscribers the snapshot will be
generated but not sent (unless a subscriber needs one which occurs in two
cases - it does not yet have a snapshot, or if you have reinitialized this
subscriptions or all subscriptions)
if you reinitialize all subscriptions, a snapshot will be generated the next
time your snapshot agent runs and be distributed to all of your subscribers.
So reinitialize is saying this subscriber(s) needs a new snapshot and the
snapshot will be generated the next time the snapshot agent runs, running
the snapshot agent merely will generate a snapshot if someone needs it for
named, or every time for anonymous.
Hope this makes sense - its a difficult concept to understand.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:9E592EC9-CB21-41CD-A274-4FEE79AA940A@.microsoft.com...[vbcol=seagreen]
> Thanks for the great detail. I appreciate that a lot. Just one quick
> follow-up question, say I only have one subscription, what is the
> difference
> between 'reinitialize all subscriptions' and running the 'snapshot' again?
> Wing
> "Hilary Cotter" wrote:
|||Thanks!! I got it now.
"Wingman" wrote:

> I am planning to do a snapshot regularly once a month to make sure that the
> publshing database and subscriber database are always in sycn. Our system
> is 24x7 but night time and weekend are relatively quiet. Is the correct
> process for this is to do the 'Reinitialize All subscriptions' in Replication
> Monitor or just start the Snapshot Agents? Do I need to shut down the
> Distribution Agent and/or Log Reader Agent first before I do anyone of these
> options?
> Also, will the tables in the publishing database that are being transferred
> over to the subscribing server be locked by SQL server during the transfer?
> wingman

Append @ExecutionTime to the Report File Name

I am trying to append the date to each snapshot of the report that is
deployed on a file share (e.g. RPT001 5-25-2005.xls).
I tried RPT001 @.ExecutionTime in the File Name of the subscription
definition but this deploys the report as: RPT001 @.ExecutionTime.xls
Anybody any ideas?
Thanks,
FlorinHi,
We would also like to do this - did you resolve?
Thanks,
--
Matt
"Florin" wrote:
> I am trying to append the date to each snapshot of the report that is
> deployed on a file share (e.g. RPT001 5-25-2005.xls).
> I tried RPT001 @.ExecutionTime in the File Name of the subscription
> definition but this deploys the report as: RPT001 @.ExecutionTime.xls
> Anybody any ideas?
> Thanks,
> Florin|||I have not figured how to do that.
"Matt" wrote:
> Hi,
> We would also like to do this - did you resolve?
> Thanks,
> --
> Matt
>
> "Florin" wrote:
> > I am trying to append the date to each snapshot of the report that is
> > deployed on a file share (e.g. RPT001 5-25-2005.xls).
> >
> > I tried RPT001 @.ExecutionTime in the File Name of the subscription
> > definition but this deploys the report as: RPT001 @.ExecutionTime.xls
> >
> > Anybody any ideas?
> >
> > Thanks,
> > Florin