Dynamic and Static

Is it possible to have a static and dynamic filter in merge replication?
I've got a publication that goes out to about 80 subscribers. I'm using
HOST_NAME() to filter by subscriber and it works great. As we've moved on,
its been decided that we only need the past three months of data at the
subscriber. I tried to change the filter to something like:
WHERE fdLocation = HOST_NAME() AND fdTransDate > DATEADD(day, -78,
GETDATE())
But when I try to replicate, no data at all moves. Is something like this
possible?
Thanks,
Scott
are you using this on a pull subscription? if you are using it on a push
subscription host_name() evaluates to the Publisher or the computer you are
connected to EM on. You can get around this by using the -hostname parameter
in your agent properties.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Scott" <john@.doe.com> wrote in message
news:%23QmyctYTEHA.2372@.TK2MSFTNGP11.phx.gbl...
> Is it possible to have a static and dynamic filter in merge replication?
> I've got a publication that goes out to about 80 subscribers. I'm using
> HOST_NAME() to filter by subscriber and it works great. As we've moved
on,
> its been decided that we only need the past three months of data at the
> subscriber. I tried to change the filter to something like:
> WHERE fdLocation = HOST_NAME() AND fdTransDate > DATEADD(day, -78,
> GETDATE())
> But when I try to replicate, no data at all moves. Is something like this
> possible?
> Thanks,
> Scott
>
|||This is a pull subscription. To clarify, if I just use the HOST_NAME(),
things work fine. What I want to do is filter by HOST_NAME() and a date
range. Is this possible?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ZDNfZcTEHA.1768@.TK2MSFTNGP10.phx.gbl...
> are you using this on a pull subscription? if you are using it on a push
> subscription host_name() evaluates to the Publisher or the computer you
are
> connected to EM on. You can get around this by using the -hostname
parameter[vbcol=seagreen]
> in your agent properties.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Scott" <john@.doe.com> wrote in message
> news:%23QmyctYTEHA.2372@.TK2MSFTNGP11.phx.gbl...
> on,
this
>
|||Scott, did you have any luck in finding a solution to your question.
I have a similar requirement ie. trying to use a merge dynamic filter to age off transactions more than 7 days old from subscriber databases
eg.
(isnull(datediff ( day , EVENT_DATE , getdate() ),0) <= 7)
and (SITE_NO in (select SITE_NO from SM_SITE where
SUBSCRIBER_LOGINNAME = suser_sname()))
The above works fine when creating the initial snapshot & dynamic snapshot however the merge agent does not then progressively age off the transactions.
Mark