Merge replication problem

I have one corporate server that acts as both publisher and distributor.
Remote locations all have SQL server loaded and act as subscribers. This
setup has worked great until yesterday. Somehow, and it is still under
investigation, the corporate server "lost" all subscriptions and became
disconnected from the remote locations.
Each location is on a 5 minute schedule for the merge agent to run.
After recreating all the subscriptions and "pushing" them down to the remote
locations, there has been information lost during the down time. The
snapshot agent had to be run in order to recreate the subscriptions. I am
trying to figure out how I could have reconfigured the setup so that when
the subscription was made all the data in the remote locations would have
been preserved and "Merged" with the data from the server.
Thank you in advance for you input.
WB
Should this situation happen again I suggest:
1. Prevent further updates on remote stations until replication is re-setup
OR choose a time for the resync that least interferes with operations - the
longer you leave it the worse your problem becomes
2. Remove publication(s) and subscriptions
3. Use a tool like Red Gates "Data Compare" to see what data the publisher
is missing - it will generate the sql inserts for you, this sql may need to
be manually tweaked
4. Apply update scripts
5. Recreate publication(s) and subscriptions
6. Find the person who caused the problem and apply thumbscrews
Jim.
"WB" wrote:

> I have one corporate server that acts as both publisher and distributor.
> Remote locations all have SQL server loaded and act as subscribers. This
> setup has worked great until yesterday. Somehow, and it is still under
> investigation, the corporate server "lost" all subscriptions and became
> disconnected from the remote locations.
> Each location is on a 5 minute schedule for the merge agent to run.
> After recreating all the subscriptions and "pushing" them down to the remote
> locations, there has been information lost during the down time. The
> snapshot agent had to be run in order to recreate the subscriptions. I am
> trying to figure out how I could have reconfigured the setup so that when
> the subscription was made all the data in the remote locations would have
> been preserved and "Merged" with the data from the server.
> Thank you in advance for you input.
> WB
>
>
|||I don't know how successful I will be at applying the thumbscrews to myself,
as it appears to have happened on my watch. It appears that while trying to
create a new remote server (from the remote location) and then send down the
snapshot and merged replication data, that the SQL server at the main office
removed the subscriptions of all the remote locations. Not sure how the
subscriptions were removed or why SQL thought it needed to do that, but I
may never know....
I will definitely look into the data compare tool; that will probably come
in handy in the future.
"Jim Breffni" <JimBreffni@.discussions.microsoft.com> wrote in message
news:C3E8DA76-F717-4353-8E0B-FACDA058F3DB@.microsoft.com...
> Should this situation happen again I suggest:
> 1. Prevent further updates on remote stations until replication is
re-setup
> OR choose a time for the resync that least interferes with operations -
the
> longer you leave it the worse your problem becomes
> 2. Remove publication(s) and subscriptions
> 3. Use a tool like Red Gates "Data Compare" to see what data the
publisher
> is missing - it will generate the sql inserts for you, this sql may need
to[vbcol=seagreen]
> be manually tweaked
> 4. Apply update scripts
> 5. Recreate publication(s) and subscriptions
> 6. Find the person who caused the problem and apply thumbscrews
>
> Jim.
>
> "WB" wrote:
This[vbcol=seagreen]
remote[vbcol=seagreen]
am[vbcol=seagreen]
when[vbcol=seagreen]
have[vbcol=seagreen]

Merge Replication not merging updates made to subscriber

