article row filter - 2 parameters

hello,

i need to filter an article based on a user-supplied datetime filter (the datetime parameter is specified by the subscriber just before replication). at the same time i need to filter again by user (different subscribers get different rows).

i already did the user-based filter using HOST_NAME( ). but the difficulty here (al least i think so) lies in passing 2 parameters to the filter. i cannot rely on using SUSER_SNAME to pass the user filter, because no one will want to create 500 user accounts. so i guess the only solution here is to pass both parameters using only HOST_NAME( ) and then write 2 splitting functions which uses HOST_NAME( ) as its parameter. am i right ?

publisher/distributor is sql server 2005, all subscribers use sql mobile.

TIA, kamil nowicki

Using datetime would not be recommended since it is not deterministic.

For eg:

lets say you have a filter to get rows being touched only in the last 7 days.

Initially you get all rows.

After 7 days having not touched any rows, you would expect the merge agent deletes the 7 rows which will not be the case.

I would advise you to use SUSER_SNAME() or HOST_NAME() to get specific rows and in addition use another column 'status' or something that you can set/reset according to your business needs.

|||

thanks for replying.

i think that i have to use a datetime filter because the parameter to this filter has to be dynamic (worst case scenario: each subscriber uses different filter parameter for each of his replication sessions). is there another way to accomplish this ?

also, is there a way to store something in SUSER_SNAME() like using SqlCeReplication.HostName to store something into HOSTNAME() ?

|||

If each session of the subscriber uses a different filter, you will get an error with mismatched partitions. You would need to reinitialize the subscriber in that case. Are you ready to reinitialize the subscribers for every sync?

But note that as I mentioned previously, you will not be able to rely on datetime filter. Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

|||

>> Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

but that would mean that i have to use a "fixed filter" (same "replicate from ..." date for every subscriber), wouldn't it ?

|||How about: login=SUSER_SNAME and status='Y'|||

We have a best practice article for time-based filtering, you may want to reference it, it's what Mahesh is talking about:

Best Practices for Time-Based Row Filters

http://msdn2.microsoft.com/en-us/library/ms365153.aspx

|||

and how does that solve my scenario ? i want each subscriber to be able to choose a date ( "replicate from..." ) before each of his replication sessions. SP on the server will not know those dates when executed, so how am i supposed to update the "status" column ?

thanks for the link, i have read that before starting the thread.

|||

Are you saying that every time the subscriber syncs, it sends a new date and expects only releant rows? You cannot achieve this using the non-deterministic filter functions. If you are ready to reinitialize your subscriptions every time you sync, you may do that. Do a reinit on the publisher/subscriber, then send in the appropriate date as the hostname to get the relevant rows. However you wont be able to upload in this session because your filters may not match data that you want to upload.

|||

i took a different approach and now everything is working as it should. i wrote a SP on the backend server which updates every row in the filtered article for a given user (SP is parametrized with @.user nvarchar and @.filter datetime), changing rep_status tinyint column. the SP is called from subscriber on the distributor just before each replication session, so now the SP has all the info to update the filtered article (@.user and @.filter). article is filtered by rep_status.

the drawback is that the backend has to be put out of LAN to the internet (public IP), but later i will write a WebService so that all the data will pass through IIS and then the backend will be again NATed.

edit: and of course this works without subscription reinitialization :)