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 :)

Article - delete all data property

Hi There

I think this is a pretty straight forward question , just want confirmation.
I am using snapshot replication to a subscriber, it is essentially a re-publisher, these same tables are replicated from the subcriber to another.

If i select the delete all data option on the snapshot articles will it truncate the table or delete all data , i am sure it is delete as it says so but i need to be 100% sure that it does not truncate, as the truncate command is not allowed on tables that are published for replication , as the ones on the re-publisher are.

ThanxOk i have subsequently found out it does truncate.

So i have a problem.

Server A has transactional replication to Server B.

I want to setup snapshot replication from Server C to Server A, some of the articles involved are also replicated from Server A to Server B.

So essentially i want it to snapshot new data from Server C to Server A, it must delete all data present , thereby the articles involved in transactional replication from Server A to Server B must be cleaned out and replicated with new data accordingly.

Hope this makes sense, and i really hope it is possible !

Thanx|||Ok it hink i have figured it out, i have choosen the article property to delete all data matching filtered rows, but i do not filter rows , so presume it simply does not have a where clause but it performs a delete obviously not a truncate.

If some one can just confirm this is he correct way to go about it i would appreciate it.

Thanx|||I havent tried this, but I would guess, you would be better off starting afresh.
First setup snapshot replication from C-->A.
The setup Tran replication from A-->B with only those articles that you need from A to B.

This would be cleaner and easy to troubleshoot. And since you want new data from A-->B anyways, I dont see any harm in removing replication between A-->B and setting it afresh after A is refreshed from C.|||This needs to be automated, and occur on a regular basis.
Deleting subscriptions and re-setting up is not an option needs to integrate into the entire replication structure, including production. So you suggestions are not an option for me unfortunately.

Thanx|||Hi Sean,

I tried your scenario and it seemed to work fine.

I setup Tran replication between A-->B with some data
Then I setup Snapshot replication C-->A and the article had delete all data matching filter criteria but there was no filter and everything seemed to work fine.

Any changes made at C did seem to propagate to A and then to B. However snapshot had to be run at C since it is a snapshot publication. And I had both push subscriptions from C to A and A to B.

Please do try out and let us know if you encounter any issues.|||Thanx Mahesh

I will be going ahead with this on 1 production environemnt today or tomorrow, will update you on the results.

Thank You

Are there any limitations on tables to be published for republishi

Hey. I have successfully setup Merge and transactional pubs on a serverA as
publisher and serverB as subscriber. Now, I want to partition the data in
ServerB and send it back to serverA in a different db. This is in testing. In
prod, it'll be going to a different server. The problem is that the tables
from the transactional publication can't be republished. Is this true or am i
doing something wrong? Can you guide me a bit through this? Thank you...
I'm sorry. I found the solution for the problem.