Hope someone can help with this. I've implemented merge replication and when
I make an update to the server that acts as the distributor the update is
reflected on the subscribers. However when I make a change on the subscriber
I cannot see it on the other subscribers or on the distributor. Any help
regarding this would be appreciated.
Thank you,
Abdul Rauf
Abdul,
how are you making the change on the subscriber? If it is a bulk insert
without firing the triggers, it won't replicate to the publisher. In some
rare other cases this also happens (eg when the filter is set to 1=2 and you
are adding records onto the subscriber. If there is no coresponding record
in MSmerge_contents try using sp_addtabletocontents to include the rows then
resynchronise. Alternatively you can use sp_mergedummyupdate for a single
row.
HTH,
Paul Ibison (SQL Server MVP)
[vbcol=seagreen]
|||Paul, I'm new to replication so I will do more research on what you have
below. I'm testing this scenario so I'm just going into the enterprise
manager of the subscriber database and updating a row in the Pubs database.
Updating a row in the Distributor database works but not in the Subscriber
through enterprise manager.
"Paul Ibison" wrote:

> Abdul,
> how are you making the change on the subscriber? If it is a bulk insert
> without firing the triggers, it won't replicate to the publisher. In some
> rare other cases this also happens (eg when the filter is set to 1=2 and you
> are adding records onto the subscriber. If there is no coresponding record
> in MSmerge_contents try using sp_addtabletocontents to include the rows then
> resynchronise. Alternatively you can use sp_mergedummyupdate for a single
> row.
> HTH,
> Paul Ibison (SQL Server MVP)
>
>

Merge replication failure

Hello,
I have an SQL2000 server, which acts as the distributor and as the
publisher. The clients are Pocket PC's, using SQL Server CE. I have the
latest SP for both. The replication is a merge replication, with a lot of
join filters, more than 100 articles (only tables, no views or procedures
included). The average size of the database on the clients is about ~7MB.
Sometimes, following a massive delete on several tables (delete from table,
than insert into table select from sthing), I have got the following error
message during replication:
1. "a call to the sql server reconciler has failed"
2. "failed to enumerate changes"
3. "select permission denied on column <pk> of object MS_<long-long guid>"
The error occurs in the stored procedure sp_MSsetupbelongs.
Actually, the message is right, the user has no rights to the referred
table. But. This is a generated table, created by the replication itself (I
think this is the table used for deletion). After I've added the rights, the
error disapperared. Reinitialize also helps.
Do I have to do this all for the tables in the publication? I could not do
this, because of the generated table names.
Or this is a bug?
Thanks for replies,
Tamas Beri
Funny, but I can't find anything better solution than this one:
select
'grant select on '+sysobjects.name+' to sales;'
from
sysobjects
where
name like 'MS_bi%_v_%'
order by
sysobjects.name;
After you ran this generated script, the whole problem described down there
disappears.
Do You have any information about the Service Pack 4? I have a lot of
problems with sql server ce and merge replication.
Regards,
Tamas Beri
"Beri Tamas" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
news:uYP%23YL89EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have an SQL2000 server, which acts as the distributor and as the
> publisher. The clients are Pocket PC's, using SQL Server CE. I have the
> latest SP for both. The replication is a merge replication, with a lot of
> join filters, more than 100 articles (only tables, no views or procedures
> included). The average size of the database on the clients is about ~7MB.
> Sometimes, following a massive delete on several tables (delete from
table,
> than insert into table select from sthing), I have got the following error
> message during replication:
> 1. "a call to the sql server reconciler has failed"
> 2. "failed to enumerate changes"
> 3. "select permission denied on column <pk> of object MS_<long-long guid>"
> The error occurs in the stored procedure sp_MSsetupbelongs.
> Actually, the message is right, the user has no rights to the referred
> table. But. This is a generated table, created by the replication itself
(I
> think this is the table used for deletion). After I've added the rights,
the
> error disapperared. Reinitialize also helps.
> Do I have to do this all for the tables in the publication? I could not do
> this, because of the generated table names.
> Or this is a bug?
> Thanks for replies,
> Tamas Beri
|||Hi Beri,
Replication shouldn't require you to issue the explicit select on this
particular object. Can you please provide some more info so that we can try
to create the repro in our test environment?
1. What kind of permission have you provided to publisher and distributor
login?
2. What's the type of object 'MS_bi%_v_%' - view, SP, etc?
3. Can you please provide the content of following tables - sysmergearticles
& sysmergepublications?
4. In case you are okay - can you post your scripts which you used to setup
your replication?
thanks - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Beri Tamas" <gfoyle@.freemail.hu> wrote in message
news:Ocybvt%239EHA.2984@.TK2MSFTNGP09.phx.gbl...
> Funny, but I can't find anything better solution than this one:
> select
> 'grant select on '+sysobjects.name+' to sales;'
> from
> sysobjects
> where
> name like 'MS_bi%_v_%'
> order by
> sysobjects.name;
> After you ran this generated script, the whole problem described down
there[vbcol=seagreen]
> disappears.
> Do You have any information about the Service Pack 4? I have a lot of
> problems with sql server ce and merge replication.
> Regards,
> Tamas Beri
> "Beri Tamas" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
> news:uYP%23YL89EHA.2984@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
procedures[vbcol=seagreen]
~7MB.[vbcol=seagreen]
> table,
error[vbcol=seagreen]
guid>"[vbcol=seagreen]
> (I
> the
do
>
|||Same error here, but I run the script and still get it.
Any more on this one?