Dynamic Query :: HELP!

I'm the new guy and I could use a hand. Any insight would be appreciated.

Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.

row1 x y z
row2 x b z
row3 x m z

results should look like :: x, (y + b + m) , z

Thank you in advance for any help you can provide!numbers? you can SUM() them, but description? can't be done

you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows|||numbers? you can SUM() them, but description? can't be done

you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows

Unfortunately the data is all of char type. :confused:|||Any suggestions of how to go about doing this using a procedure?|||http://www.dbforums.com/t1038027.html|||Thanks for the link...I can't say I totally understand the function though.

I hate to ask this, and sound like an idiot, but can anyone give me the english break down?|||Cut and paste this code in to Query Analyzer...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40520

Dynamic horizontal partitioning

I have a database which requires merge replication which can be horizontally
partitioned into 25 branches.
Would this create 25 distribution databases and is there a high impact on
the performance on updating these databases?
Or is the best solution to use is dynamic horizontal partitioning?
Thanks
mm,
there is no need for more than one distribution database. Your options are
to create 25 separate publications and a distinct subscriber for each or (as
you say) you can use dynamic horizontal partitioning. I would favour the
latter as it is less administration, especially if any schema changes are
needed. To implement, you can easily add -HOSTNAME as a merge agent
parameter for each subscriber, and use this to filter.
Regards,
Paul Ibison

Dynamic Form Letters

I would like to design a 'mail merge' type of letter, whereby some of the paragraphs will be conditional on parameters. Can anyone direct me to example of mail merger letters using reporting services?

thanks

ken

Did you ever find out any info about this? I am needing to do the same thing.

Thanks,

Scott

Dynamic Form Letters

I would like to design a 'mail merge' type of letter, whereby some of the paragraphs will be conditional on parameters. Can anyone direct me to example of mail merger letters using reporting services?

thanks

ken

Did you ever find out any info about this? I am needing to do the same thing.

Thanks,

Scott

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

Dynamic Filter Not Updating Correctly

I have several SQL Mobile devices (WM5) syncronizing with a SQL 2005 database through merge replication. We are using the HOST_NAME parameter to filter several tables. A few of these filters use a sub-query to filter the data because of the complexity of the filter (need to do more than join to one table). The subquery will use the HOST_NAME parameter. For example, I want to get a list of all users in the current user's company I need to use host_name to get the current user, find their company ID, then filter the user table on that company ID.

Everything works fine until a user is removed from the table. In that case, the delete statement is not replicated to the device until they reinitialize the mobile database.

Thanks for the help!!

Mike

it's unclear what the problem is without knowing what your filter clause looks like. Could you script out your publication, article and any merge/join filter clauses?|||

One of the affected pieces consists of 2 tables. A USER_LOCATION table that contains the locations a user want to see and a LOCATION table that contains all locations (see script below). I filter the USER_LOCATION table with the following filter: WHERE cast(person_id as varchar(36)) = host_name(). I then use a join to extend the filter on the location_id to the LOCATION table. On the device, I do not use the USER_LOCATION table as it is really just used to filter the proper locations for the user. I have also tried a filter on the LOCATION table directly using a subquery (so that the user_location table never makes it to the device). The same problem still shows up: If a user removes a location from their list from our website (used to configure their settings, which are replicated to the device), the next sync does not delete the record from the device. In fact, the only way to get the delete to take is to reinitialize the database on the device.

Tables:

CREATE TABLE [dbo].[USER_LOCATION](

[user_location_id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_USER_LOCATION_user_location_id] DEFAULT (newsequentialid()),

[user_id] [uniqueidentifier] NOT NULL,

[location_id] [uniqueidentifier] NOT NULL)

GO

