Merge replication row filter

I have a situation which I can't seem to find an answer to. I have
merge replication set up in SQL2005 and everything seems to work find
except that the row filter for a table isn't working 100%. I have 200
or so field guys who have SQL Express installed on their laptops who
syncronize data back to the server through web syncronization. The
filter in question looks like this:
SELECT <published columns> FROM [dbo].[Stores]
WHERE Division =
(SELECT DivID FROM Divisions
WHERE DistrictDivision = HOST_NAME())
The row filter works fine except when a store is moved from one
division to the next. While it shows up in the new division, it
remains in the old one. I have tested the select statements used in
the filter and they return act as expected. Has anyone seen anything
like this?
Thanks in advance
JC
Hi,
On SQL2005 there is a parameter to sp_addmergepublication called
@.allow_partition_realignment. It sounds like the option you need if
you want to remove a store from the old division. You may need to
consider realigning your current subscribers by forcing a
reinitialisation.
http://msdn2.microsoft.com/en-us/library/ms176103.aspx
Tim
On Mar 31, 6:07 pm, "JC" <jbzcoo...@.gmail.com> wrote:
> I have a situation which I can't seem to find an answer to. I have
> merge replication set up in SQL2005 and everything seems to work find
> except that the row filter for a table isn't working 100%. I have 200
> or so field guys who have SQL Express installed on their laptops who
> syncronize data back to the server through web syncronization. The
> filter in question looks like this:
> SELECT <published columns> FROM [dbo].[Stores]
> WHERE Division =
> (SELECT DivID FROM Divisions
> WHERE DistrictDivision = HOST_NAME())
> The row filter works fine except when a store is moved from one
> division to the next. While it shows up in the new division, it
> remains in the old one. I have tested the select statements used in
> the filter and they return act as expected. Has anyone seen anything
> like this?
> Thanks in advance
> JC

Merge replication new subscriber problem

Hi all,
i can't create new subscriptions for my publication since i made a
change to a view:
i used to have a view called vlAnag
i changed the view adding a reference (join) to a new view called
vlAnagValidi
then added the new view to the articles in my publication
since that moment when i try to synchronize i get the error
'the schema script ...\vlAnag_999.sch could not be propagated to the
subscriber'
it seems that he tries to create the view vlAnag *before* creating the
vlAnagValidi
in fact i can't find it in the new database
how can i change the order in which the merge process initializes the
schema?
or is there any workaround?
thanx in advance
lorenzo
The workaround on sql 2000 is to recreate the views and then sql server
should create the correct dependencied and therefore establish the required
replication order. Alternatively you could use sp_addscriptexec. In SQL
Server 2005 you can specify the article order and dependencies are
apparently more robust.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i can't delete the views:
error 3724 cannot drop the view ... because it is being used for
replication
of course
now i'm going to study the sp_addscriptexec...
see you
lorenzo
|||I would recommend removing all the views and any other programming objects
from your merge publication if you have to reinitialize at some point. In my
case they are part of a snapshot publication only, which gives me the
flexibility that I need. The sp_addscriptexec can be used but you have to
hand script the file. In your case this is probably ideal as you can
manually determine the replicaiton order of articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanx for your reply
i have to manage a 150 articles publication, therefore i'm afraid i
won't edit a manual script...
the (ugly but working) solution i'm taking is to launch the sync, wait
for it to stop for the error, launch a little script that creates the
view, and finally restart the sync
(no comments, please)
my subscribers are very few, so i can handle it :-)
ciao
lorenzo

Merge replication- How to drop article and than add it again

Hi All,
I need to drop an article from live merge publication and than add it back
again with different schema (column, PK etc’) . I can’t use
sp_dropsubscription for every subscriber and sp_droparticle because it is not
transactional / snapshot publication.
Do I have any other alternative other than reinitialize the whole
publication? .
Thanks,
Eyal
Unfortunately not in the case of merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There is more info here
http://msdn.microsoft.com/library/de...limpl_22pf.asp
about managing schema changes with merge replication.
Thanks
Nabila Lacey
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:05D80E20-9EC0-4A00-BF56-210C868F3617@.microsoft.com...
> Hi All,
> I need to drop an article from live merge publication and than add it
back
> again with different schema (column, PK etc') . I can't use
> sp_dropsubscription for every subscriber and sp_droparticle because it is
not
> transactional / snapshot publication.
> Do I have any other alternative other than reinitialize the whole
> publication? .
>
> Thanks,
> Eyal

Merge replication and conflict tables.

If you are making schema changes you should be using sp_repladdcolumn, and
sp_repldropcolumn.
You can't delete the conflict tables using EM, but you can using
sp_droptable through ISQLW
You really don't have to delete these tables. If you are creating a new
publication SQL Server will detect these existing tables and then create new
ones based on the existing name but incrementing by 1 letter.
So conflict_pubs2_authors would be the first table, aonflict_pubs2_pubs the
second, bonflict_pubs2_pubs the third, donflict_pubs2_pubs the fourth, etc.
"mary" <mary@.dbagua.com> wrote in message
news:eN3enz0FEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi, I configured a merge replication with pull subscription. I need to
> do some changes on the table structure so, i delete the replication, do
> the changes and configure the replication again.
> I saw that every time the publication is configured it creates some
> conflict tables but dont delete the conflict tables that the first
> publication used.
> I try to delete this tables but i cant because are system tables.
> Do you know if there is a way that i can delete this tables?
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
I was having trouble adding column to tables that are=20
being replicated using EM and I get a message saying=20
that "Cannot alter column ... because it is 'REPLICATED'.=20
I was directed to the following knowledge base article=20
listed below to correct the problem. Will using=20
sp_repladdcolumn and sp_repldropcolumn prevent this?
http://support.microsoft.com/default.aspx?scid=3DKB;EN-
US;811899

>--Original Message--
>If you are making schema changes you should be using=20
sp_repladdcolumn, and
>sp_repldropcolumn.
>You can't delete the conflict tables using EM, but you=20
can using
>sp_droptable through ISQLW
>You really don't have to delete these tables. If you are=20
creating a new
>publication SQL Server will detect these existing tables=20
and then create new
>ones based on the existing name but incrementing by 1=20
letter.
>So conflict_pubs2_authors would be the first table,=20
aonflict_pubs2_pubs the
>second, bonflict_pubs2_pubs the third,=20
donflict_pubs2_pubs the fourth, etc.
>
>"mary" <mary@.dbagua.com> wrote in message
>news:eN3enz0FEHA.2876@.TK2MSFTNGP09.phx.gbl...
subscription. I need to
the replication, do
creates some
that the first
system tables.
tables?
http://www.codecomments.com ***
>
>.
>
|||Hi Emma,
Yes. Using sp_repladdcolumn and sp_repldropcolumn would solve your problem while changing the schema for any table.
Regards,
Karthik.