Merge replication with MSSQL/SQLCE using row filters

Hello everyone,
I'm new in replication, I have some - maybe trivial - question.
First of all, I'm using a MSSQL server as a publisher, the clients are
Pocket PC's, running SQLCE. All clients need only a subset of data, which
can be controlled via row filters, using SUSER_SNAME(). For the first
replication, it seems everything is correct, but.
I have 2 tables in the publication with a reference, eg. table X with
fields code,data and table Y with fields xcode,username. The row filters
looks like:
for table X: 'WHERE X.code IN (SELECT Y.xcode FROM Y WHERE Y.xcode=X.code
AND Y.username=SUSER_SNAME())'
for table Y: 'WHERE Y.username=SUSER_SNAME()'
If I add a row to table Y, the referrenced row from table X will not be
transferred through the next merge replication process, causing referential
integrity error. If I reinitialize the publication, it works again. It
seems, the expressions in the row filters has no effect after the first run
of the Snapshot Agent. I've tried JOIN FILTERS, the effect was that the join
filters worked _only_, not the row filters.
Any idea? The dynamic filters option is on. Am I need to create a dynamic
snapshot job for every single client? There are lot's of them .
Thanks for any help,
Nomad
Correct me if I am wrong, but I don't think that you can use SUSER_Sname()
on your pocket PC.
When I issue this command on isqlw on my pocket pc I get an: At least one
input table is required.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Nomad" <gfoyle@.freemail.hu> wrote in message
news:OGlIubKnEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Hello everyone,
> I'm new in replication, I have some - maybe trivial - question.
> First of all, I'm using a MSSQL server as a publisher, the clients are
> Pocket PC's, running SQLCE. All clients need only a subset of data, which
> can be controlled via row filters, using SUSER_SNAME(). For the first
> replication, it seems everything is correct, but.
> I have 2 tables in the publication with a reference, eg. table X with
> fields code,data and table Y with fields xcode,username. The row filters
> looks like:
> for table X: 'WHERE X.code IN (SELECT Y.xcode FROM Y WHERE Y.xcode=X.code
> AND Y.username=SUSER_SNAME())'
> for table Y: 'WHERE Y.username=SUSER_SNAME()'
> If I add a row to table Y, the referrenced row from table X will not be
> transferred through the next merge replication process, causing
referential
> integrity error. If I reinitialize the publication, it works again. It
> seems, the expressions in the row filters has no effect after the first
run
> of the Snapshot Agent. I've tried JOIN FILTERS, the effect was that the
join
> filters worked _only_, not the row filters.
> Any idea? The dynamic filters option is on. Am I need to create a dynamic
> snapshot job for every single client? There are lot's of them .
> Thanks for any help,
> Nomad
>
|||I'm using the SUSER_SNAME function at the publication row/join filters, in a
publisher database. The publisher is a PC running MSSQL, the clients are
Pocket PC's using SQLCE. It seems, the function itself working correctly,
for the first time, I've found not the solution, but the description of this
problem, here:
http://support.microsoft.com/default...n-us%3Bq324362
So this is not a bug, it's a feature .
"Hilary Cotter" <hilary.cotter@.gmail.com> az albbiakat rta a kvetkezo
zenetben news:O4l1BwLnEHA.648@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Correct me if I am wrong, but I don't think that you can use SUSER_Sname()
> on your pocket PC.
> When I issue this command on isqlw on my pocket pc I get an: At least one
> input table is required.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Nomad" <gfoyle@.freemail.hu> wrote in message
> news:OGlIubKnEHA.3708@.TK2MSFTNGP10.phx.gbl...
which[vbcol=seagreen]
Y.xcode=X.code[vbcol=seagreen]
> referential
> run
> join
dynamic
>
|||As the artice down below says, this kind of replication process (e.g. using
subqueries referencing other tables in a row filter) could not be executed
using row filters, because row filters won't be evaluated after the first
run of the snaphot agent. This was disappointing .
Now I have a question about JOIN FILTER-s. The behavoiur of the join filters
"cumulative" (or "transitive")? For example, I have a row filter for one
table, and a join filter for another - joined to the first table. So, I will
have only the joined rows from the second table after replication. Am I
right? Can I use row and join filters in cooperation?
"Nomad" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
news:%23dAuB6LnEHA.2612@.TK2MSFTNGP15.phx.gbl...
> I'm using the SUSER_SNAME function at the publication row/join filters, in
a
> publisher database. The publisher is a PC running MSSQL, the clients are
> Pocket PC's using SQLCE. It seems, the function itself working correctly,
> for the first time, I've found not the solution, but the description of
this
> problem, here:
> http://support.microsoft.com/default...n-us%3Bq324362
> So this is not a bug, it's a feature .
|||Yes subset filters and join filters are intended to be used together. Yes
you will only get rows in the join filter table that meet the subset filter
in the parent table.
Philip Vaughn
Program Manager -SQL Server Replication
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
"Nomad" <gfoyle@.freemail.hu> wrote in message
news:OA7wcVnnEHA.4084@.TK2MSFTNGP10.phx.gbl...
> As the artice down below says, this kind of replication process (e.g.
> using
> subqueries referencing other tables in a row filter) could not be executed
> using row filters, because row filters won't be evaluated after the first
> run of the snaphot agent. This was disappointing .
> Now I have a question about JOIN FILTER-s. The behavoiur of the join
> filters
> "cumulative" (or "transitive")? For example, I have a row filter for one
> table, and a join filter for another - joined to the first table. So, I
> will
> have only the joined rows from the second table after replication. Am I
> right? Can I use row and join filters in cooperation?
> "Nomad" <gfoyle@.freemail.hu> az albbiakat rta a kvetkezo zenetben
> news:%23dAuB6LnEHA.2612@.TK2MSFTNGP15.phx.gbl...
> a
> this
>

