Join Filters not filtering 
Author Message
 Join Filters not filtering

Hi, I've got a problem with a SQL Server 2000 replication publication - it's
a merge publication which is going to be subscribed to from Windows CE
machines.

The publication contains 5 tables;
tblCycles; filtered using GetDate() to return only the current cycle.
tblAuditBuilds; filtered using join filter where tblAuditBuilds.CycleID =
tblCycles.CycleID
tblUsers; filtered using sUser_sName() to return the correct user ID
tblReps; filtered using join filter where tblReps.UserID = tblUsers.UserID
tblAudits filtered using join filters tblAudits.RepID = tblReps.RepID AND
tblAudits.BuildID = tblAuditBuilds.BuildID

In theory, this means that the only records that are returned from tblAudits
are the ones for the current cycle and the current user, however, when I
perform the replication tblAudits is not being filtered at all, and all
records are returned.

What am I missing? Surely it must be possible to filter a table on two
condtions? How do I define the order in which the filtering occurs?

I hope you can help,
Regards,
Kenny Johnson

IRW Associates.
http://www.***.com/



Mon, 29 Dec 2003 21:54:15 GMT
 Join Filters not filtering

Hi, I've got a problem with a SQL Server 2000 replication publication - it's
a merge publication which is going to be subscribed to from Windows CE
machines.

The publication contains 5 tables;
tblCycles; filtered using GetDate() to return only the current cycle.
tblAuditBuilds; filtered using join filter where tblAuditBuilds.CycleID =
tblCycles.CycleID
tblUsers; filtered using sUser_sName() to return the correct user ID
tblReps; filtered using join filter where tblReps.UserID = tblUsers.UserID
tblAudits filtered using join filters tblAudits.RepID = tblReps.RepID AND
tblAudits.BuildID = tblAuditBuilds.BuildID

In theory, this means that the only records that are returned from tblAudits
are the ones for the current cycle and the current user, however, when I
perform the replication tblAudits is not being filtered at all, and all
records are returned.

What am I missing? Surely it must be possible to filter a table on two
condtions? How do I define the order in which the filtering occurs?

I hope you can help,
Regards,
Kenny Johnson

IRW Associates.
http://www.irw.co.uk/



Mon, 29 Dec 2003 21:55:06 GMT
 Join Filters not filtering
When you have two filters, the result set would be UNION of both the filters
rather than INTERSECTION.  This is how replication works.  What you are
expecting is filter on filter  which is INTERSECTION.  .

The only workaround that I can think of is to have a subselect in [audits]
which says
"audits.buildid=auditbuilds.buildid and auditbuilds.cycleid=cycles.cycleid
and cycles.date=date
and audits.repid=rep.repid and rep.userid=users.name and
users.name=suser_sname()"


Quote:
> Hi, I've got a problem with a SQL Server 2000 replication publication -
it's
> a merge publication which is going to be subscribed to from Windows CE
> machines.

> The publication contains 5 tables;
> tblCycles; filtered using GetDate() to return only the current cycle.
> tblAuditBuilds; filtered using join filter where tblAuditBuilds.CycleID =
> tblCycles.CycleID
> tblUsers; filtered using sUser_sName() to return the correct user ID
> tblReps; filtered using join filter where tblReps.UserID = tblUsers.UserID
> tblAudits filtered using join filters tblAudits.RepID = tblReps.RepID AND
> tblAudits.BuildID = tblAuditBuilds.BuildID

> In theory, this means that the only records that are returned from
tblAudits
> are the ones for the current cycle and the current user, however, when I
> perform the replication tblAudits is not being filtered at all, and all
> records are returned.

> What am I missing? Surely it must be possible to filter a table on two
> condtions? How do I define the order in which the filtering occurs?

> I hope you can help,
> Regards,
> Kenny Johnson

> IRW Associates.
> http://www.irw.co.uk/



Tue, 30 Dec 2003 05:20:30 GMT
 Join Filters not filtering
Thanks for your help - that seems to work [using WHERE AuditID IN (SELECT
AuditID FROM ...)], although I suspect there might be performance
implications. Still - better to have performance implications than try to
put a 36 Mb .sdf file onto a 32Mb handheld...

I'm unimpressed that there's no way to control how multiple filters act on
tables though - something for MS to think about implementing....


Quote:
> When you have two filters, the result set would be UNION of both the
filters
> rather than INTERSECTION.  This is how replication works.  What you are
> expecting is filter on filter  which is INTERSECTION.  .

> The only workaround that I can think of is to have a subselect in [audits]
> which says
> "audits.buildid=auditbuilds.buildid and auditbuilds.cycleid=cycles.cycleid
> and cycles.date=date
> and audits.repid=rep.repid and rep.userid=users.name and
> users.name=suser_sname()"



Tue, 30 Dec 2003 19:39:48 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Row Filter & Join Filter

2. Problem with filters and join filters in merge replication

3. joined dynamic filters VS dynamic filters [mergerepl]

4. To filter or not to filter???

5. Outer join filter from derived table not working

6. FIX: Dynamic Filtered Tables Do Not Replicate Data From Joined Tables to SQL Server CE Subscribers

7. How detect filter selected in filter axis?

8. How to filter many data in filter axis when using PiovtTable10

9. Filter Accross Date Range - Different Filter Value for Different Dates

10. Merge Replication with Dynamic Filter and Row Filter failure on two Deletes

11. Row filters don't filter enough

12. Dynamic filter vs static filter...


 
Powered by phpBB® Forum Software