CREATE TABLE [dbo].[LOCATION](

[location_id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__LOCATION__locati__48CFD27E] DEFAULT (newsequentialid()),

[name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[abbreviation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)

|||

Mike, it would be much easier if you scripted your publication, articles (maybe just for USER_LOCATION and LOCATION for now) and merge filter joins, can you do that please?

Just to be clear, when a row from LOCATION is deleted at the publisher, the delete isn't replicated down to the subscriber, correct? That would mean there's nonconvergence, and validation would fail. Could you verify that validation for the given subscriber fails?

|||

Actually, we just updated some of our code and upgraded to SP1 and the problem seems to be gone. Hopefully we won't see it in the future. Thanks for the help!

Mike

|||Hello,
I am facing the same problem. I have dynamic filters on most tables and the rows dont get deleted from the subscriber once they cease to satisfy the filter conditions.
HEre is a scenario: A Dynamic filter checks for suser_sname() and based on that(and a few other conditions) sends rows to the subscriber. when the data in the column used to compare the suser_sname() value changes at the publisher , the related row automatically got deleted from the subscriber. Recently this has stopped happening. Validation of the subscription also fails. Mike- What did you upgrade to SP1? We are running off SQL Server2005 SP2? thanks in advance. Rocky

Dynamic Dynamic Filters

Hello,
I am attempting to use dynamic filters on merge replication to filter rows. In my front end app I am using the ActiveX Merge object to set the Host_Name property. My question is: Is it possible to set the host name to a more complex string?
example:
.HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
The problem I am running into is that the sql wizard for creating the dynamic filters runs a check on the sql statement and won't allow something like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
Technically if I could somehow bypass the wizard to enter the filter rules this should work. Anyone know if this is possible? Or possibly another way to work around this so that you can pass more than 1 value to the where clause? I suppose you could do so
mething like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
Would this be the only way? Any feedback would be greatly appreciated. Thanks in advance.
I'm unsure on what your filtering condition is. For instance your filter
should evaluate to a boolean true or false. Yours evaluates to hostname.
You can use a UDF to extend the functionalty of your filter or you can do
stuff like this
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
or use a subquery
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
Servername from ServerList where state='ca')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> Hello,
> I am attempting to use dynamic filters on merge replication to filter
rows. In my front end app I am using the ActiveX Merge object to set the
Host_Name property. My question is: Is it possible to set the host name to a
more complex string?
> example:
> .HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> The problem I am running into is that the sql wizard for creating the
dynamic filters runs a check on the sql statement and won't allow something
like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
> Technically if I could somehow bypass the wizard to enter the filter rules
this should work. Anyone know if this is possible? Or possibly another way
to work around this so that you can pass more than 1 value to the where
clause? I suppose you could do something like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
> HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> Would this be the only way? Any feedback would be greatly appreciated.
Thanks in advance.
>
>
|||Yes mine purposely evaluates to hostname. What I am trying to do is set the filter to:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
and then set hostname to something like:
Col = 'Val1' OR Col = 'Val2'
So that the resulting select will look like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = 'Val1' OR Col = 'Val2'
Or whatever other complex WHERE clause that I choose. The problem is that it won't except the above filter. My work around listed above should work (I have yet to test it). Although I am interested in other possibilites to achieve the same results. This s
eems fairly basic unless I am missing something?
"Hilary Cotter" wrote:

> I'm unsure on what your filtering condition is. For instance your filter
> should evaluate to a boolean true or false. Yours evaluates to hostname.
> You can use a UDF to extend the functionalty of your filter or you can do
> stuff like this
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
> or use a subquery
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
> Servername from ServerList where state='ca')
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> rows. In my front end app I am using the ActiveX Merge object to set the
> Host_Name property. My question is: Is it possible to set the host name to a
> more complex string?
> dynamic filters runs a check on the sql statement and won't allow something
> like:
> this should work. Anyone know if this is possible? Or possibly another way
> to work around this so that you can pass more than 1 value to the where
> clause? I suppose you could do something like:
> Thanks in advance.
>
>
|||Steve,
you could have a mapping table which relates values (Val1 and Val2) to
hostnames. In your example there would be 2 rows:
-- MAPPINGTABLE --
hostname1 val1
hostname1 val2
If you include this table in the filter, you can achieve the clause you want
to create.
HTH,
Paul Ibison
|||Paul,
I am not familiar with mapping tables and I have been unable to find any information relating to them. Could you provide an example of what you mean or possibly a link to some information? Thanks.
|||Steve,
it's easiest if I explain how I set up a test: I had 2 tables - region and
HostnameLookup - shown below. The HostnameLookup table defines the multiple
values I'm interested in. I use dynamic filters to filter the HostnameLookup
table using HOST_NAME() and a join filter to join to the region table.
Editing the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer means that only 2 regions get replicated. I've listed the
exact text below in case you want to recreate it to test.
HTH,
Paul Ibison
Region
RegionID RegionDescription rowguid
-- -- --
1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
HostnameLookup
RegionDescription Hostname rowguid
-- -- --
Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
Filter Clause for HostnameLookup:
SELECT <published_columns> FROM [dbo].[HostnameLookup]
WHERE HostnameLookup.Hostname = HOST_NAME()
Filter Clause for Region:
< All rows published >
Join Filter:
Filtered table is HostnameLookup
Table to FIlter is Region
SELECT <published_columns> FROM [dbo].[HostnameLookup]
INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
region.regiondescription
Edit the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer
Run the snapshot then merge agents and only 2 regions should be replicated.
|||Hi Paul,
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,
can I modify your solution accordingly?
Thank you.
"Paul Ibison" wrote:

> Steve,
> it's easiest if I explain how I set up a test: I had 2 tables - region and
> HostnameLookup - shown below. The HostnameLookup table defines the multiple
> values I'm interested in. I use dynamic filters to filter the HostnameLookup
> table using HOST_NAME() and a join filter to join to the region table.
> Editing the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer means that only 2 regions get replicated. I've listed the
> exact text below in case you want to recreate it to test.
> HTH,
> Paul Ibison
> Region
> --
> RegionID RegionDescription rowguid
> -- -- --
> 1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
> 2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
> 3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
> 4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
> HostnameLookup
> --
> RegionDescription Hostname rowguid
> -- -- --
> Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
> Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
>
> Filter Clause for HostnameLookup:
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> WHERE HostnameLookup.Hostname = HOST_NAME()
> Filter Clause for Region:
> < All rows published >
> Join Filter:
> Filtered table is HostnameLookup
> Table to FIlter is Region
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
> region.regiondescription
> Edit the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer
> Run the snapshot then merge agents and only 2 regions should be replicated.
>
>

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

Dynamic and Join Filters with Multipple clauses

Hello,
I have a strange problem with merge replication. Our database has a users
table and an Orders table. we are succesfully using suser_sname() as our
first clause on the users table. This by itself works fine and only that
users data are returned to the PDA. However, when the second clause is
added, this is a clause on orders table ie. active='YES' which returns the
active orders only, then SQL seems to return a union of these 2 clauses
rather than using them both at the same time.
Any ideas?
I've seen this before - the view that is created uses a union rather than an
extra AND clause in the select statement. I suppose a workaround would be to
use an indexed view and join the data yourself, or you could add each filter
to each table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for your prompt reply. I am hacking the view in question and things
seem to work. I would have thought that such view behaivour is rather
strange.
Let's hope Microsoft fix this bug/feature in the future so that it does not
require weird work arounds.
Many thanks
"Paul Ibison" wrote:

> I've seen this before - the view that is created uses a union rather than an
> extra AND clause in the select statement. I suppose a workaround would be to
> use an indexed view and join the data yourself, or you could add each filter
> to each table.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Thanks for the update. I'll take a look at SQL Server 2005 this weekend and
post back to see if the fix exists.
Cheers,
Paul Ibison