Merge Replication Problem

Hello
I have merge Replication MSSQL Server 2000 SP4, One Publisher and 5
subscribers,
a) Subscribers1 has inserted the record at 8 AM and Send it to
subscribers2 & subscribers3
b) Using DTS, but not insert directly subscribers2 DB its Inserted
TempDB to subscribers2 DB using Store procedure.
c) Subscribers2 users update same records at 3 PM. It means now other
Subscribers not update those records.
d) I changed my article
@.property='compensate_for_errors',@.value='false'.
e) I have facing some conflict Problems
f) After Synchronization those records was deleted at some
Subscriber
g) We are Synchronization at 1 AM. For Schedule bases
Here the Question.
Why the Data going to deleted?
Which data going to Synchronies in above case?
Suppose I update row 1 following time
Subscribers1 at 6 am
Subscribers1 at 7 am
Subscribers1 at 8 am
Subscribers1 at 9 am
Subscribers1 at 11 am
Which data going to Synchronies in above case?
Please Explaning Clearly
Thanks
Sam
Sam I am having problems understanding the scenario you present. Before I
start guessing at what happened I want to explain a couple of things.
a) compensate_for_errors - this switch means that if a conflict occurs the
data will not be wiped out on the conflict loser. For example if a row with
the same PK value is simultaneously inserted on the publisher and subscriber
and the compensate_for_errors switch is set to true (true is default for SQL
2000, false is the default for SQL 2005) the row on the subscriber will be
deleted and replaced with the row from the publisher. If the
compensate_for_errors setting is set to false, the row on the subscriber
will not be deleted - in other words - the subscribers row will remain.
With this setting all such compensating actions will not occur.
b) when you use DTS with the defaults the rows dts'd into the table are not
logged and will not be replicated. Consult
http://support.microsoft.com/kb/275680/en-us for more information.
c) exactly what do you mean by "Using DTS, but not insert directly
subscribers2 DB its Inserted TempDB to subscribers2 DB using Store
procedure." If you mean it was DTS'd to tempdb and then from tempdb to the
subscriber2 db, this should have been logged and any inserts or other DML as
a result of this action should be replicated.
d) did you run conflict viewer for a window into what is happening?
It sounds like what happened what that in step b the rows were not logged
and hence never made it to subscriber 1 and subscriber 3. I don't understand
what happened in f). The conflict viewer should have a record of what
happened.
To answer your questions - data should not be deleted with the
compensate_for_errors setting set to false - only true.
From what you describe in the below scenario, if you are updating the same
row the update should flow with each synchronization unless you are doing
column level tracking - this would be logged as a conflict and the conflict
winner should win the conflict - ie its data should make it to all the
subscribers.
If the sync doesn't happen each hour, only the final result will travel. For
example if the sync runs at 11 am, only the final 11 am change will move.
HTH
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Sam" <shankar@.cdscom.co.th> wrote in message
news:Os7L2bEOGHA.812@.TK2MSFTNGP10.phx.gbl...
> Hello
> I have merge Replication MSSQL Server 2000 SP4, One Publisher and 5
> subscribers,
> a) Subscribers1 has inserted the record at 8 AM and Send it to
> subscribers2 & subscribers3
> b) Using DTS, but not insert directly subscribers2 DB its Inserted
> TempDB to subscribers2 DB using Store procedure.
> c) Subscribers2 users update same records at 3 PM. It means now other
> Subscribers not update those records.
> d) I changed my article
> @.property='compensate_for_errors',@.value='false'.
> e) I have facing some conflict Problems
> f) After Synchronization those records was deleted at some
> Subscriber
> g) We are Synchronization at 1 AM. For Schedule bases
>
> Here the Question.
> Why the Data going to deleted?
> Which data going to Synchronies in above case?
>
> Suppose I update row 1 following time
> Subscribers1 at 6 am
> Subscribers1 at 7 am
> Subscribers1 at 8 am
> Subscribers1 at 9 am
> Subscribers1 at 11 am
> Which data going to Synchronies in above case?
>
> Please Explaning Clearly
> Thanks
> Sam
>
>
>
>
>
>
>
|||THANKS Hilary
I need to test , let Contact You.
Sam
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:erQqQoHOGHA.2992@.tk2msftngp13.phx.gbl...
> Sam I am having problems understanding the scenario you present. Before I
> start guessing at what happened I want to explain a couple of things.
> a) compensate_for_errors - this switch means that if a conflict occurs the
> data will not be wiped out on the conflict loser. For example if a row
> with the same PK value is simultaneously inserted on the publisher and
> subscriber and the compensate_for_errors switch is set to true (true is
> default for SQL 2000, false is the default for SQL 2005) the row on the
> subscriber will be deleted and replaced with the row from the publisher.
> If the compensate_for_errors setting is set to false, the row on the
> subscriber will not be deleted - in other words - the subscribers row
> will remain. With this setting all such compensating actions will not
> occur.
> b) when you use DTS with the defaults the rows dts'd into the table are
> not logged and will not be replicated. Consult
> http://support.microsoft.com/kb/275680/en-us for more information.
> c) exactly what do you mean by "Using DTS, but not insert directly
> subscribers2 DB its Inserted TempDB to subscribers2 DB using Store
> procedure." If you mean it was DTS'd to tempdb and then from tempdb to the
> subscriber2 db, this should have been logged and any inserts or other DML
> as a result of this action should be replicated.
> d) did you run conflict viewer for a window into what is happening?
> It sounds like what happened what that in step b the rows were not logged
> and hence never made it to subscriber 1 and subscriber 3. I don't
> understand what happened in f). The conflict viewer should have a record
> of what happened.
> To answer your questions - data should not be deleted with the
> compensate_for_errors setting set to false - only true.
> From what you describe in the below scenario, if you are updating the same
> row the update should flow with each synchronization unless you are doing
> column level tracking - this would be logged as a conflict and the
> conflict winner should win the conflict - ie its data should make it to
> all the subscribers.
> If the sync doesn't happen each hour, only the final result will travel.
> For example if the sync runs at 11 am, only the final 11 am change will
> move.
> HTH
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Sam" <shankar@.cdscom.co.th> wrote in message
> news:Os7L2bEOGHA.812@.TK2MSFTNGP10.phx.gbl...
>

