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