Dynamic Filter during creating New Publication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Dynamic Filter during creating New Publication

Hi Friends, I have a central Main Database(say MD) and 2 Debots-servers(say DP1 And DP2).Now my main Database has a single Table with 4 rows with a Field named say PlantCode having 1100 and 1200 values 2 rows each.
I need to create a Publication such that my Subscription for DP1 And DP2 will be Pushed from MD(Main Database).Hence I am using Push Subscription.
I want to create the Initial SnapShot using Dynamic Filter such that for Subscription in DP1 the rows with PlantCode = 1100 should go to DP1 and for DP2 PlantCode = 1200 should.Same thing should happen during the time of Synchronisation.
Now since I will be using My Main Database Machine I cannot use HOST_NAME to segregate the Subscriptions nor can I use suser_name since it does not matter for our case.
I don’t want to setup manually 2 different publications with static filters, and more may come in the future.
This is My Requirement.
Please guide me how I can use Dynamic Filters with user defined functions that can tell me which Subscription should get which Data. Thanks for your help, Best regards
I haven’t tried this but I see no reason for it not to work. On you filter you could use a case statement. So The were clause would be: PlantCode = CASE WHEN @@SERVERNAME = DP1 THEN 1100 ELSE 2200 END
If you can’t use @@SERVERNAME because they’re the same or somthing strange you maybe able to DB_NAME().
Thanx for ur response,
But I am not sure whether @@servername will give me the name of the subscriber server since I am pushing my subscriptions from my Publisher which in that case will be same for both my Depots(Subscriptions). since I am new to replication I will appreciate more of your inputs.
When using dynamic filters, the filtering logic expression is evaluated within the context of the merge connection to the Publisher, not the connection to the Subscriber. If the merge process uses the SQL Server 2000 login Janet Leverling to connect to the Publisher, and the sa login to connect to the Subscriber, the SUSER_SNAME() function will evaluate to user name Leverling in the filtering logic Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.