Merge replication and Domain Controller

Hi,
A few questions on merge replication and domain controller. We are using MS
SQL 2005 and Windows Server 2003.
1. Is domain controller a must for merge replication? Does performing
replication using Integrated Windows Authentication essentially has the same
effect as with a domain controller?
2. If merge replication is to be done with a domain controller, which method
of authentication, SQL Server Authentication or Integrated Windows
Authentication, can/should be used for replication? What are the advantages
of one over the other, if any?
3. I understand that MS does not recommend running SQL Server 2005 on a
domain controller. Having said that, this would mean we need to separate the
two. But we have the constraint of having only one machine. Is there any
workaround for this, that is, performing merge replication and achieving the
function of a domain controller but with only one machine?
My apologies if I am not making sense as I am not too familiar with these.
Would appreciate any help on this. Thanks in advance.
http://www.zetainteractive.com - Shift Happens!
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
"AimlessZombie" <AimlessZombie@.discussions.microsoft.com> wrote in message
news:09DB05F1-288D-4489-BFEF-2CB09FAF7E8B@.microsoft.com...
> Hi,
> A few questions on merge replication and domain controller. We are using
> MS
> SQL 2005 and Windows Server 2003.
> 1. Is domain controller a must for merge replication? Does performing
> replication using Integrated Windows Authentication essentially has the
> same
> effect as with a domain controller?
> 2. If merge replication is to be done with a domain controller, which
> method
> of authentication, SQL Server Authentication or Integrated Windows
> Authentication, can/should be used for replication? What are the
> advantages
> of one over the other, if any?
> 3. I understand that MS does not recommend running SQL Server 2005 on a
> domain controller. Having said that, this would mean we need to separate
> the
> two. But we have the constraint of having only one machine. Is there any
> workaround for this, that is, performing merge replication and achieving
> the
> function of a domain controller but with only one machine?
> My apologies if I am not making sense as I am not too familiar with these.
> Would appreciate any help on this. Thanks in advance.
|||Dear Aimless zombie.
1) No, you can use local machine accounts with pass through authentication
or use sql authentication.
2) you can use either sql authentication or integrated authentication. SQL
Authentication is intended for replicating between untrusted domains or
heterogeneous applications. Integrated authentication is more secure.
3) You don't need a domain controller.
http://www.zetainteractive.com - Shift Happens!
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
"AimlessZombie" <AimlessZombie@.discussions.microsoft.com> wrote in message
news:09DB05F1-288D-4489-BFEF-2CB09FAF7E8B@.microsoft.com...
> Hi,
> A few questions on merge replication and domain controller. We are using
> MS
> SQL 2005 and Windows Server 2003.
> 1. Is domain controller a must for merge replication? Does performing
> replication using Integrated Windows Authentication essentially has the
> same
> effect as with a domain controller?
> 2. If merge replication is to be done with a domain controller, which
> method
> of authentication, SQL Server Authentication or Integrated Windows
> Authentication, can/should be used for replication? What are the
> advantages
> of one over the other, if any?
> 3. I understand that MS does not recommend running SQL Server 2005 on a
> domain controller. Having said that, this would mean we need to separate
> the
> two. But we have the constraint of having only one machine. Is there any
> workaround for this, that is, performing merge replication and achieving
> the
> function of a domain controller but with only one machine?
> My apologies if I am not making sense as I am not too familiar with these.
> Would appreciate any help on this. Thanks in advance.
|||Hi Hilary,
Thanks for the prompt help. Just a couple more questions with respect to
your replies.
1) Can either pass through authentication or use sql authentication achieve
the role of a domain controller? Or is there no basis for comparison at all
in the first place?
3) What if I must have a domain controller (part of requirement) but yet
only one machine available? Is there no workaround to my constraint to have
both domain controller and replication?
Thanks.
"Hilary Cotter" wrote:

