Hello...
Are the only parameters allowed for dynamic filtering of data just the
Host_Name and SUser_SName?
Just curious...
thanks
- will
Will,
on SQL Server 2000 this is the case. These 2 parameters (suser_sname() and
host_name()) and UDFs using them. Actually the HOSTNAME can be overridden in
the merge job's parameters, so this is not as restrictive as it seems. On
SQL Server 2005 there is the option to override the HOSTNAME setting in the
subscription wizard. Scriptwise this corresponds to
sp_addmergesubscription @.hostname = 'hostname'
and is referred to as the "partition definition".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul..
I am currently overriding the Host_Name value in my application...so that
works fine. I was just hoping/wondering if there could be other parameters
(like a stored proc) that I could pass in... Oh well...guess I'll have to
wait for SQL 2025!
...
"Paul Ibison" wrote:
> Will,
> on SQL Server 2000 this is the case. These 2 parameters (suser_sname() and
> host_name()) and UDFs using them. Actually the HOSTNAME can be overridden in
> the merge job's parameters, so this is not as restrictive as it seems. On
> SQL Server 2005 there is the option to override the HOSTNAME setting in the
> subscription wizard. Scriptwise this corresponds to
> sp_addmergesubscription @.hostname = 'hostname'
> and is referred to as the "partition definition".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||You could use a UDF to transform the HOSTNAME, or use a join to an
intermediate table to produce a 'many' value filter - it depends on what you
would like to achieve.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||hey...interesting idea about using a UDF. I'll give that some brain power...
thanks!
"Paul Ibison" wrote:
> You could use a UDF to transform the HOSTNAME, or use a join to an
> intermediate table to produce a 'many' value filter - it depends on what you
> would like to achieve.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||HostName and SUSER_SNAME() can both be over ridden. SUSER_SNAME() can be
overridden by PublisherLogin.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SciuK3JGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Will,
> on SQL Server 2000 this is the case. These 2 parameters (suser_sname() and
> host_name()) and UDFs using them. Actually the HOSTNAME can be overridden
> in the merge job's parameters, so this is not as restrictive as it seems.
> On SQL Server 2005 there is the option to override the HOSTNAME setting in
> the subscription wizard. Scriptwise this corresponds to
> sp_addmergesubscription @.hostname = 'hostname'
> and is referred to as the "partition definition".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Showing posts with label filtering. Show all posts
Showing posts with label filtering. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Dynamic Filtering, a further question
Am I correct to assume that the Host_Name function, when used in a filter
expression at the publisher will return the subsriber host name?
What does SUSER_SNAME return:
The name of the current logged on user at the subscriber?
The name of the SQL server run account ath the subsciber?
Or the SQLServerAgent run account at the subscriber?
Tony Toker
Data Identic Ltd.
That depends. If it is a pull subscription, which means the agent is running
on the subscriber it will return the name of the subscriber. If it is a push
subscription, which means the agent is running on the publisher it will
return the name of the publisher.
Unless of course we are talking merge replication and you override this
behavior by using the HostName parameter in the merge agent commands
section. (right click on your merge agent, select agent propertes, Steps,
run agent, click edit, and in the command section look or add a -HostName
and then enter the name you wish to be replaced by the HostName parameter
here.
SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:celd7r$n8l$1$830fa7b3@.news.demon.co.uk...
> Am I correct to assume that the Host_Name function, when used in a filter
> expression at the publisher will return the subsriber host name?
> What does SUSER_SNAME return:
> The name of the current logged on user at the subscriber?
> The name of the SQL server run account ath the subsciber?
> Or the SQLServerAgent run account at the subscriber?
> Tony Toker
> Data Identic Ltd.
>
|||<mini-quibble>
"SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber."
The Merge Agent typically runs under SQL Server Agent at the Distributor for
push subscriptions (not the publisher) or at the Subscriber for pull
subscriptions.
</mini-quibble>
Regards,
Paul Ibison
|||That is correct. Thanks for the correction.
Keep in mind that for most topologies - the publisher will be on the same
server as the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uM%23AusJeEHA.1000@.TK2MSFTNGP12.phx.gbl...
> <mini-quibble>
> "SUser_SName will resolve to account that the SQL Server agent runs under.
> For a push, its the SQL Server agent account on the publisher, for a pull
> the SQL Server agent account on the Subscriber."
> The Merge Agent typically runs under SQL Server Agent at the Distributor
for
> push subscriptions (not the publisher) or at the Subscriber for pull
> subscriptions.
> </mini-quibble>
> Regards,
> Paul Ibison
>
expression at the publisher will return the subsriber host name?
What does SUSER_SNAME return:
The name of the current logged on user at the subscriber?
The name of the SQL server run account ath the subsciber?
Or the SQLServerAgent run account at the subscriber?
Tony Toker
Data Identic Ltd.
That depends. If it is a pull subscription, which means the agent is running
on the subscriber it will return the name of the subscriber. If it is a push
subscription, which means the agent is running on the publisher it will
return the name of the publisher.
Unless of course we are talking merge replication and you override this
behavior by using the HostName parameter in the merge agent commands
section. (right click on your merge agent, select agent propertes, Steps,
run agent, click edit, and in the command section look or add a -HostName
and then enter the name you wish to be replaced by the HostName parameter
here.
SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tony Toker" <xxxx@.xxxx.com> wrote in message
news:celd7r$n8l$1$830fa7b3@.news.demon.co.uk...
> Am I correct to assume that the Host_Name function, when used in a filter
> expression at the publisher will return the subsriber host name?
> What does SUSER_SNAME return:
> The name of the current logged on user at the subscriber?
> The name of the SQL server run account ath the subsciber?
> Or the SQLServerAgent run account at the subscriber?
> Tony Toker
> Data Identic Ltd.
>
|||<mini-quibble>
"SUser_SName will resolve to account that the SQL Server agent runs under.
For a push, its the SQL Server agent account on the publisher, for a pull
the SQL Server agent account on the Subscriber."
The Merge Agent typically runs under SQL Server Agent at the Distributor for
push subscriptions (not the publisher) or at the Subscriber for pull
subscriptions.
</mini-quibble>
Regards,
Paul Ibison
|||That is correct. Thanks for the correction.
Keep in mind that for most topologies - the publisher will be on the same
server as the distributor.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uM%23AusJeEHA.1000@.TK2MSFTNGP12.phx.gbl...
> <mini-quibble>
> "SUser_SName will resolve to account that the SQL Server agent runs under.
> For a push, its the SQL Server agent account on the publisher, for a pull
> the SQL Server agent account on the Subscriber."
> The Merge Agent typically runs under SQL Server Agent at the Distributor
for
> push subscriptions (not the publisher) or at the Subscriber for pull
> subscriptions.
> </mini-quibble>
> Regards,
> Paul Ibison
>
Dynamic Filtering Expression
I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:
If SuppressZero = 1
Filter where Quantity <> 0
If SuppressZero = 2
No Filter
If SuppressZero = 3
Filter where Quantity <> 0 Or InStockFamily = "Y"
How can I do this in my report?
In general its better to filter the data in the query than the report. The report filters are per report object not the entire report so in the case of mutiple objects you may need to set multiple filters. Assuming you have a single table report you can set a filter on the that table filter property. The filter porperty has three fields; expression, operator, value. One solution is
Expression: iif((Paramters!SupprssZero=1 and Fields!Quantity=0) or Parameters!SuppressZero=2 or (Parameters!SuppressZero=3 and Fields!Quantity=0 and InstockFamily="N"),1,0)
Operator: =
Value = 1
|||assuming you pass the SuppressZero param to the stored procedure, you need to add a WHERE clause something likeWHERE
(@.suppressZero = 1 and quantity <> 0)
OR
(@.supressZero = 2)
OR
(@.supressZero = 3 and (quantity <> 0 OR instockfamily <> 'Y')
Dynamic Filtering
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!
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!
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 Filtering
Hi Guys,
I have been playing with dynamic filtering in merge replication and I
have a question about its behavior. What I have done is to use a user
defined function to evaluate SUSER_SNAME() and return a list of ids by
which the records should be filtered for that user.
Everything works fine and synchronization works as expected. However if
the list of ids that the UDF returns changes, it makes no difference when I
synchronize. It will only reflect that change when I reiniliaze the
subscriber and synchronize again.
So my question is:
Is the filtering criteria "locked" when you apply the snapshot? How does
it work?
Thanks,
Maer
Hello Maer,
Based on my scope, this behavior is as designed because synchronization
only synchronize the data between publication and subscriber and it does
nothing to filter itself. Reinitialization will usually update the snapshot
on subscriber according to filter. The snapshot schema and data are applied
at the Subscriber after the next time the Snapshot Agent prepares a
snapshot and merge Agent runs.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
--
>From: "Maer" <maer@.auditleverage.com>
>Subject: Dynamic Filtering
>Date: Wed, 26 Oct 2005 18:35:53 -0400
>Lines: 20
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: pcp08364781pcs.lndsd201.pa.comcast.net 68.42.19.117
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17387
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Hi Guys,
> I have been playing with dynamic filtering in merge replication and I
>have a question about its behavior. What I have done is to use a user
>defined function to evaluate SUSER_SNAME() and return a list of ids by
>which the records should be filtered for that user.
> Everything works fine and synchronization works as expected. However
if
>the list of ids that the UDF returns changes, it makes no difference when
I
>synchronize. It will only reflect that change when I reiniliaze the
>subscriber and synchronize again.
> So my question is:
> Is the filtering criteria "locked" when you apply the snapshot? How
does
>it work?
> Thanks,
> Maer
>
>
|||I find what I have to do is run multiple snapshots for each subscriber when
I am doing something like what you are attempting to do.
So I override the value of SUSER_NAME() with the PublisherLogin of andrew
and get a snapshot for andrew which I distribute, then I repeat for Nancy.
The accounts have to be in the dbo role of the publication database and the
distribution database to get this to work IIRC. Make sure you set
PublisherLogin in the snapshot agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maer" <maer@.auditleverage.com> wrote in message
news:uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I have been playing with dynamic filtering in merge replication and I
> have a question about its behavior. What I have done is to use a user
> defined function to evaluate SUSER_SNAME() and return a list of ids by
> which the records should be filtered for that user.
> Everything works fine and synchronization works as expected. However
if
> the list of ids that the UDF returns changes, it makes no difference when
I
> synchronize. It will only reflect that change when I reiniliaze the
> subscriber and synchronize again.
> So my question is:
> Is the filtering criteria "locked" when you apply the snapshot? How
does
> it work?
> Thanks,
> Maer
>
|||Hi Hilary,
Are you referring to dynamic snapshots? Let me show you my filter and
the UDF that I created to illustrate my dilemma (below). As you can see
SUSER_SNAME() is used in the body of the UDF. According to BOL:
"You can use the SUSER_SNAME() system function nested in a user-defined
function in the filter criteria for a dynamic filter, and you can use a
dynamic snapshot (for example, MyUDF(SUSER_SNAME()) where the MyUDF
user-defined function evaluates the SUSER_SNAME() system function). The
system function must be visible in the dynamic filter criteria. If the
system function exists in the definition of the user-defined function, and
you enter only the user-defined function in the dynamic filter, you will not
be able to use a dynamic snapshot."
However I cannot do something like "select id from
udf_GetIdList(SUSER_SNAME() ,'Categories')" expliciting passing
SUSER_SNAME() so that it is visible in the filter criteria (I get a syntax
error).
As I said, the filter below works fine, but the whole point of this exercise
was to be able to change the Replication_Filters table so that it would
return different ids for that user without having to reinitialize the
subscriber and create another another snapshot.
According to Peter Yang, I would still have to reinitialize the subscriber
otherwise the different ids will not have any effect on the filtering. Since
the ids would change quite often, this is not desirable.
Given this scenario and my UDF limitation. Is there any other way to get the
result I am looking for?
Thank you for your help,
Maer
SELECT <published_columns> FROM [dbo].[Categories] WHERE
CategoryId in (select id from udf_GetIdList('Categories'))
CREATE FUNCTION [dbo].[udf_GetIdList]
(
@.TableName sysname
)
RETURNS @.IdList Table
(
id int
)
AS
BEGIN
insert @.IdList
SELECT dbo.Replication_Filters.PKId
FROM dbo.Replication_Filters INNER JOIN
dbo.Replication_Users ON
dbo.Replication_Filters.UserId = dbo.Replication_Users.UserId INNER JOIN
dbo.Replication_FilteredTables ON
dbo.Replication_Filters.TableId = dbo.Replication_FilteredTables.TableId
WHERE (dbo.Replication_Users.UserName = SUSER_SNAME() ) AND
(dbo.Replication_FilteredTables.TableName = @.TableName)
return
END
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O9jIFLu2FHA.956@.TK2MSFTNGP10.phx.gbl...
>I find what I have to do is run multiple snapshots for each subscriber when
> I am doing something like what you are attempting to do.
> So I override the value of SUSER_NAME() with the PublisherLogin of andrew
> and get a snapshot for andrew which I distribute, then I repeat for Nancy.
> The accounts have to be in the dbo role of the publication database and
> the
> distribution database to get this to work IIRC. Make sure you set
> PublisherLogin in the snapshot agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Maer" <maer@.auditleverage.com> wrote in message
> news:uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl...
> if
> I
> does
>
|||Hello Maer,
It seems you are trying to filter different tables according to user name.
Dynamic filter is designed to filter rows according to the "where"
condition on a specific column. UDF you uses returns a table which does not
fit for a where clause.
Since you want to filter different tables for users, I am afraid that you
have to reinitialize the subscriber because the snapshot are different.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Maer" <maer@.auditleverage.com>
>References: <uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl>
<O9jIFLu2FHA.956@.TK2MSFTNGP10.phx.gbl>
>Subject: Re: Dynamic Filtering
>Date: Thu, 27 Oct 2005 11:55:12 -0400
>Lines: 113
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <uGxp45w2FHA.2196@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: pcp08364781pcs.lndsd201.pa.comcast.net 68.42.19.117
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17415
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Hi Hilary,
> Are you referring to dynamic snapshots? Let me show you my filter and
>the UDF that I created to illustrate my dilemma (below). As you can see
>SUSER_SNAME() is used in the body of the UDF. According to BOL:
>"You can use the SUSER_SNAME() system function nested in a user-defined
>function in the filter criteria for a dynamic filter, and you can use a
>dynamic snapshot (for example, MyUDF(SUSER_SNAME()) where the MyUDF
>user-defined function evaluates the SUSER_SNAME() system function). The
>system function must be visible in the dynamic filter criteria. If the
>system function exists in the definition of the user-defined function, and
>you enter only the user-defined function in the dynamic filter, you will
not
>be able to use a dynamic snapshot."
> However I cannot do something like "select id from
>udf_GetIdList(SUSER_SNAME() ,'Categories')" expliciting passing
>SUSER_SNAME() so that it is visible in the filter criteria (I get a
syntax
>error).
>As I said, the filter below works fine, but the whole point of this
exercise
>was to be able to change the Replication_Filters table so that it would
>return different ids for that user without having to reinitialize the
>subscriber and create another another snapshot.
>According to Peter Yang, I would still have to reinitialize the subscriber
>otherwise the different ids will not have any effect on the filtering.
Since
>the ids would change quite often, this is not desirable.
>Given this scenario and my UDF limitation. Is there any other way to get
the[vbcol=seagreen]
>result I am looking for?
>Thank you for your help,
>Maer
>SELECT <published_columns> FROM [dbo].[Categories] WHERE
>CategoryId in (select id from udf_GetIdList('Categories'))
>CREATE FUNCTION [dbo].[udf_GetIdList]
>(
> @.TableName sysname
>)
>RETURNS @.IdList Table
>(
> id int
>)
> AS
>BEGIN
> insert @.IdList
> SELECT dbo.Replication_Filters.PKId
> FROM dbo.Replication_Filters INNER JOIN
> dbo.Replication_Users ON
>dbo.Replication_Filters.UserId = dbo.Replication_Users.UserId INNER JOIN
> dbo.Replication_FilteredTables ON
>dbo.Replication_Filters.TableId = dbo.Replication_FilteredTables.TableId
> WHERE (dbo.Replication_Users.UserName = SUSER_SNAME() ) AND
>(dbo.Replication_FilteredTables.TableName = @.TableName)
> return
>END
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:O9jIFLu2FHA.956@.TK2MSFTNGP10.phx.gbl...
when[vbcol=seagreen]
Nancy.[vbcol=seagreen]
I[vbcol=seagreen]
when
>
>
I have been playing with dynamic filtering in merge replication and I
have a question about its behavior. What I have done is to use a user
defined function to evaluate SUSER_SNAME() and return a list of ids by
which the records should be filtered for that user.
Everything works fine and synchronization works as expected. However if
the list of ids that the UDF returns changes, it makes no difference when I
synchronize. It will only reflect that change when I reiniliaze the
subscriber and synchronize again.
So my question is:
Is the filtering criteria "locked" when you apply the snapshot? How does
it work?
Thanks,
Maer
Hello Maer,
Based on my scope, this behavior is as designed because synchronization
only synchronize the data between publication and subscriber and it does
nothing to filter itself. Reinitialization will usually update the snapshot
on subscriber according to filter. The snapshot schema and data are applied
at the Subscriber after the next time the Snapshot Agent prepares a
snapshot and merge Agent runs.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
--
>From: "Maer" <maer@.auditleverage.com>
>Subject: Dynamic Filtering
>Date: Wed, 26 Oct 2005 18:35:53 -0400
>Lines: 20
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: pcp08364781pcs.lndsd201.pa.comcast.net 68.42.19.117
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17387
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Hi Guys,
> I have been playing with dynamic filtering in merge replication and I
>have a question about its behavior. What I have done is to use a user
>defined function to evaluate SUSER_SNAME() and return a list of ids by
>which the records should be filtered for that user.
> Everything works fine and synchronization works as expected. However
if
>the list of ids that the UDF returns changes, it makes no difference when
I
>synchronize. It will only reflect that change when I reiniliaze the
>subscriber and synchronize again.
> So my question is:
> Is the filtering criteria "locked" when you apply the snapshot? How
does
>it work?
> Thanks,
> Maer
>
>
|||I find what I have to do is run multiple snapshots for each subscriber when
I am doing something like what you are attempting to do.
So I override the value of SUSER_NAME() with the PublisherLogin of andrew
and get a snapshot for andrew which I distribute, then I repeat for Nancy.
The accounts have to be in the dbo role of the publication database and the
distribution database to get this to work IIRC. Make sure you set
PublisherLogin in the snapshot agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Maer" <maer@.auditleverage.com> wrote in message
news:uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl...
> Hi Guys,
> I have been playing with dynamic filtering in merge replication and I
> have a question about its behavior. What I have done is to use a user
> defined function to evaluate SUSER_SNAME() and return a list of ids by
> which the records should be filtered for that user.
> Everything works fine and synchronization works as expected. However
if
> the list of ids that the UDF returns changes, it makes no difference when
I
> synchronize. It will only reflect that change when I reiniliaze the
> subscriber and synchronize again.
> So my question is:
> Is the filtering criteria "locked" when you apply the snapshot? How
does
> it work?
> Thanks,
> Maer
>
|||Hi Hilary,
Are you referring to dynamic snapshots? Let me show you my filter and
the UDF that I created to illustrate my dilemma (below). As you can see
SUSER_SNAME() is used in the body of the UDF. According to BOL:
"You can use the SUSER_SNAME() system function nested in a user-defined
function in the filter criteria for a dynamic filter, and you can use a
dynamic snapshot (for example, MyUDF(SUSER_SNAME()) where the MyUDF
user-defined function evaluates the SUSER_SNAME() system function). The
system function must be visible in the dynamic filter criteria. If the
system function exists in the definition of the user-defined function, and
you enter only the user-defined function in the dynamic filter, you will not
be able to use a dynamic snapshot."
However I cannot do something like "select id from
udf_GetIdList(SUSER_SNAME() ,'Categories')" expliciting passing
SUSER_SNAME() so that it is visible in the filter criteria (I get a syntax
error).
As I said, the filter below works fine, but the whole point of this exercise
was to be able to change the Replication_Filters table so that it would
return different ids for that user without having to reinitialize the
subscriber and create another another snapshot.
According to Peter Yang, I would still have to reinitialize the subscriber
otherwise the different ids will not have any effect on the filtering. Since
the ids would change quite often, this is not desirable.
Given this scenario and my UDF limitation. Is there any other way to get the
result I am looking for?
Thank you for your help,
Maer
SELECT <published_columns> FROM [dbo].[Categories] WHERE
CategoryId in (select id from udf_GetIdList('Categories'))
CREATE FUNCTION [dbo].[udf_GetIdList]
(
@.TableName sysname
)
RETURNS @.IdList Table
(
id int
)
AS
BEGIN
insert @.IdList
SELECT dbo.Replication_Filters.PKId
FROM dbo.Replication_Filters INNER JOIN
dbo.Replication_Users ON
dbo.Replication_Filters.UserId = dbo.Replication_Users.UserId INNER JOIN
dbo.Replication_FilteredTables ON
dbo.Replication_Filters.TableId = dbo.Replication_FilteredTables.TableId
WHERE (dbo.Replication_Users.UserName = SUSER_SNAME() ) AND
(dbo.Replication_FilteredTables.TableName = @.TableName)
return
END
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O9jIFLu2FHA.956@.TK2MSFTNGP10.phx.gbl...
>I find what I have to do is run multiple snapshots for each subscriber when
> I am doing something like what you are attempting to do.
> So I override the value of SUSER_NAME() with the PublisherLogin of andrew
> and get a snapshot for andrew which I distribute, then I repeat for Nancy.
> The accounts have to be in the dbo role of the publication database and
> the
> distribution database to get this to work IIRC. Make sure you set
> PublisherLogin in the snapshot agent.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Maer" <maer@.auditleverage.com> wrote in message
> news:uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl...
> if
> I
> does
>
|||Hello Maer,
It seems you are trying to filter different tables according to user name.
Dynamic filter is designed to filter rows according to the "where"
condition on a specific column. UDF you uses returns a table which does not
fit for a where clause.
Since you want to filter different tables for users, I am afraid that you
have to reinitialize the subscriber because the snapshot are different.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Maer" <maer@.auditleverage.com>
>References: <uFHCI1n2FHA.1700@.tk2msftngp13.phx.gbl>
<O9jIFLu2FHA.956@.TK2MSFTNGP10.phx.gbl>
>Subject: Re: Dynamic Filtering
>Date: Thu, 27 Oct 2005 11:55:12 -0400
>Lines: 113
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <uGxp45w2FHA.2196@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: pcp08364781pcs.lndsd201.pa.comcast.net 68.42.19.117
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17415
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Hi Hilary,
> Are you referring to dynamic snapshots? Let me show you my filter and
>the UDF that I created to illustrate my dilemma (below). As you can see
>SUSER_SNAME() is used in the body of the UDF. According to BOL:
>"You can use the SUSER_SNAME() system function nested in a user-defined
>function in the filter criteria for a dynamic filter, and you can use a
>dynamic snapshot (for example, MyUDF(SUSER_SNAME()) where the MyUDF
>user-defined function evaluates the SUSER_SNAME() system function). The
>system function must be visible in the dynamic filter criteria. If the
>system function exists in the definition of the user-defined function, and
>you enter only the user-defined function in the dynamic filter, you will
not
>be able to use a dynamic snapshot."
> However I cannot do something like "select id from
>udf_GetIdList(SUSER_SNAME() ,'Categories')" expliciting passing
>SUSER_SNAME() so that it is visible in the filter criteria (I get a
syntax
>error).
>As I said, the filter below works fine, but the whole point of this
exercise
>was to be able to change the Replication_Filters table so that it would
>return different ids for that user without having to reinitialize the
>subscriber and create another another snapshot.
>According to Peter Yang, I would still have to reinitialize the subscriber
>otherwise the different ids will not have any effect on the filtering.
Since
>the ids would change quite often, this is not desirable.
>Given this scenario and my UDF limitation. Is there any other way to get
the[vbcol=seagreen]
>result I am looking for?
>Thank you for your help,
>Maer
>SELECT <published_columns> FROM [dbo].[Categories] WHERE
>CategoryId in (select id from udf_GetIdList('Categories'))
>CREATE FUNCTION [dbo].[udf_GetIdList]
>(
> @.TableName sysname
>)
>RETURNS @.IdList Table
>(
> id int
>)
> AS
>BEGIN
> insert @.IdList
> SELECT dbo.Replication_Filters.PKId
> FROM dbo.Replication_Filters INNER JOIN
> dbo.Replication_Users ON
>dbo.Replication_Filters.UserId = dbo.Replication_Users.UserId INNER JOIN
> dbo.Replication_FilteredTables ON
>dbo.Replication_Filters.TableId = dbo.Replication_FilteredTables.TableId
> WHERE (dbo.Replication_Users.UserName = SUSER_SNAME() ) AND
>(dbo.Replication_FilteredTables.TableName = @.TableName)
> return
>END
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:O9jIFLu2FHA.956@.TK2MSFTNGP10.phx.gbl...
when[vbcol=seagreen]
Nancy.[vbcol=seagreen]
I[vbcol=seagreen]
when
>
>
Subscribe to:
Posts (Atom)