I want to dynamically filter my published data to many subscribers based
on a number contained within the server name of the subscriber. ie one
subscriber server is called 8364-Server and I want to use the 8364 as
the "club code" to extract the subset from the publisher. I preferably
want to use push subscriptions.
Using host_name just seems to return the name of the publisher which is
no good. Is my only option to use a look-up table on the publisher to
map usernames of all the subscribers to club codes?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
use a pull subscription to get the hostname parameter to resolve to the name
of the subscriber.
If you are using merge replication you can use the HostName parameter on you
merge agent commands for this.
You would have to use left(host_name(), 4) for this to work in your filter
in the pull subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Martin Bancroft" <martin.bancroft@.galagroup.co.uk> wrote in message
news:egYD3AIeEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I want to dynamically filter my published data to many subscribers based
> on a number contained within the server name of the subscriber. ie one
> subscriber server is called 8364-Server and I want to use the 8364 as
> the "club code" to extract the subset from the publisher. I preferably
> want to use push subscriptions.
> Using host_name just seems to return the name of the publisher which is
> no good. Is my only option to use a look-up table on the publisher to
> map usernames of all the subscribers to club codes?
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label subscribers. Show all posts
Showing posts with label subscribers. Show all posts
Sunday, March 11, 2012
Dynamic Filtering
Hi,
I have one publisher and two subscribers, i want to filter the data
dynamiclly before sending it to the subscriber(s), my question is, how to
know the current subscriber when the merge agent implement the filtering
criteria, i tried to use Host_Name() function to know the current subscriber
but it returns the value of the publisher.
If you have any idea(s) or alternative way or any additional configuration i
have to do for this, please let answer me ASAP
Thanks in Advance.
HOST_NAME() should work fine with pull subscriptions. Anyway whether push or
pull, you can use -HOSTNAME value in the merge agent's job commandline to
override the returned value. Often you want to filter on something that has
more relevance to the business this way. For an image of setting the
HOSTNAME take a look at the article on www.replicationanswers.com/articles.
It relates to SQL 2005 but the job image is still relevant.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I've set the HOST_NAME() property for each subscriber merge agent to be its
host name and then worked fine and the data being filtered between servers as
i want.
Thanks again
"Paul Ibison" wrote:
> HOST_NAME() should work fine with pull subscriptions. Anyway whether push or
> pull, you can use -HOSTNAME value in the merge agent's job commandline to
> override the returned value. Often you want to filter on something that has
> more relevance to the business this way. For an image of setting the
> HOSTNAME take a look at the article on www.replicationanswers.com/articles.
> It relates to SQL 2005 but the job image is still relevant.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
I have one publisher and two subscribers, i want to filter the data
dynamiclly before sending it to the subscriber(s), my question is, how to
know the current subscriber when the merge agent implement the filtering
criteria, i tried to use Host_Name() function to know the current subscriber
but it returns the value of the publisher.
If you have any idea(s) or alternative way or any additional configuration i
have to do for this, please let answer me ASAP
Thanks in Advance.
HOST_NAME() should work fine with pull subscriptions. Anyway whether push or
pull, you can use -HOSTNAME value in the merge agent's job commandline to
override the returned value. Often you want to filter on something that has
more relevance to the business this way. For an image of setting the
HOSTNAME take a look at the article on www.replicationanswers.com/articles.
It relates to SQL 2005 but the job image is still relevant.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I've set the HOST_NAME() property for each subscriber merge agent to be its
host name and then worked fine and the data being filtered between servers as
i want.
Thanks again
"Paul Ibison" wrote:
> HOST_NAME() should work fine with pull subscriptions. Anyway whether push or
> pull, you can use -HOSTNAME value in the merge agent's job commandline to
> override the returned value. Often you want to filter on something that has
> more relevance to the business this way. For an image of setting the
> HOSTNAME take a look at the article on www.replicationanswers.com/articles.
> It relates to SQL 2005 but the job image is still relevant.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
dynamic filter - mobile device
Hi,
I am using mobile devices as subscribers and a desktop as a publisher.
If i want to use dynamic filters, do I really have to use host_name?
If the mobile devices have a web service method called ReturnId() that
returns the unique id that identifies the mobile device and the id can be
verified against the database that is being synchronised,
is it possible to do dynamic filters based on this webservice method?
Thank you.
-HOSTNAME can be used as a parameter to the merge agent and a value hardcoded
there. If the value returned from the webservice never changes then it could
be read when creating the subscription and used to define the merge agent's
job.
HTH,
Paul Ibison
|||Hi,
I am terribly sorry but I do not quite understand.
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
how do I modify your solution accordingly if it is at all possible?
eg, if i arbitrarily set my mobile devices with the ids: 1, 2, and 3 for 3
different devices, how can I still use those values as parameters inside your
dynamic filters?
I am a novice at this, thank you for your patience and advice.
"Paul Ibison" wrote:
> -HOSTNAME can be used as a parameter to the merge agent and a value hardcoded
> there. If the value returned from the webservice never changes then it could
> be read when creating the subscription and used to define the merge agent's
> job.
> HTH,
> Paul Ibison
>
|||I'm assuming you run the merge sync programatically on the mobile device - if
this is the case, you'll be using the "SqlCeReplication" class. This class
has a "HostName" property that can be set to the return value from your
webservice. Please let me know if this makes sense fior your case.
HTH,
Paul Ibison
I am using mobile devices as subscribers and a desktop as a publisher.
If i want to use dynamic filters, do I really have to use host_name?
If the mobile devices have a web service method called ReturnId() that
returns the unique id that identifies the mobile device and the id can be
verified against the database that is being synchronised,
is it possible to do dynamic filters based on this webservice method?
Thank you.
-HOSTNAME can be used as a parameter to the merge agent and a value hardcoded
there. If the value returned from the webservice never changes then it could
be read when creating the subscription and used to define the merge agent's
job.
HTH,
Paul Ibison
|||Hi,
I am terribly sorry but I do not quite understand.
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
how do I modify your solution accordingly if it is at all possible?
eg, if i arbitrarily set my mobile devices with the ids: 1, 2, and 3 for 3
different devices, how can I still use those values as parameters inside your
dynamic filters?
I am a novice at this, thank you for your patience and advice.
"Paul Ibison" wrote:
> -HOSTNAME can be used as a parameter to the merge agent and a value hardcoded
> there. If the value returned from the webservice never changes then it could
> be read when creating the subscription and used to define the merge agent's
> job.
> HTH,
> Paul Ibison
>
|||I'm assuming you run the merge sync programatically on the mobile device - if
this is the case, you'll be using the "SqlCeReplication" class. This class
has a "HostName" property that can be set to the return value from your
webservice. Please let me know if this makes sense fior your case.
HTH,
Paul Ibison
Friday, February 17, 2012
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
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
Subscribe to:
Posts (Atom)