> Dear Aimless zombie.
> 1) No, you can use local machine accounts with pass through authentication
> or use sql authentication.
> 2) you can use either sql authentication or integrated authentication. SQL
> Authentication is intended for replicating between untrusted domains or
> heterogeneous applications. Integrated authentication is more secure.
> 3) You don't need a domain controller.
>
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "AimlessZombie" <AimlessZombie@.discussions.microsoft.com> wrote in message
> news:09DB05F1-288D-4489-BFEF-2CB09FAF7E8B@.microsoft.com...
>
>
|||Dear Aimless Zombie
1) Can either pass through authentication or use sql authentication achieve
the role of a domain controller? Or is there no basis for comparison at all
in the first place?
No a domain controller is primarily used to authenticate users. You will not
be able to add local machine accounts to a domain controller, so you will
need to add domain accounts. You can still use pass through authentication
as long as the subscriber has a local machine account with the same name and
password. It is not recommended that you install SQL Server on a domain
controller as if SQL Server is compromised your entire domain will be.
3) What if I must have a domain controller (part of requirement) but yet
only one machine available? Is there no workaround to my constraint to have
both domain controller and replication?
You can install SQL Server on your domain controller but it is considered to
be a security risk.
I am not sure what you are doing, but I think you will make life simpler for
yourself if you use push subscriptions.
http://www.zetainteractive.com - Shift Happens!
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
"AimlessZombie" <AimlessZombie@.discussions.microsoft.com> wrote in message
news:76DC30CD-7E60-4EEC-8736-3661788DDCDC@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks for the prompt help. Just a couple more questions with respect to
> your replies.
> 1) Can either pass through authentication or use sql authentication
> achieve
> the role of a domain controller? Or is there no basis for comparison at
> all
> in the first place?
> 3) What if I must have a domain controller (part of requirement) but yet
> only one machine available? Is there no workaround to my constraint to
> have
> both domain controller and replication?
> Thanks.
> "Hilary Cotter" wrote: