Merge snapshot frequency

I have the following scenario,
1.merge replication with 3000 subscribers
2.I have dynamic publication (NOT dynamic snapshot)
3.we observed that synchronization is slow whenever we do a data import at
the publisher
4.because of this we ran the snapshot agent immediately after the data import
5.after the snapshot run, we find the subscriber syncs faster
I would like to know what are the implications of running snapshot agent.
Will it remove the delta changes which are pending for sync? Or is it okay if
I run snapshot as much as I like?
Expect slowness whenever you do a data import as there is always the impact
of the dataload and then there is the added data you have to merge.
Are you saying if you run the snapshot you get faster sync's? This only
makes sense if you re-initialize your subscribers after regenerating the
snapshot.
If you have anonymous subscribers the snapshot is always generated each time
you run it. If you have named it is only regenerated if subscribers expire
or require reinitialization.
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
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>I have the following scenario,
> 1.merge replication with 3000 subscribers
> 2.I have dynamic publication (NOT dynamic snapshot)
> 3.we observed that synchronization is slow whenever we do a data import at
> the publisher
> 4.because of this we ran the snapshot agent immediately after the data
> import
> 5.after the snapshot run, we find the subscriber syncs faster
>
> I would like to know what are the implications of running snapshot agent.
> Will it remove the delta changes which are pending for sync? Or is it okay
> if
> I run snapshot as much as I like?
>
>
|||Thank you Hilary for you time. I have some more clarifications,
1.I have sql server ce subscribers
2.Hence I need to use anonymous subscription
I have the following questions here,
a)Can I use pre-generated snapshot in my case? (Anonymous + sql ce
subscribers)
b)I also have dynamic filters on the publisher. What impact I will have by
re-running the snapshot second time, on the subscriber?
"Hilary Cotter" wrote:

> Expect slowness whenever you do a data import as there is always the impact
> of the dataload and then there is the added data you have to merge.
> Are you saying if you run the snapshot you get faster sync's? This only
> makes sense if you re-initialize your subscribers after regenerating the
> snapshot.
> If you have anonymous subscribers the snapshot is always generated each time
> you run it. If you have named it is only regenerated if subscribers expire
> or require reinitialization.
> --
> 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
>
> "Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
> news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>
>

Merge Repliction - Run Stored Procedure when merge agent starts

I have database on SQL Server 2000 set up with a merge publication.
This publication is configured with a number of dynamic filters to
reduce the amount of data sent to each client. Each client has an
anonymous pull subscription. The merge process can be triggered by the
windows sync manager and my application.

To improve performance I have created some helper tables to hold the
mapping between user login and primary keys of selected entities.

For the replicated data to be correct the contents of the helper tables
needs to be up to date.

I need to fire off a stored procedure on the publisher before
replication starts to verify that this data is up to date. I can not
see any documented way of doing this however I have been experimenting
with some unorthodox systems.

Firstly has anyone any ideas?

I have been considering adding a trigger to some of the tables used by
the Microsoft replication code - yes I know this is very nasty.

My problems arise because executing this stored procedure will cause
some data to be updated. In updating data we could create a new
generation in the database. I must therefore run my stored procedure
before any the Microsoft code makes any generation checks / updates.

Anyone done anything similar, Anyone have any better ideas?

Any comments would be gratefully received.(tedd_n_alex@.yahoo.com) writes:
> My problems arise because executing this stored procedure will cause
> some data to be updated. In updating data we could create a new
> generation in the database. I must therefore run my stored procedure
> before any the Microsoft code makes any generation checks / updates.
> Anyone done anything similar, Anyone have any better ideas?

Not being very good at replication, I can only recommend you to visit
microsoft.public.sqlserver.replication. There are some guys that knows
replicataion really well there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Merge replication with dynamic filter

Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database.

I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc.

I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great.

Thanks in advance.

SUSER_NAME is the account that replication connects to the publisher with. You can override it with the -PublisherLogon property. By chance is this subscriber SQL Server Express? There are some limitations of SQL Server Express with replication, and I suspect that you are bumping into one of them. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 wrote in message news:740eab10-6268-4aff-9dc9-56651ebc9d3d@.discussions.microsoft.com... Brand new to SQL Server 2005 and replication, I was able to use online books info to get a replication topology set up (using wizards) and successfully replicate an article (a single table) to a SQL Server Express database. I then cleared the subscription table and added a simple dynamic filter (colx = SUSER_SNAME()), which I can't get to work. Based on what I read, I'm thinking that SUSER_SNAME() is not returning the proper value because the merge agent is set to connect to the publisher and distributor by 'impersonating the process account'. This is on the subscription merge agent security form. However, 'using a SQL Server login' is greyed out and nothing I tried will enable it, including setting up logins and SQL user ids on the publisher and playing with entries on the publication properties data partitions page, etc. I think that I have discovered only some of the pieces that I need and not enough details to find the rest and put them together. Any info on what to have set up where would be appreciated. Any suggestions for 'step by step' information that would help in addition to SQL Server 2005 Books Online would be great. Thanks in advance.|||Yes, the subscriber is on SQL Express. In the mean time, I am trying to get it to work with HOST_NAME(). That doesn't seem to have the login/user/rights issues that SUSER_SNAME() does?|||You could override the -HostName parameter with what you normally use for the SUSER_NAME. For example if you want to filter on users, you could use a -HostName parameter of Fred. -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. 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 wrote in message news:927b07a9-77e7-4a97-b7f5-14f5f9b9a496@.discussions.microsoft.com... Yes, the subscriber is on SQL Express. In the mean time, I am trying to get it to work with HOST_NAME(). That doesn't seem to have the login/user/rights issues that SUSER_SNAME() does?|||

There are three situations related to your scenario:

1. if the merge subscription is push, there is a known Yukon bug, which tells there is no way to input a SQL Authentication user to login in Publisher/Distributor. Please refer to the below description of the bug.

In dynamic filter merge pub, if filter is like SUSER_SNAME(), and SUSER_SNAME is a SQL user, there is no way to input a SQL Authentication user to connect Pub/Dist in Merge Agent Security Page of CreateSub wizard.

For push sub, Publisherlogin and DistributorLogin can only be process account in UI.

2. If merge subscription is pull. It will be fine.

3. If subscriber is based on SQL Express and merge subscription is pull, it won't be supported. Since SQL Express doesn't support Merge agent at all, sync can only be done through Windowns Synchronization.

Hope the above information will help.

-Yunjing

|||

I got rid of everything that I had and started over this time using HOST_NAME(). Everything went smoothly and it worked the first time!

I'm not sure why the MS online books appear to focus on SUSER_SNAME() and mention HOST_NAME(). The use of SUSER_SNAME() looks to be way more complicated in accomplishing the same thing despite the bug mentioned.

Thanks for helping a new guy get going. The merge replication tool with filtering is going to save a bunch of additional application development.

|||

It's glad to see your scenario worked. SUSER_SNAME() is used more often than HOST_NAME(), since it can implement partitional filtering for different user instead of for different machine. The bug I mentioned is only for configuration through UI, if replication is set up through SP, everything still works fine.

Hope these information will help as well.

Thanks

Yunjing

Merge Replication using a guid as a dynamic filter

Hi ...

I am working on a project where the server version of application has vouchers from different entities. I have created a publication manually. My next step was to create a client subscription using rmo and to execute a pull. This part works fine. Code samples from http://msdn2.microsoft.com/en-us/library/ms147314.aspx

My next step would be to implement dynamic filtering using the guid of the entity as a parameter.

I dont want to use suser_sname() or host_name() as I want to use a fixed login for the replication for all users, and a client could have several host dbs (sql express, sql mobile)

My goal would be to pass a guid-value to the HostName Property of the MergePullSubscription class and convert it to an uniquidentifier and use it as a filter as I have not found any other way to pass a guid as a filter.

RMO-Code:

subscription.HostName = "4bb0e468-c68a-4253-ba82-f71c3a6e302d"

Filter:

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] = dbo.fx_ConvertHostToEntity()

Function:

create function fx_ConvertHostToEntity()
returns uniqueidentifier
as
Begin
declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( @.host as uniqueidentifier)
return @.entity
End

When trying to set the filter sql server complains that a character string cannot be casted to a uniqueidentifier - so i can not set this filter. Is there a way to pass a parameter other then the username or the hostname as a filter?

SELECT <published_columns> FROM [dbo].[voucher] WHERE [entity_ID] =@.entity, where @.entity is a guid

Thanks for your support

Alex

Hi, Alex,

You can convert the string to binary then to uniqueidentifier, like following:

declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( convert(varbinary, @.host) as uniqueidentifier)
print @.entity

Output:

00450044-004C-004C-3000-340032003700

Thanks,

Zhiqiang Feng

|||

Thanks for the info changed the function according to your suggestion.

Now a new problem arrises: I receive 0 rows for the filtered tables. When changing the funktion to

ALTER FUNCTION [dbo].[fx_ConvertHostToEntity] ()
returns uniqueidentifier
as
Begin
declare @.host nvarchar(50)
set @.host = host_name()
declare @.entity uniqueidentifier
set @.entity = cast( convert(varbinary, @.host) as uniqueidentifier)
-- return @.entity
return '4bb0e468-c68a-4253-ba82-f71c3a6e302d'
End

I get the rows in the replicated table. So the problem must be somewhere either in the creation of the sanpshots or the resolution of the hostname

|||

Using Replication Monitor for the publication I also found out using the properties of the publication that the snapshot for the data partition has not been created. So I did this manually and will code it later on. When replicating again I got the follwowing error shown up in the Replication Monitor (I left out the first one as I am not using web sync right now):

Partitioned snapshot validation failed for this Subscriber. The snapshot validation token stored in the specified partitioned snapshot location does not match the value '{00620034-0062-0030-6500-340036003800}' used by the Merge Agent when evaluating the parameterized filter function. If specifying the location of the partitioned snapshot (using -DynamicSnapshotLocation), you must ensure that the snapshot files in that directory belong to the correct partition or allow the Merge Agent to automatically detec (Source: MSSQL_REPL, Error number: MSSQL_REPL27223)

Find the full source code below:

Imports Microsoft.SqlServer.Replication

Imports Microsoft.SqlServer.Management.Common

Public Class Replication

Private subscriberName As String

Private publisherName As String

Private windowsLogin As String

Private windowsPWD As String

Private publicationName As String

Private publicationDbName As String

Private subscriptionDbName As String

Private passedHostname As String

''' <summary>

''' a new instance of the replication object

''' </summary>

''' <param name="EntityID">id of the entity as string</param>

''' <param name="SubscriberHost">the hostname of the subscribing sql instance</param>

''' <param name="PublisherHost">the hostname of the publishing sql instance</param>

''' <param name="Login">the name of the windows login to authenticate towards the publisher: domain\user</param>

''' <param name="PWD">the password</param>

''' <param name="Publication">the name of the puplicaiton on the publisher</param>

''' <param name="PublicationDB">the name of the puplication db</param>

''' <param name="SubscriptionDB">the name of the subscription db</param>

''' <remarks></remarks>

Sub New(ByVal EntityID As Guid, ByVal SubscriberHost As String, ByVal PublisherHost As String, ByVal Login As String, ByVal PWD As String, ByVal Publication As String, ByVal PublicationDB As String, ByVal SubscriptionDB As String)

subscriberName = SubscriberHost

publisherName = PublisherHost

'the guid of the entity is passed as hostname to be used for filtering

PassedHostname = EntityID.ToString

windowsLogin = Login

windowsPWD = PWD

publicationName = Publication

subscriptionDbName = SubscriptionDB

publicationDbName = PublicationDB

End Sub

Sub SetupPullSubscription()

'Create connections to the Publisher and Subscriber.

Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)

Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.

Dim publication As MergePublication

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

subscriberConn.Connect()

' Ensure that the publication exists and that

' it supports pull subscriptions.

publication = New MergePublication()

publication.Name = publicationName

publication.DatabaseName = publicationDbName

publication.ConnectionContext = publisherConn

If publication.LoadProperties() Then

If (publication.Attributes And PublicationAttributes.AllowPull) = 0 Then

publication.Attributes = publication.Attributes Or PublicationAttributes.AllowPull

End If

' Define the pull subscription.

subscription = New MergePullSubscription()

subscription.ConnectionContext = subscriberConn

subscription.PublisherName = publisherName

subscription.PublicationName = publicationName

subscription.PublicationDBName = publicationDbName

subscription.DatabaseName = subscriptionDbName

subscription.HostName = passedHostname

' Specify the Windows login credentials for the Merge Agent job.

subscription.SynchronizationAgentProcessSecurity.Login = windowsLogin

subscription.SynchronizationAgentProcessSecurity.Password = windowsPWD

' Make sure that the agent job for the subscription is created.

subscription.CreateSyncAgentByDefault = True

' Create the pull subscription at the Subscriber.

subscription.Create()

Dim registered As Boolean = False

' Verify that the subscription is not already registered.

For Each existing As MergeSubscription In _

publication.EnumSubscriptions()

If existing.SubscriberName = subscriberName Then

registered = True

End If

Next

If Not registered Then

' Register the local subscription with the Publisher.

publication.MakePullSubscriptionWellKnown(subscriberName, subscriptionDbName, SubscriptionSyncType.Automatic, MergeSubscriberType.Local, 0)

'publication.MakePullSubscriptionWellKnown(subscriberName, subscriptionDbName, SubscriptionSyncType.Automatic, MergeSubscriberType.Local, 0)

End If

Else

' Do something here if the publication does not exist.

Throw New ApplicationException(String.Format("The publication '{0}' does not exist on {1}.", publicationName, publisherName))

End If

Catch ex As Exception

' Implement the appropriate error handling here.

Throw New ApplicationException(String.Format("The subscription to {0} could not be created.", publicationName), ex)

Finally

subscriberConn.Disconnect()

publisherConn.Disconnect()

End Try

End Sub

Sub PullMergeReplication()

' Create a connection to the Subscriber.

Dim conn As ServerConnection = New ServerConnection(subscriberName)

Dim subscription As MergePullSubscription

Try

' Connect to the Subscriber.

conn.Connect()

' Define subscription properties.

subscription = New MergePullSubscription()

subscription.ConnectionContext = conn

subscription.DatabaseName = subscriptionDbName

subscription.PublisherName = publisherName

subscription.PublicationDBName = publicationDbName

subscription.PublicationName = publicationName

' If the pull subscription and the job exists, start the agent job.

If subscription.LoadProperties() And Not subscription.AgentJobId Is Nothing Then

subscription.SynchronizeWithJob()

Else

' Do something here if the subscription does not exist.

Throw New ApplicationException(String.Format("A subscription to '{0}' does not exists on {1}", publicationName, subscriberName))

End If

Catch ex As Exception

' Do appropriate error handling here.

Throw New ApplicationException("The subscription could not be synchronized.", ex)

Finally

conn.Disconnect()

End Try

End Sub

End Class

|||

this means you're trying to apply a dynamic snapshot that's not for your partition. i.e. your filter is for 'StoreA', but you're trying to apply snapshot belonging to 'StoreB'.

|||

After finding no solution to the problem, feeling that the problem has something to to with the conversion vom guid to string, I decided to have a second col in the table that was filled with a trigger: the guid converted to nvarchar.

And suddenly I knew where the problem was hiding:

hostname value: 4bb0e468-c68a-4253-ba82-f71c3a6e302d

cast( convert(varbinary,host_name()) as uniqueidentifier) -> 4BB0E468-C68A-4253-BA82-F71C3A6E302D

And that is the solution: When using a guid as the hostname and filter, the values have to be either both lower case or upper case. Otherwise you will get an empty result for dynamicly filtered tables :)

Thanks for your support

Alex

Merge Replication Problem

We have a merge replication database with a publisher server and a distribution server. We made and publication with dynamic filters. We are using a pull suscriptor wich was installed with a backup from the publisher database.
First time we made it works we had no errors and no conflicts. However, later we had troubles with the suscriptor, it was trying to delete rows from some tables that we didn't modified, and it produced some conflicts because referential integrity on the
publisher database stopped it.
We didn't apply a initial snapshot because it had the same data of the publisher and we are using @.pre_creation_cmd = truncate but the suscription doesn't ignore it (suscription has the schema and data).
Deletes on this tables are imposible however the suscriptor always try to do them
What can I do to prevent this problem?
Is it necessary to apply always the initial snapshot and how can I do it if there is referential integrity among the tables into the publication?
Thanks.
you should put the NFR option on all constraints and relationships on the
publisher and subscriber.
This should solve most of the problems related to DRI relationships.
What is happening is that DRI is enforced on the Publisher as two (or more
statements). These changes are then replicated to the Subscriber. The
initiating transaction is executed on the Subscriber where the DRI
relationship is applied. Then the next statement where DRI was enforced on
the publisher makes its way to the subscriber where it is attempted to be
applied again. This is typically where your conflict occurs.
"Heiner Morales" <anonymous@.discussions.microsoft.com> wrote in message
news:5CDDA4F6-C4AD-4A33-A426-0AA554A54DD7@.microsoft.com...
> We have a merge replication database with a publisher server and a
distribution server. We made and publication with dynamic filters. We are
using a pull suscriptor wich was installed with a backup from the publisher
database.
> First time we made it works we had no errors and no conflicts. However,
later we had troubles with the suscriptor, it was trying to delete rows from
some tables that we didn't modified, and it produced some conflicts because
referential integrity on the publisher database stopped it.
> We didn't apply a initial snapshot because it had the same data of the
publisher and we are using @.pre_creation_cmd = truncate but the suscription
doesn't ignore it (suscription has the schema and data).
> Deletes on this tables are imposible however the suscriptor always try to
do them
> What can I do to prevent this problem?
> Is it necessary to apply always the initial snapshot and how can I do it
if there is referential integrity among the tables into the publication?
> Thanks.
>
sql

Merge replication performance problems

Hi,
I have a merge replication between MSSQL2005 as a server, and SQLExpress
as the clients. The publication contains dynamic filters, ~190 tables and
~300 joins (the longest path in a "join tree" is 12), currently I'm testing
on database that contains ~2GB of data. If I use a precomputed partitions
- it seems to work, but it just kills the performance of the back-end application;
If I don't use precomputed partitions it constantly fails (during subscriber's
synchronization) with "The merge process failed to enumerate changes in articles
with parameterized row filters... Query timeout expired..." error. The problem
is that there's no additional info - so I don't even know what was the problematic
query... Please help
I would consider trying to use static filters if you have a smallish number
of subscribers. You'll need a separate publication for each subscriber but
the performance is much improved. Apart from that you can increase the
querytimeout value as a possibility and use logging if this doesn't work.
Cheers,
Paul Ibison
|||Hello Paul,
Thanks for the answer, I'd like to clarify couple of things:
1. What do you mean by smallish number? less than 10? or less than 100 is
OK too?
2. I've increased the querytimeout for the agent (which didn't help). Can
you please provide me the details for the logging part? With the highest
-HistoryVerboseLevel I still can't see the exact sqls that are executed,
so I have no idea where the timeout error is coming from; and in SQLProfiler
all I see is the calls to sp_setupbelongs procedure...
Thanks again,
Vladimir Kofman

> I would consider trying to use static filters if you have a smallish
> number
> of subscribers. You'll need a separate publication for each subscriber
> but
> the performance is much improved. Apart from that you can increase the
> querytimeout value as a possibility and use logging if this doesn't
> work.
> Cheers,
> Paul Ibison
|||This'll help for logging: http://support.microsoft.com/Default.aspx?id=312292
The reason I mentioned a smallish number of subscribers as a distinction was
purely practical. If you have the time to set up 100 publications with static
filters then I'd definitely recommend this over dynamic filters.
HTH,
Paul Ibison

Merge Replication Error - A dynamic snapshot is required

Any info on this error:
Scenario:
Replication Server: Sql Server 2005
Replication client: SQL Server Express Edition
I have setup a merge publication on SQL Server 2005, with a dynamic
filter (using HOST_NAME()) defined on one of the articles, also I have
defined a logical record.
(To be precise it is a logical record without join filter, I have done
this using 'sp_addmergefilter' and specifying the @.filter_type = 2
value which configures it just as logical record without a join filter)
Subscription (am using push) to this Publication is successful, but
when I see Replication Monitor, I get the following error there:
A dynamic snapshot is required because the publication 'XYZ' contains
both dynamic filters and logical record relationships.
How do i configure dynamic snapshot? Any info this error would be great.
I tried the following stored procedure to add a dynamic snapshot:
exec sp_adddynamicsnapshot_job
@.publication = 'XYZ',
@.suser_sname = NULL,
@.host_name = sysname,
@.frequency_type= 2
but am still getting the same error "A dynamic snapshot is required
because the publication 'XYZ' contains
both dynamic filters and logical record relationships. "
On Jan 25, 4:25 pm, narasimha...@.gmail.com wrote:
> Any info on this error:
> Scenario:ReplicationServer: Sql Server 2005Replicationclient: SQL Server Express Edition
> I have setup amergepublication on SQL Server 2005, with a dynamic
> filter (using HOST_NAME()) defined on one of the articles, also I have
> defined a logical record.
> (To be precise it is a logical record without join filter, I have done
> this using 'sp_addmergefilter' and specifying the @.filter_type = 2
> value which configures it just as logical record without a join filter)
> Subscription (am using push) to this Publication is successful, but
> when I seeReplicationMonitor, I get the following error there:
> Adynamic snapshotis required because the publication 'XYZ' contains
> both dynamic filters and logical record relationships.
> How do i configuredynamic snapshot? Any info this error would be great.

Merge Replication Conditional Filter with UDF Problem

I'm performing Merge Replication between SQL Server 2000/SQL CE using Dynamic Filters.
I would like a conditional filter based on the Host_Name(). I've written and tested a UDF
in Query Analyzer called SELECT_Stores_RepID. In the Dynamic Filters properties for the
Stores Table I've tried many different things such as:
SELECT <published_columns> FROM [dbo].[Stores] WHERE dbo.SELECT_Stores_RepID (HOST_NAME())
All them give me errors. My question is what is the correct syntax to use my UDF? The documentation
on this is very poor.
Cheers
it should be something like this
select <punished_columns> from <<Table>> where au_id in (select au_id from dbo.Authors_filter('CA'))
There may be more efficient ways of doing this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi Hilary,
Thanks for the help. I tried to apply it to my Dynamic Filter...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.StoreID IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
but I'm receiving the following error...
Incorrect Syntax near '('
Once again, I can't see where the problem is with this statment ?
|||Sorry typo in my post ...
(SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
Should be (extra bracket)...
(SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME()))
Still getting the same problem as before.
-- Cube wrote: --
Hi Hilary,
Thanks for the help. I tried to apply it to my Dynamic Filter...
SELECT <published_columns> FROM [dbo].[Stores] WHERE
Stores.StoreID IN (SELECT StoreID FROM dbo.SELECT_Stores_RepID(HOST_NAME())
but I'm receiving the following error...
Incorrect Syntax near '('
Once again, I can't see where the problem is with this statment ?
|||can you post your UDF.
BTW - I should warn you that UDF's don't always offer the best performance. Also Host_Name() is evaluated where the merge agent is run. If you kick it off on EM runing it on your desktop it will evaluated to your machine name.
If you kick if off on the subscriber as a pull subscription it will resolve correctly.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||I'm still getting this error in Replication. Does anyone know if its a known issue?
The error is that when I call my UDF below in my Dynamic Filter and pass in
HOST_NAME() I get an incorrect syntax near '(' error.
Ciaran
-- Cube wrote: --
Hi Hilary,
Please find below my UDF. The RepID in my table is an int, which I CAST so I can
compare it to my Host_Name(). I'm interested to know your opinion. Also, if UDF's
don't offer best performance, do I have other options apart from creating a seperate
publication?
ALTER FUNCTION dbo.SELECT_Stores_RepID
(
@.inRepIDchar(4)
)
RETURNS @.Stores TABLE (StoreID int)
AS
BEGIN
IF (@.inRepID = '8')
INSERT INTO @.Stores
SELECTStoreID FROM Stores
ELSE
INSERT INTO @.Stores
SELECTStoreID
FROM Stores
WHERE CAST(Stores.RepID AS char(4)) = @.inRepID
RETURN
END
BTW - Some history behind all this. I have some HandHeld Devices deployed with my custom app.
The Replication depends on the RepID, which is set through the Host_Name() on the device, pulling
back the users specified data. Now, there is a requirement to pull back all data in some cases
e.g. management, without modifying the software on the client. Henece, the reasons for the
IF RepID = X conditional filter.

Merge replication between two publishers with dynamic filters

Hello,
I am working on a distributed database system in which each site is a
publisher of a filtered set of data. It is necessary that a publisher can
subscribe to another publisher.
I am using merge replication, dynamic filters and push subscriptions. Each
site publishes the same tables with another filter.
When I test this scenario, I notice that the merge agent replicates only the
changes of the publisher to the subscriber. A change on the subscriber (thas
has also a publication on the same tables) is not replicated to the publisher.
Does someone know a solution for this?
Is it possible to have (bidirectional) merge replication between two
publishers with dynamic filters?
I hope someone can help me with this.
thanks in advance!
Marco Broenink
Could you please describe in more detail your publisher subscriber
configurations and the filters.
If the table has a column that you are using to filter from Publisher to
Subscriber and then make this subscriber a republisher and then try to use
the same column as the filter column, there is only set of data at the
republisher/subscriber.
I am not clear on your setup. Could you please repost with more elaborate
setup steps?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> Hello,
> I am working on a distributed database system in which each site is a
> publisher of a filtered set of data. It is necessary that a publisher can
> subscribe to another publisher.
> I am using merge replication, dynamic filters and push subscriptions. Each
> site publishes the same tables with another filter.
> When I test this scenario, I notice that the merge agent replicates only
the
> changes of the publisher to the subscriber. A change on the subscriber
(thas
> has also a publication on the same tables) is not replicated to the
publisher.
> Does someone know a solution for this?
> Is it possible to have (bidirectional) merge replication between two
> publishers with dynamic filters?
> I hope someone can help me with this.
> thanks in advance!
> Marco Broenink
|||Thanks for your response!
I am using a dynamic filter. This filter uses a function. This function
needs the hostname and a filter-column to dermine if the row needs to be
filtered. The filter looks like:
SELECT <published_columns> FROM [dbo].[PublishedTable]
WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
The filterfunction uses a mapping table that maps the contents of the
[FilterColumn] to hostnames.
With this mapping table, each publisher publishes its own part of all data.
So the publications of two publishers do not overlap. But the publications
are on the same tables.
Problem with this configuration is that changes of a subscriber are not
replicated to the publisher. It looks like that the subscriber's own
publication is blocking this.
I hope you can help me with this.
greetings, Marco.
"Mahesh [MSFT]" wrote:

> Could you please describe in more detail your publisher subscriber
> configurations and the filters.
> If the table has a column that you are using to filter from Publisher to
> Subscriber and then make this subscriber a republisher and then try to use
> the same column as the filter column, there is only set of data at the
> republisher/subscriber.
> I am not clear on your setup. Could you please repost with more elaborate
> setup steps?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:CD4D84B8-A940-46BF-AC9A-5FF5098D2200@.microsoft.com...
> the
> (thas
> publisher.
>
>
|||I have used 'global' subscriptions in stead of 'local' and this problem is
solved.
Now the changes are also replicated from subscriber to publisher.
Unfortunately, I have a new problem.
I use replication with dynamic filters. In my system it is possible that
data is added to the subscriber that doesnot pass the filter. When
replicating, this data is deleted at the subscriber and added to the
publisher.
How can I prevent this delete & insert ?
thanks in advance, Marco
"Marco Broenink" wrote:
[vbcol=seagreen]
> Thanks for your response!
> I am using a dynamic filter. This filter uses a function. This function
> needs the hostname and a filter-column to dermine if the row needs to be
> filtered. The filter looks like:
> SELECT <published_columns> FROM [dbo].[PublishedTable]
> WHERE 1 = [dbo].[fn_DynamicFilter]([FilterColumn], HOST_NAME())
> The filterfunction uses a mapping table that maps the contents of the
> [FilterColumn] to hostnames.
> With this mapping table, each publisher publishes its own part of all data.
> So the publications of two publishers do not overlap. But the publications
> are on the same tables.
> Problem with this configuration is that changes of a subscriber are not
> replicated to the publisher. It looks like that the subscriber's own
> publication is blocking this.
> I hope you can help me with this.
> greetings, Marco.
> "Mahesh [MSFT]" wrote:
|||Glad that you could work around your first problem, though to be frank, I am
still unclear of the setup.
Regarding your new problem,
If each subscriber inserts data that corresponds to only its subset of data
then you could try using a default of some kind to the tables. Like hostname
or something that will map appropriately to the filter condition and make it
pass. So everytime an insert happens at the subscriber, the filter condition
is met and then is successfully propagated to the publisher and does not get
deleted at the subscriber in turn.
Please note that this can work only if the subscriber always makes
"good" inserts, that is to say that the subscriber never expects to insert
data (that does not satisfy the filter) and then in turn expects the data to
be deleted by the publisher.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...[vbcol=seagreen]
> I have used 'global' subscriptions in stead of 'local' and this problem is
> solved.
> Now the changes are also replicated from subscriber to publisher.
> Unfortunately, I have a new problem.
> I use replication with dynamic filters. In my system it is possible that
> data is added to the subscriber that doesnot pass the filter. When
> replicating, this data is deleted at the subscriber and added to the
> publisher.
> How can I prevent this delete & insert ?
> thanks in advance, Marco
>
> "Marco Broenink" wrote:
data.[vbcol=seagreen]
publications[vbcol=seagreen]
to[vbcol=seagreen]
use[vbcol=seagreen]
elaborate[vbcol=seagreen]
message[vbcol=seagreen]
a[vbcol=seagreen]
publisher can[vbcol=seagreen]
subscriptions. Each[vbcol=seagreen]
only[vbcol=seagreen]
subscriber[vbcol=seagreen]
|||thanks again for the response.
In my topology, I have different publishers of the same table. These
publishers use different filters. A subscriber can be subscribed to different
publishers.
For example:
Site A publishes table1
Site B publishes table1
Site C is subscribed to Site A table1. This subscribtion is filtered with a
dynamic filter F1.
Site C is also subscribed to Site B table1. This subscribtion is filtered
with another dynamic filter F2.
The different dynamic filters make sure that the subscription to Site A do
not overlap the subscription to Site B.
Thus: The table1 of C contains a subset of table1 of A and a subset of
table1 of B.
So: table1 of C contains two types of data:
- data that meets filtercondition F1 and doesnot meet filtercondition F2.
- data that meets filtercondition F2 and doesnot meet filtercondition F1.
So the problem is: The subscriber will contain data that doesnot meet one of
the filterconditions. When replicating to Site A (filter F1), data of filter
F2 is deleted. When replicating to Site B (filter F2), data of filter F1 is
deleted.
So in this scenario, I think it is not possible to make only 'good' inserts
because it violates always one of the two filtersconditions.
I hope you know a solution. Or am I trying to do something impossible?
greetings, Marco Broenink
"Mahesh [MSFT]" wrote:

> Glad that you could work around your first problem, though to be frank, I am
> still unclear of the setup.
> Regarding your new problem,
> If each subscriber inserts data that corresponds to only its subset of data
> then you could try using a default of some kind to the tables. Like hostname
> or something that will map appropriately to the filter condition and make it
> pass. So everytime an insert happens at the subscriber, the filter condition
> is met and then is successfully propagated to the publisher and does not get
> deleted at the subscriber in turn.
> Please note that this can work only if the subscriber always makes
> "good" inserts, that is to say that the subscriber never expects to insert
> data (that does not satisfy the filter) and then in turn expects the data to
> be deleted by the publisher.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:664513B1-B610-453F-B033-6FD1B8720BE1@.microsoft.com...
> data.
> publications
> to
> use
> elaborate
> message
> a
> publisher can
> subscriptions. Each
> only
> subscriber
>
>
|||Hi Marco,
Please correct me if I understood wrong:
So what you are saying is SiteA and SiteB are publishing the same tables,
but are not replicating to each other. Is that right?
But in turn are replicating that table to SiteC.
This is not supported.
In the first place, When SiteC subscribed to SiteA, it gets the table from
SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
configure? Did you configure a no-sync subscription? If not, and you used
all the default settings then actually you will not even be able to complete
the subscription because the table at SiteC (got from SiteA) will be
attempted to drop and recreate with the scripts from SiteB which will fail.
If you want to do what you are trying to do, one solution is to have table1
at SiteA and replicate it to SiteC with the proper filter. Have table2 at
SiteB and replicate that to SiteC with the proper filter.
On the subscriber you can have a view on those two tables that will give you
a combined view for the results. But you may not be able to make DMLs on the
view directly. You will still need to do the DMLs on the actual tables.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> thanks again for the response.
> In my topology, I have different publishers of the same table. These
> publishers use different filters. A subscriber can be subscribed to
different
> publishers.
> For example:
> Site A publishes table1
> Site B publishes table1
> Site C is subscribed to Site A table1. This subscribtion is filtered with
a
> dynamic filter F1.
> Site C is also subscribed to Site B table1. This subscribtion is filtered
> with another dynamic filter F2.
> The different dynamic filters make sure that the subscription to Site A do
> not overlap the subscription to Site B.
> Thus: The table1 of C contains a subset of table1 of A and a subset of
> table1 of B.
> So: table1 of C contains two types of data:
> - data that meets filtercondition F1 and doesnot meet filtercondition F2.
> - data that meets filtercondition F2 and doesnot meet filtercondition F1.
> So the problem is: The subscriber will contain data that doesnot meet one
of
> the filterconditions. When replicating to Site A (filter F1), data of
filter
> F2 is deleted. When replicating to Site B (filter F2), data of filter F1
is
> deleted.
> So in this scenario, I think it is not possible to make only 'good'
inserts[vbcol=seagreen]
> because it violates always one of the two filtersconditions.
> I hope you know a solution. Or am I trying to do something impossible?
> greetings, Marco Broenink
>
> "Mahesh [MSFT]" wrote:
I am[vbcol=seagreen]
data[vbcol=seagreen]
hostname[vbcol=seagreen]
make it[vbcol=seagreen]
condition[vbcol=seagreen]
get[vbcol=seagreen]
insert[vbcol=seagreen]
data to[vbcol=seagreen]
message[vbcol=seagreen]
problem is[vbcol=seagreen]
that[vbcol=seagreen]
function[vbcol=seagreen]
to be[vbcol=seagreen]
the[vbcol=seagreen]
all[vbcol=seagreen]
not[vbcol=seagreen]
Publisher[vbcol=seagreen]
try to[vbcol=seagreen]
the[vbcol=seagreen]
no[vbcol=seagreen]
in[vbcol=seagreen]
is[vbcol=seagreen]
replicates[vbcol=seagreen]
the[vbcol=seagreen]
two[vbcol=seagreen]
|||thanks for the help!
Marco
"Mahesh [MSFT]" wrote:

> Hi Marco,
> Please correct me if I understood wrong:
> So what you are saying is SiteA and SiteB are publishing the same tables,
> but are not replicating to each other. Is that right?
> But in turn are replicating that table to SiteC.
> This is not supported.
> In the first place, When SiteC subscribed to SiteA, it gets the table from
> SiteA. Now when you configure SiteC to subscribe from SiteB, how did you
> configure? Did you configure a no-sync subscription? If not, and you used
> all the default settings then actually you will not even be able to complete
> the subscription because the table at SiteC (got from SiteA) will be
> attempted to drop and recreate with the scripts from SiteB which will fail.
> If you want to do what you are trying to do, one solution is to have table1
> at SiteA and replicate it to SiteC with the proper filter. Have table2 at
> SiteB and replicate that to SiteC with the proper filter.
> On the subscriber you can have a view on those two tables that will give you
> a combined view for the results. But you may not be able to make DMLs on the
> view directly. You will still need to do the DMLs on the actual tables.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
> news:BADB7E44-D9C9-4333-B69E-9F5B33314BEF@.microsoft.com...
> different
> a
> of
> filter
> is
> inserts
> I am
> data
> hostname
> make it
> condition
> get
> insert
> data to
> message
> problem is
> that
> function
> to be
> the
> all
> not
> Publisher
> try to
> the
> no
> in
> is
> replicates
> the
> two
>
>

Merge Replication and transactions

I have one table in Merge Replication with dynamic filtering and
exchangetype=2 (from publisher to subscriber only), with Merge Agent
scheduled daily (night)
In this table I insert tipically some rows between two agent's run and I
would like to replicate each row as single transaction (i.e. what's happen if
I must to replicate three rows to a subscriber in single agent run but after
replication of first row network link between publisher and subscriber goes
down?).
Can I obtain this with three insert with three transactions on publisher
table (one insert, one transaction)?
Thanks in advance
Alexago,
unlike transactional, merge replication doesn't really have a concept of
transactions. Generation numbers are used as a logical clock, but this is a
different mechanish (see BOL). If the network link goes down, you can just
restart the merge agent and it will continue where it left off. If you are
concerned about the article processing order, have a look at this article:
http://support.microsoft.com/default.aspx?scid=kb;[LN];307356
HTH,
Paul Ibison

Merge replication and dynamic filters question

Hi!
I have a schema that looks like this, on my main server:
tableFiles:
FileID (PK)
FileName
table FileProperties
PropertyID
FileID (FK Files.FileID) (PK)
PropertyName (PK)
PropertyValue
table Hosts_Files
HostName (PK)
FileID (PK)
Now what I want to achieve is this:
There are many clients that use this database. Sometimes they go
offline and take some of the files with them to work offline.
When this occurs, I want to replicate the entire table Files, and only
the rows from FileProperties that have to do with the files they
downloaded to their computer (marked so in Hosts_Files).
Can I make this work with merge replication? I tried but the dynamic
filters I can write are either JOIN or WHERE (or am I wrong?) so I
couldn't manage to do a join between FileProperties and Hosts_Files
only where Hosts_Files.HostName = host_name().
Thanks in advance for answering,
Zzzbla
You could join these tables in the merge publication. Filtering in this case
is a bit tricky as it looks as though you want to change the filter
effectively dynamically. I'm assuming that there is a typo somewhere, as
this doesn't seem to make logical sense: "I want to replicate the entire
table Files, and only the rows from FileProperties that have to do with the
files they downloaded to their computer" - this would seem to imply all
FileProperties. The way I read your requirements are that you want users to
select certain files and have just associated details downloaded? If this is
the case, you can have a further table which relates fileids to hostnames.
Selecting to download a file will cause an insert of a record in this new
table. All tables would be joined on fileid, and the dynamic filter would be
solely to the new table.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
I forgot to mention that the rows in the files table correlate to
physical files. So what I wanted is to have some *physical* files
downloaded to the client's computer, all of the rows in the files table
(the client may have to see or use data about files that he didn't
download) and only the rows in the fileproperties table that are
related to the few rows in files table that are related to the physical
files the user downloaded.
I know I can join the tables - FileProperties and the table that
associates the fileid to the hostname - but when I also need to use
WHERE Hosts_Files.HostName = host_name(). I tried to do it but the
replication wizard doesn't let me.
If you could provide me with a script or an example solution (or is it
just a view that I have to write in order to combine JOIN and WHERE
filters?), I'll be grateful.
Thanks in advance,
Zzzbla
Paul Ibison wrote:

> You could join these tables in the merge publication. Filtering in
> this case is a bit tricky as it looks as though you want to change
the
> filter effectively dynamically. I'm assuming that there is a typo
> somewhere, as this doesn't seem to make logical sense: "I want to
> replicate the entire table Files, and only the rows from
> fileProperties that have to do with the files they downloaded to
their
> computer" - this would seem to imply all FileProperties. The way I
> read your requirements are that you want users to select certain
files
> and have just associated details downloaded? If this is the case,
you
> can have a further table which relates fileids to hostnames.
Selecting
> to download a file will cause an insert of a record in this new
table.
> All tables would be joined on fileid, and the dynamic filter would be
> solely to the new table.
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Some additions:
The real problem I'm facing is that the table I want to filter by
(Hosts_Files) is only related to the table I want to filter
(FileProperties) via the table Files - which I want to leave unfiltered.
If I let it filter the files table, I get the FileProperties I wanted,
but I also get only the selected rows from files and not the whole
table as required.
I wouldn't want to create a Hosts_FileProperties unless there's no
better solution...
|||Please post up the schema of hte tables involved and I'll have a look. A
(denormalized) linking table will work (maintained by triggers) but there
might be an alternative.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
I attached a script to create tables and relationships.
Thank you for your time and effort!
begin 444 replication test.sql
M__YI`&8`(`!E`'@.`:0!S`'0`<P`@.`"@.`<P!E`&P`90!C`'0`( ``J`"``9@.!R
M`&\`;0`@.`&0`8@.!O`"X`<P!Y`',`;P!B`&H`90!C`'0`<P`@.` '<`:`!E`'(`
M90`@.`&D`9``@.`#T`(`!O`&(`:@.!E`&,`=`!?`&D`9``H`$X`) P!;`&0`8@.!O
M`%T`+@.!;`$8`2P!?`$8`:0!L`&4`4`!R`&\`<`!E`'(`=`!I` &4`<P!?`$8`
M:0!L`&4`<P!=`"<`*0`@.`&$`;@.!D`"``3P!"`$H`10!#`%0`4 `!2`$\`4`!%
M`%(`5`!9`"@.`:0!D`"P`(`!.`"<`20!S`$8`;P!R`&4`:0!G` &X`2P!E`'D`
M)P`I`"``/0`@.`#$`*0`-``H`00!,`%0`10!2`"``5`!!`$(`3`!%`"``6P!D
M`&(`;P!=`"X`6P!&`&D`;`!E`%``<@.!O`'``90!R`'0`:0!E` ',`70`@.`$0`
M4@.!/`%``(`!#`$\`3@.!3`%0`4@.!!`$D`3@.!4`"``1@.!+`%\`1@.!I`& P`90!0
M`'(`;P!P`&4`<@.!T`&D`90!S`%\`1@.!I`&P`90!S``T`"@.!'` $\`#0`*``T`
M"@.!I`&8`(`!E`'@.`:0!S`'0`<P`@.`"@.`<P!E`&P`90!C`'0`( ``J`"``9@.!R
M`&\`;0`@.`&0`8@.!O`"X`<P!Y`',`;P!B`&H`90!C`'0`<P`@.` '<`:`!E`'(`
M90`@.`&D`9``@.`#T`(`!O`&(`:@.!E`&,`=`!?`&D`9``H`$X`) P!;`&0`8@.!O
M`%T`+@.!;`$8`2P!?`$@.`;P!S`'0`<P!?`$8`:0!L`&4`<P!?` $8`:0!L`&4`
M<P!=`"<`*0`@.`&$`;@.!D`"``3P!"`$H`10!#`%0`4`!2`$\`4 `!%`%(`5`!9
M`"@.`:0!D`"P`(`!.`"<`20!S`$8`;P!R`&4`:0!G`&X`2P!E` 'D`)P`I`"``
M/0`@.`#$`*0`-``H`00!,`%0`10!2`"``5`!!`$(`3`!%`"``6P!D`&(`;P!=
M`"X`6P!(`&\`<P!T`',`7P!&`&D`;`!E`',`70`@.`$0`4@.! /`%``(`!#`$\`
M3@.!3`%0`4@.!!`$D`3@.!4`"``1@.!+`%\`2`!O`',`=`!S`%\`1 @.!I`&P`90!S
M`%\`1@.!I`&P`90!S``T`"@.!'`$\`#0`*``T`"@.!I`&8`(`!E` '@.`:0!S`'0`
M<P`@.`"@.`<P!E`&P`90!C`'0`(``J`"``9@.!R`&\`;0`@.`&0`8 @.!O`"X`<P!Y
M`',`;P!B`&H`90!C`'0`<P`@.`'<`:`!E`'(`90`@.`&D`9``@.` #T`(`!O`&(`
M:@.!E`&,`=`!?`&D`9``H`$X`)P!;`&0`8@.!O`%T`+@.!;`$8`: 0!L`&4`4`!R
M`&\`<`!E`'(`=`!I`&4`<P!=`"<`*0`@.`&$`;@.!D`"``3P!"` $H`10!#`%0`
M4`!2`$\`4`!%`%(`5`!9`"@.`:0!D`"P`(`!.`"<`20!S`%4`< P!E`'(`5`!A
M`&(`;`!E`"<`*0`@.`#T`(``Q`"D`#0`*`&0`<@.!O`'``(`!T` &$`8@.!L`&4`
M(`!;`&0`8@.!O`%T`+@.!;`$8`:0!L`&4`4`!R`&\`<`!E`'(`= `!I`&4`<P!=
M``T`"@.!'`$\`#0`*``T`"@.!I`&8`(`!E`'@.`:0!S`'0`<P`@.` "@.`<P!E`&P`
M90!C`'0`(``J`"``9@.!R`&\`;0`@.`&0`8@.!O`"X`<P!Y`',`; P!B`&H`90!C
M`'0`<P`@.`'<`:`!E`'(`90`@.`&D`9``@.`#T`(`!O`&(`:@.!E` &,`=`!?`&D`
M9``H`$X`)P!;`&0`8@.!O`%T`+@.!;`$8`:0!L`&4`<P!=`"<`* 0`@.`&$`;@.!D
M`"``3P!"`$H`10!#`%0`4`!2`$\`4`!%`%(`5`!9`"@.`:0!D` "P`(`!.`"<`
M20!S`%4`<P!E`'(`5`!A`&(`;`!E`"<`*0`@.`#T`(``Q`"D`# 0`*`&0`<@.!O
M`'``(`!T`&$`8@.!L`&4`(`!;`&0`8@.!O`%T`+@.!;`$8`:0!L` &4`<P!=``T`
M"@.!'`$\`#0`*``T`"@.!I`&8`(`!E`'@.`:0!S`'0`<P`@.`"@.`< P!E`&P`90!C
M`'0`(``J`"``9@.!R`&\`;0`@.`&0`8@.!O`"X`<P!Y`',`;P!B` &H`90!C`'0`
M<P`@.`'<`:`!E`'(`90`@.`&D`9``@.`#T`(`!O`&(`:@.!E`&,`= `!?`&D`9``H
M`$X`)P!;`&0`8@.!O`%T`+@.!;`$@.`;P!S`'0`<P!?`$8`:0!L` &4`<P!=`"<`
M*0`@.`&$`;@.!D`"``3P!"`$H`10!#`%0`4`!2`$\`4`!%`%(`5 `!9`"@.`:0!D
M`"P`(`!.`"<`20!S`%4`<P!E`'(`5`!A`&(`;`!E`"<`*0`@.` #T`(``Q`"D`
M#0`*`&0`<@.!O`'``(`!T`&$`8@.!L`&4`(`!;`&0`8@.!O`%T`+ @.!;`$@.`;P!S
M`'0`<P!?`$8`:0!L`&4`<P!=``T`"@.!'`$\`#0`*``T`"@.!#` %(`10!!`%0`
M10`@.`%0`00!"`$P`10`@.`%L`9`!B`&\`70`N`%L`1@.!I`&P`9 0!0`'(`;P!P
M`&4`<@.!T`&D`90!S`%T`(``H``T`"@.`)`%L`4`!R`&\`<`!E` '(`=`!Y`$D`
M1`!=`"``6P!I`&X`=`!=`"``20!$`$4`3@.!4`$D`5`!9`"``* ``Q`"P`(``Q
M`"D`(`!.`$\`5``@.`$X`50!,`$P`(``L``T`"@.`)`%L`1@.!I` &P`90!)`$0`
M70`@.`%L`:0!N`'0`70`@.`$X`3P!4`"``3@.!5`$P`3``@.`"P`# 0`*``D`6P!0
M`'(`;P!P`&4`<@.!T`'D`3@.!A`&T`90!=`"``6P!N`'8`80!R` &,`:`!A`'(`
M70`@.`"@.`-0`P`"D`(`!#`$\`3`!,`$$`5`!%`"``2`!E`&(`<@.!E`'<`7P! #
M`$D`7P!!`%,`(`!.`$\`5``@.`$X`50!,`$P`(``L``T`"@.`)` %L`4`!R`&\`
M<`!E`'(`=`!Y`%8`80!L`'4`90!=`"``6P!N`'8`80!R`&,`: `!A`'(`70`@.
M`"@.`,0`P`#``*0`@.`$,`3P!,`$P`00!4`$4`(`!(`&4`8@.!R` &4`=P!?`$,`
M20!?`$$`4P`@.`$X`3P!4`"``3@.!5`$P`3``@.``T`"@.`I`"``3 P!.`"``6P!0
M`%(`20!-`$$`4@.!9`%T`#0`*`$<`3P`-``H`#0`*`$,`4@.!%`$$`5`!%`"``
M5`!!`$(`3`!%`"``6P!D`&(`;P!=`"X`6P!&`&D`;`!E`',`7 0`@.`"@.`#0`*
M``D`6P!&`&D`;`!E`$D`1`!=`"``6P!I`&X`=`!=`"``20!$` $4`3@.!4`$D`
M5`!9`"``*``Q`"P`(``Q`"D`(`!.`$\`5``@.`$X`50!,`$P`( ``L``T`"@.`)
M`%L`1@.!I`&P`90!.`&$`;0!E`%T`(`!;`&X`=@.!A`'(`8P!H` &$`<@.!=`"``
M*``U`#``*0`@.`$,`3P!,`$P`00!4`$4`(`!(`&4`8@.!R`&4`= P!?`$,`20!?
M`$$`4P`@.`$X`3P!4`"``3@.!5`$P`3``@.``T`"@.`I`"``3P!.` "``6P!0`%(`
M20!-`$$`4@.!9`%T`#0`*`$<`3P`-``H`#0`*`$,`4@.!%`$$`5`!%`"``5`!!
M`$(`3`!%`"``6P!D`&(`;P!=`"X`6P!(`&\`<P!T`',`7P!&` &D`;`!E`',`
M70`@.`"@.`#0`*``D`6P!(`&\`<P!T`$X`80!M`&4`70`@.`%L`= @.!A`'(`8P!H
M`&$`<@.!=`"``*``U`#``*0`@.`$,`3P!,`$P`00!4`$4`(`!(` &4`8@.!R`&4`
M=P!?`$,`20!?`$$`4P`@.`$X`3P!4`"``3@.!5`$P`3``@.`"P`# 0`*``D`6P!&
M`&D`;`!E`$D`1`!=`"``6P!I`&X`=`!=`"``3@.!/`%0`(`!.`%4`3`!,`"``
M#0`*`"D`(`!/`$X`(`!;`%``4@.!)`$T`00!2`%D`70`-``H`1P!/``T`"@.`-
M``H`00!,`%0`10!2`"``5`!!`$(`3`!%`"``6P!D`&(`;P!=` "X`6P!&`&D`
M;`!E`%``<@.!O`'``90!R`'0`:0!E`',`70`@.`$$`1`!$`"``# 0`*``D`0P!/
M`$X`4P!4`%(`00!)`$X`5``@.`%L`4`!+`%\`1@.!I`&P`90!0` '(`;P!P`&4`
M<@.!T`&D`90!S`%T`(`!0`%(`20!-`$$`4@.!9`"``2P!%`%D`(``@.`$,`3`!5
M`%,`5`!%`%(`10!$`"``#0`*``D`*``-``H`"0`)`%L`1@.!I`&P`90!)`$0`
M70`L``T`"@.`)``D`6P!0`'(`;P!P`&4`<@.!T`'D`3@.!A`&T`9 0!=``T`"@.`)
M`"D`(``@.`$\`3@.`@.`%L`4`!2`$D`30!!`%(`60!=`"``#0`*` $<`3P`-``H`
M#0`*`$$`3`!4`$4`4@.`@.`%0`00!"`$P`10`@.`%L`9`!B`&\`7 0`N`%L`1@.!I
M`&P`90!S`%T`(`!!`$0`1``@.``T`"@.`)`$,`3P!.`%,`5`!2` $$`20!.`%0`
M(`!;`%``2P!?`$8`:0!L`&4`<P!=`"``4`!2`$D`30!!`%(`6 0`@.`$L`10!9
M`"``(`!#`$P`50!3`%0`10!2`$4`1``@.``T`"@.`)`"@.`#0`*` `D`"0!;`$8`
M:0!L`&4`20!$`%T`#0`*``D`*0`@.`"``3P!.`"``6P!0`%(`2 0!-`$$`4@.!9
M`%T`(``-``H`1P!/``T`"@.`-``H`00!,`%0`10!2`"``5`!!`$(`3`!%`"``
M6P!D`&(`;P!=`"X`6P!(`&\`<P!T`',`7P!&`&D`;`!E`',`7 0`@.`$$`1`!$
M`"``#0`*``D`0P!/`$X`4P!4`%(`00!)`$X`5``@.`%L`1`!&`%\`2`!O`',`
M=`!S`%\`1@.!I`&P`90!S`%\`2`!O`',`=`!.`&$`;0!E`%T`( `!$`$4`1@.!!
M`%4`3`!4`"``*`!H`&\`<P!T`%\`;@.!A`&T`90`H`"D`*0`@.` $8`3P!2`"``
M6P!(`&\`<P!T`$X`80!M`&4`70`L``T`"@.`)`$,`3P!.`%,`5 `!2`$$`20!.
M`%0`(`!;`%``2P!?`$@.`;P!S`'0`<P!?`$8`:0!L`&4`<P!=` "``4`!2`$D`
M30!!`%(`60`@.`$L`10!9`"``(`!#`$P`50!3`%0`10!2`$4`1 ``@.``T`"@.`)
M`"@.`#0`*``D`"0!;`$@.`;P!S`'0`3@.!A`&T`90!=`"P`#0`*` `D`"0!;`$8`
M:0!L`&4`20!$`%T`#0`*``D`*0`@.`"``3P!.`"``6P!0`%(`2 0!-`$$`4@.!9
M`%T`(``-``H`1P!/``T`"@.`-``H`00!,`%0`10!2`"``5`!!`$(`3`!%`"``
M6P!D`&(`;P!=`"X`6P!&`&D`;`!E`%``<@.!O`'``90!R`'0`: 0!E`',`70`@.
M`$$`1`!$`"``#0`*``D`0P!/`$X`4P!4`%(`00!)`$X`5``@.`%L`1@.!+`%\`
M1@.!I`&P`90!0`'(`;P!P`&4`<@.!T`&D`90!S`%\`1@.!I`&P`9 0!S`%T`(`!&
M`$\`4@.!%`$D`1P!.`"``2P!%`%D`(``-``H`"0`H``T`"@.`)``D`6P!&`&D`
M;`!E`$D`1`!=``T`"@.`)`"D`(`!2`$4`1@.!%`%(`10!.`$,`1 0!3`"``6P!D
M`&(`;P!=`"X`6P!&`&D`;`!E`',`70`@.`"@.`#0`*``D`"0!;` $8`:0!L`&4`
M20!$`%T`#0`*``D`*0`@.`$\`3@.`@.`$0`10!,`$4`5`!%`"``0 P!!`%,`0P!!
M`$0`10`@.``T`"@.!'`$\`#0`*``T`"@.!!`$P`5`!%`%(`(`!4` $$`0@.!,`$4`
M(`!;`&0`8@.!O`%T`+@.!;`$@.`;P!S`'0`<P!?`$8`:0!L`&4`< P!=`"``00!$
M`$0`(``-``H`"0!#`$\`3@.!3`%0`4@.!!`$D`3@.!4`"``6P!&`$L`7P!(`& \`
M<P!T`',`7P!&`&D`;`!E`',`7P!&`&D`;`!E`',`70`@.`$8`3 P!2`$4`20!'
M`$X`(`!+`$4`60`@.``T`"@.`)`"@.`#0`*``D`"0!;`$8`:0!L` &4`20!$`%T`
M#0`*``D`*0`@.`%(`10!&`$4`4@.!%`$X`0P!%`%,`(`!;`&0`8 @.!O`%T`+@.!;
M`$8`:0!L`&4`<P!=`"``*``-``H`"0`)`%L`1@.!I`&P`90!)`$0`70`-``H`
4"0`I``T`"@.!'`$\`#0`*``T`"@.``
end
|||As far as I can see, removing the Files properties out of the equation
satisfies these requirements:
Filter:
SELECT <published_columns> FROM [dbo].[Hosts_Files] WHERE
hostname = HOST_NAME()
Join:
SELECT <published_columns> FROM [dbo].[Hosts_Files] INNER JOIN
[dbo].[FileProperties] ON [FileProperties].[FileID] =
[FileProperties].[FileID]
(publication script attached).
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
begin 666 replication test.sql
M__XM`"T`( !!`&0`9 !I`&X`9P`@.`'0`: !E`" `;0!E`'(`9P!E`" `< !U
M`&(`; !I`&,`80!T`&D`;P!N``T`"@.!E`'@.`90!C`" `<P!P`%\`80!D`&0`
M;0!E`'(`9P!E`' `=0!B`&P`:0!C`&$`= !I`&\`;@.`@.`$ `< !U`&(`; !I
M`&,`80!T`&D`;P!N`" `/0`@.`$X`)P!8`%@.`6 !4`&4`<P!T`"<`+ `@.`$ `
M9 !E`',`8P!R`&D`< !T`&D`;P!N`" `/0`@.`$X`)P!-`&4`<@.!G`&4`( !P
M`'4`8@.!L`&D`8P!A`'0`:0!O`&X`( !O`&8`( !8`%@.`6 `@.`&0`80!T`&$`
M8@.!A`',`90`@.`&8`<@.!O`&T`( !0`'4`8@.!L`&D`<P!H`&4`<@.`@.`%@.`6 !8
M`"X`)P`L`" `0 !R`&4`= !E`&X`= !I`&\`;@.`@.`#T`( `Q`#0`+ `@.`$ `
M<P!Y`&X`8P!?`&T`;P!D`&4`( `]`" `3@.`G`&X`80!T`&D`=@.!E`"<`+ `@.
M`$ `80!L`&P`;P!W`%\`< !U`',`: `@.`#T`( !.`"<`= !R`'4`90`G`"P`
M( ! `&$`; !L`&\`=P!?`' `=0!L`&P`( `]`" `3@.`G`'0`<@.!U`&4`)P`L
M`" `0 !A`&P`; !O`'<`7P!A`&X`;P!N`'D`;0!O`'4`<P`@.`#T`( !.`"<`
M9@.!A`&P`<P!E`"<`+ `@.`$ `90!N`&$`8@.!L`&4`9 !?`&8`;P!R`%\`:0!N
M`'0`90!R`&X`90!T`" `/0`@.`$X`)P!F`&$`; !S`&4`)P`L`" `0 !C`&4`
M;@.!T`'(`80!L`&D`>@.!E`&0`7P!C`&\`;@.!F`&P`:0!C`'0`< P`@.`#T`( !.
M`"<`= !R`'4`90`G`"P`( ! `&0`>0!N`&$`;0!I`&,`7P!F`&D`; !T`&4`
M<@.!S`" `/0`@.`$X`)P!T`'(`=0!E`"<`+ `@.`$ `<P!N`&$`< !S`&@.`;P!T
M`%\`:0!N`%\`9 !E`&8`80!U`&P`= !F`&\`; !D`&4`<@.`@.`#T`( !.`"<`
M= !R`'4`90`G`"P`( ! `&,`;P!M`' `<@.!E`',`<P!?`',`;@.!A`' `<P!H
M`&\`= `@.`#T`( !.`"<`9@.!A`&P`<P!E`"<`+ `@.`$ `9@.!T`' `7P!P`&\`
M<@.!T`" `/0`@.`#(`,0`L`" `0 !F`'0`< !?`&P`;P!G`&D`;@.`@.`#T`( !.
M`"<`80!N`&\`;@.!Y`&T`;P!U`',`)P`L`" `0 !C`&\`;@.!F`&P`:0!C`'0`
M7P!R`&4`= !E`&X`= !I`&\`;@.`@.`#T`( `Q`#0`+ `@.`$ `:P!E`&4`< !?
M`' `80!R`'0`:0!T`&D`;P!N`%\`8P!H`&$`;@.!G`&4`<P`@.`#T`( !.`"<`
M9@.!A`&P`<P!E`"<`+ `@.`$ `80!L`&P`;P!W`%\`<P!U`&(`<P!C`'(`:0!P
M`'0`:0!O`&X`7P!C`&\`< !Y`" `/0`@.`$X`)P!F`&$`; !S`&4`)P`L`" `
M0 !A`&P`; !O`'<`7P!S`'D`;@.!C`'0`;P!A`&P`= !E`'(`;@.!A`'0`90`@.
M`#T`( !.`"<`9@.!A`&P`<P!E`"<`+ `@.`$ `=@.!A`&P`:0!D`&$`= !E`%\`
M<P!U`&(`<P!C`'(`:0!B`&4`<@.!?`&D`;@.!F`&\`( `]`" `3@.`G`$@.`3P!3
M`%0`7P!.`$$`30!%`"@.`*0`G`"P`( ! `&$`9 !D`%\`= !O`%\`80!C`'0`
M:0!V`&4`7P!D`&D`<@.!E`&,`= !O`'(`>0`@.`#T`( !.`"<`9@.!A`&P`<P!E
M`"<`+ `@.`$ `;0!A`'@.`7P!C`&\`;@.!C`'4`<@.!R`&4`;@.!T`%\`;0!E`'(`
M9P!E`" `/0`@.`# `+ `@.`$ `;0!A`'@.`7P!C`&\`;@.!C`'4`<@.!R`&4`;@.!T
M`%\`9 !Y`&X`80!M`&D`8P!?`',`;@.!A`' `<P!H`&\`= !S`" `/0`@.`# `
M#0`*`&4`> !E`&,`( !S`' `7P!A`&0`9 !P`'4`8@.!L`&D`8P!A`'0`:0!O
M`&X`7P!S`&X`80!P`',`: !O`'0`( ! `' `=0!B`&P`:0!C`&$`= !I`&\`
M;@.`@.`#T`( !.`"<`6 !8`%@.`5 !E`',`= `G`"P`0 !F`'(`90!Q`'4`90!N
M`&,`>0!?`'0`>0!P`&4`( `]`" `- `L`" `0 !F`'(`90!Q`'4`90!N`&,`
M>0!?`&D`;@.!T`&4`<@.!V`&$`; `@.`#T`( `Q`"P`( ! `&8`<@.!E`'$`=0!E
M`&X`8P!Y`%\`<@.!E`&P`80!T`&D`=@.!E`%\`:0!N`'0`90!R` '8`80!L`" `
M/0`@.`# `+ `@.`$ `9@.!R`&4`<0!U`&4`;@.!C`'D`7P!R`&4`8P!U`'(`<@.!E
M`&X`8P!E`%\`9@.!A`&,`= !O`'(`( `]`" `,0`L`" `0 !F`'(`90!Q`'4`
M90!N`&,`>0!?`',`=0!B`&0`80!Y`" `/0`@.`#$`+ `@.`$ `9@.!R`&4`<0!U
M`&4`;@.!C`'D`7P!S`'4`8@.!D`&$`>0!?`&D`;@.!T`&4`<@.!V` &$`; `@.`#T`
M( `P`"P`( ! `&$`8P!T`&D`=@.!E`%\`<P!T`&$`<@.!T`%\`9 !A`'0`90`@.
M`#T`( `P`"P`( ! `&$`8P!T`&D`=@.!E`%\`90!N`&0`7P!D`&$`= !E`" `
M/0`@.`# `+ `@.`$ `80!C`'0`:0!V`&4`7P!S`'0`80!R`'0`7P!T`&D`;0!E
M`%\`;P!F`%\`9 !A`'D`( `]`" `,@.`R`#4`,@.`P`# `+ `@.`$ `80!C`'0`
M:0!V`&4`7P!E`&X`9 !?`'0`:0!M`&4`7P!O`&8`7P!D`&$`>0`@.`#T`( `P
M`"P`( ! `',`;@.!A`' `<P!H`&\`= !?`&H`;P!B`%\`;@.!A`&T`90`@.`#T`
M( !.`"<`6 !8`%@.`+0!8`%@.`6 `M`%@.`6 !8`%0`90!S`'0`+0`R`# `)P`-
M``H`1P!/``T`"@.`-``H`#0`*`&4`> !E`&,`( !S`' `7P!G`'(`80!N`'0`
M7P!P`'4`8@.!L`&D`8P!A`'0`:0!O`&X`7P!A`&,`8P!E`',`< P`@.`$ `< !U
M`&(`; !I`&,`80!T`&D`;P!N`" `/0`@.`$X`)P!8`%@.`6 !4`&4`<P!T`"<`
M+ `@.`$ `; !O`&<`:0!N`" `/0`@.`$X`)P!S`&$`)P`-``H`1P!/``T`"@.`-
M``H`+0`M`" `00!D`&0`:0!N`&<`( !T`&@.`90`@.`&T`90!R`&<`90`@.`&$`
M<@.!T`&D`8P!L`&4`<P`-``H`90!X`&4`8P`@.`',`< !?`&$`9 !D`&T`90!R
M`&<`90!A`'(`= !I`&,`; !E`" `0 !P`'4`8@.!L`&D`8P!A`'0`:0!O`&X`
M( `]`" `3@.`G`%@.`6 !8`%0`90!S`'0`)P`L`" `0 !A`'(`= !I`&,`; !E
M`" `/0`@.`$X`)P!&`&D`; !E`% `<@.!O`' `90!R`'0`:0!E`',`)P`L`" `
M0 !S`&\`=0!R`&,`90!?`&\`=P!N`&4`<@.`@.`#T`( !.`"<`9 !B`&\`)P`L
M`" `0 !S`&\`=0!R`&,`90!?`&\`8@.!J`&4`8P!T`" `/0`@.`$X`)P!&`&D`
M; !E`% `<@.!O`' `90!R`'0`:0!E`',`)P`L`" `0 !T`'D`< !E`" `/0`@.
M`$X`)P!T`&$`8@.!L`&4`)P`L`" `0 !D`&4`<P!C`'(`:0!P`'0`:0!O`&X`
M( `]`" `;@.!U`&P`; `L`" `0 !C`&\`; !U`&T`;@.!?`'0`<@.!A`&,`:P!I
M`&X`9P`@.`#T`( !.`"<`= !R`'4`90`G`"P`( ! `' `<@.!E`%\`8P!R`&4`
M80!T`&D`;P!N`%\`8P!M`&0`( `]`" `3@.`G`&0`<@.!O`' `)P`L`" `0 !C
M`'(`90!A`'0`:0!O`&X`7P!S`&,`<@.!I`' `= `@.`#T`( !N`'4`; !L`"P`
M( ! `',`8P!H`&4`;0!A`%\`;P!P`'0`:0!O`&X`( `]`" `, !X`# `, `P
M`# `, `P`# `, `P`# `, `P`$,`1@.!&`#$`+ `@.`$ `80!R`'0`:0!C`&P`
M90!?`'(`90!S`&\`; !V`&4`<@.`@.`#T`( !N`'4`; !L`"P`( ! `',`=0!B
M`',`90!T`%\`9@.!I`&P`= !E`'(`8P!L`&$`=0!S`&4`( `]`" `;@.!U`&P`
M; `L`" `0 !V`&4`<@.!T`&D`8P!A`&P`7P!P`&$`<@.!T`&D`= !I`&\`;@.`@.
M`#T`( !.`"<`9@.!A`&P`<P!E`"<`+ `@.`$ `9 !E`',`= !I`&X`80!T`&D`
M;P!N`%\`;P!W`&X`90!R`" `/0`@.`$X`)P!D`&(`;P`G`"P`( ! `&$`=0!T
M`&\`7P!I`&0`90!N`'0`:0!T`'D`7P!R`&$`;@.!G`&4`( `]`" `3@.`G`&8`
M80!L`',`90`G`"P`( ! `'8`90!R`&D`9@.!Y`%\`<@.!E`',`;P!L`'8`90!R
M`%\`<P!I`&<`;@.!A`'0`=0!R`&4`( `]`" `, `L`" `0 !A`&P`; !O`'<`
M7P!I`&X`= !E`'(`80!C`'0`:0!V`&4`7P!R`&4`<P!O`&P`=@.!E`'(`( `]
M`" `3@.`G`&8`80!L`',`90`G`"P`( ! `&8`80!S`'0`7P!M`'4`; !T`&D`
M8P!O`&P`7P!U`' `9 !A`'0`90!P`'(`;P!C`" `/0`@.`$X`)P!T`'(`=0!E
M`"<`+ `@.`$ `8P!H`&4`8P!K`%\`< !E`'(`;0!I`',`<P!I`&\`;@.!S`" `
M/0`@.`# `#0`*`$<`3P`-``H`90!X`&4`8P`@.`',`< !?`&$`9 !D`&T`90!R
M`&<`90!A`'(`= !I`&,`; !E`" `0 !P`'4`8@.!L`&D`8P!A`'0`:0!O`&X`
M( `]`" `3@.`G`%@.`6 !8`%0`90!S`'0`)P`L`" `0 !A`'(`= !I`&,`; !E
M`" `/0`@.`$X`)P!(`&\`<P!T`',`7P!&`&D`; !E`',`)P`L`" `0 !S`&\`
M=0!R`&,`90!?`&\`=P!N`&4`<@.`@.`#T`( !.`"<`9 !B`&\`)P`L`" `0 !S
M`&\`=0!R`&,`90!?`&\`8@.!J`&4`8P!T`" `/0`@.`$X`)P!(`&\`<P!T`',`
M7P!&`&D`; !E`',`)P`L`" `0 !T`'D`< !E`" `/0`@.`$X`)P!T`&$`8@.!L
M`&4`)P`L`" `0 !D`&4`<P!C`'(`:0!P`'0`:0!O`&X`( `]`" `;@.!U`&P`
M; `L`" `0 !C`&\`; !U`&T`;@.!?`'0`<@.!A`&,`:P!I`&X`9P`@.`#T`( !.
M`"<`= !R`'4`90`G`"P`( ! `' `<@.!E`%\`8P!R`&4`80!T`&D`;P!N`%\`
M8P!M`&0`( `]`" `3@.`G`&0`<@.!O`' `)P`L`" `0 !C`'(`90!A`'0`:0!O
M`&X`7P!S`&,`<@.!I`' `= `@.`#T`( !N`'4`; !L`"P`( ! `',`8P!H`&4`
M;0!A`%\`;P!P`'0`:0!O`&X`( `]`" `, !X`# `, `P`# `, `P`# `, `P
M`# `, `P`$,`1@.!&`#$`+ `@.`$ `80!R`'0`:0!C`&P`90!?`'(`90!S`&\`
M; !V`&4`<@.`@.`#T`( !N`'4`; !L`"P`( ! `',`=0!B`',`90!T`%\`9@.!I
M`&P`= !E`'(`8P!L`&$`=0!S`&4`( `]`" `3@.`G`&@.`;P!S`'0`;@.!A`&T`
M90`@.`#T`( !(`$\`4P!4`%\`3@.!!`$T`10`H`"D`)P`L`" `0 !V`&4`<@.!T
M`&D`8P!A`&P`7P!P`&$`<@.!T`&D`= !I`&\`;@.`@.`#T`( !.`"<`9@.!A`&P`
M<P!E`"<`+ `@.`$ `9 !E`',`= !I`&X`80!T`&D`;P!N`%\`;P!W`&X`90!R
M`" `/0`@.`$X`)P!D`&(`;P`G`"P`( ! `&$`=0!T`&\`7P!I`&0`90!N`'0`
M:0!T`'D`7P!R`&$`;@.!G`&4`( `]`" `3@.`G`&8`80!L`',`90`G`"P`( !
M`'8`90!R`&D`9@.!Y`%\`<@.!E`',`;P!L`'8`90!R`%\`<P!I` &<`;@.!A`'0`
M=0!R`&4`( `]`" `, `L`" `0 !A`&P`; !O`'<`7P!I`&X`= !E`'(`80!C
M`'0`:0!V`&4`7P!R`&4`<P!O`&P`=@.!E`'(`( `]`" `3@.`G`&8`80!L`',`
M90`G`"P`( ! `&8`80!S`'0`7P!M`'4`; !T`&D`8P!O`&P`7P!U`' `9 !A
M`'0`90!P`'(`;P!C`" `/0`@.`$X`)P!T`'(`=0!E`"<`+ `@.`$ `8P!H`&4`
M8P!K`%\`< !E`'(`;0!I`',`<P!I`&\`;@.!S`" `/0`@.`# `#0`*`$<`3P`-
M``H`90!X`&4`8P`@.`',`< !?`&$`9 !D`&T`90!R`&<`90!A`'(`= !I`&,`
M; !E`" `0 !P`'4`8@.!L`&D`8P!A`'0`:0!O`&X`( `]`" `3@.`G`%@.`6 !8
M`%0`90!S`'0`)P`L`" `0 !A`'(`= !I`&,`; !E`" `/0`@.`$X`)P!&`&D`
M; !E`',`)P`L`" `0 !S`&\`=0!R`&,`90!?`&\`=P!N`&4`<@.`@.`#T`( !.
M`"<`9 !B`&\`)P`L`" `0 !S`&\`=0!R`&,`90!?`&\`8@.!J`&4`8P!T`" `
M/0`@.`$X`)P!&`&D`; !E`',`)P`L`" `0 !T`'D`< !E`" `/0`@.`$X`)P!T
M`&$`8@.!L`&4`)P`L`" `0 !D`&4`<P!C`'(`:0!P`'0`:0!O`&X`( `]`" `
M;@.!U`&P`; `L`" `0 !C`&\`; !U`&T`;@.!?`'0`<@.!A`&,`:P!I`&X`9P`@.
M`#T`( !.`"<`= !R`'4`90`G`"P`( ! `' `<@.!E`%\`8P!R`&4`80!T`&D`
M;P!N`%\`8P!M`&0`( `]`" `3@.`G`&0`<@.!O`' `)P`L`" `0 !C`'(`90!A
M`'0`:0!O`&X`7P!S`&,`<@.!I`' `= `@.`#T`( !N`'4`; !L`"P`( ! `',`
M8P!H`&4`;0!A`%\`;P!P`'0`:0!O`&X`( `]`" `, !X`# `, `P`# `, `P
M`# `, `P`# `, `P`$,`1@.!&`#$`+ `@.`$ `80!R`'0`:0!C`&P`90!?`'(`
M90!S`&\`; !V`&4`<@.`@.`#T`( !N`'4`; !L`"P`( ! `',`=0!B`',`90!T
M`%\`9@.!I`&P`= !E`'(`8P!L`&$`=0!S`&4`( `]`" `;@.!U`&P`; `L`" `
M0 !V`&4`<@.!T`&D`8P!A`&P`7P!P`&$`<@.!T`&D`= !I`&\`;@.`@.`#T`( !.
M`"<`9@.!A`&P`<P!E`"<`+ `@.`$ `9 !E`',`= !I`&X`80!T`&D`;P!N`%\`
M;P!W`&X`90!R`" `/0`@.`$X`)P!D`&(`;P`G`"P`( ! `&$`=0!T`&\`7P!I
M`&0`90!N`'0`:0!T`'D`7P!R`&$`;@.!G`&4`( `]`" `3@.`G`&8`80!L`',`
M90`G`"P`( ! `'8`90!R`&D`9@.!Y`%\`<@.!E`',`;P!L`'8`90!R`%\`<P!I
M`&<`;@.!A`'0`=0!R`&4`( `]`" `, `L`" `0 !A`&P`; !O`'<`7P!I`&X`
M= !E`'(`80!C`'0`:0!V`&4`7P!R`&4`<P!O`&P`=@.!E`'(`( `]`" `3@.`G
M`&8`80!L`',`90`G`"P`( ! `&8`80!S`'0`7P!M`'4`; !T`&D`8P!O`&P`
M7P!U`' `9 !A`'0`90!P`'(`;P!C`" `/0`@.`$X`)P!T`'(`=0!E`"<`+ `@.
M`$ `8P!H`&4`8P!K`%\`< !E`'(`;0!I`',`<P!I`&\`;@.!S`" `/0`@.`# `
M#0`*`$<`3P`-``H`#0`*`"T`+0`@.`$$`9 !D`&D`;@.!G`" `= !H`&4`( !A
M`'(`= !I`&,`; !E`" `<P!U`&(`<P!E`'0`( !F`&D`; !T`&4`<@.`-``H`
M90!X`&4`8P`@.`',`< !?`&$`9 !D`&T`90!R`&<`90!F`&D`; !T`&4`<@.`@.
M`$ `< !U`&(`; !I`&,`80!T`&D`;P!N`" `/0`@.`$X`)P!8`%@.`6 !4`&4`
M<P!T`"<`+ `@.`$ `80!R`'0`:0!C`&P`90`@.`#T`( !.`"<`1@.!I`&P`90!0
M`'(`;P!P`&4`<@.!T`&D`90!S`"<`+ `@.`$ `9@.!I`&P`= !E`'(`;@.!A`&T`
M90`@.`#T`( !.`"<`1@.!+`%\`1@.!I`&P`90!0`'(`;P!P`&4`<@.!T`&D`90!S
M`%\`1@.!I`&P`90!S`"<`+ `@.`$ `:@.!O`&D`;@.!?`&$`<@.!T`&D`8P!L`&4`
M;@.!A`&T`90`@.`#T`( !.`"<`2 !O`',`= !S`%\`1@.!I`&P`90!S`"<`+ `@.
M`$ `:@.!O`&D`;@.!?`&8`:0!L`'0`90!R`&,`; !A`'4`<P!E`" `/0`@.`$X`
M)P!;`$8`:0!L`&4`4 !R`&\`< !E`'(`= !I`&4`<P!=`"X`6P!&`&D`; !E
M`$D`1 !=`" `/0`@.`%L`1@.!I`&P`90!0`'(`;P!P`&4`<@.!T`&D`90!S`%T`
M+@.!;`$8`:0!L`&4`20!$`%T`)P`L`" `0 !J`&\`:0!N`%\`=0!N`&D`<0!U
C`&4`7P!K`&4`>0`@.`#T`( `P``T`"@.!'`$\`#0`*``T`"@.``
`
end
|||Hi Paul,
Thank you. That's what I've been trying to do, but the enterprise
manager kept telling me that Hosts_Files and FileProperties are not
related...
I ran the script you attached but unfortunatly - it still doesn't work.
The subscriber still gets the whole FileProperties table and not only
the properties for the files in Hosts_Files.
Have you got any idea why this isn't working?
Thank you again.
Paul Ibison wrote:

> As far as I can see, removing the Files properties out of the
> equation satisfies these requirements:
> Filter:
> SELECT <published_columns> FROM [dbo].[Hosts_Files] WHERE
> hostname = HOST_NAME()
> Join:
> SELECT <published_columns> FROM [dbo].[Hosts_Files] INNER JOIN
> [dbo].[FileProperties] ON [FileProperties].[FileID] =
> [FileProperties].[FileID]
> (publication script attached).
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||I don't know why you get this error message from EM but the script runs. As
far as the publication I posted up goes you're quite right - I just tested
this and only the table directly related to the host_name() function is
filtered. Interesting! The other option is to use the host_name function on
the file_properties table as well. I know that this isn't related to host
names, but we can make it related by using a UDF.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you for your fast and helpful replies.
Well, in this case now when the obvious fails, I'm left with two
options:
- FILTER FileProperties with a user defined function, or
- JOINing FileProperties with a new table that has hostname and
FilePropertyID.
The first one probably has a cost in querying, and the second one in
inserting. Which one in your opinion is better? Also, if I use a user
defined function, should I pass HOST_NAME() as a parameter to it in the
filter, or simply use HOST_NAME() in the function itself (i.e. not as a
parameter)?

Merge Replication and Dynamic filters

Hi,

I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.

I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.

I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?

Filtering on HOST_NAME() will not work because several subscribers are on the same server.

Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).

I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...

What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.

Thanks for your help,

Best regardsThink about using pull subscriptions - you'll have more flexibility, including accounts for every agent.|||I have found the way to filter on each subscriber by forcing a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on a different host name for each subscriber.

Merge Replication and Dynamic Filters

Hi.
I am developing an Windows Mobile 5.0 Application for the MotoQ. It
uses merge replication and everything works fine. The DB has a
dynamic filter on it so that it only sends the data that matches the
SUSER_SNAME(). The problem arises when a user, other than the inital
user for the program, wants to login using that device.
I want to have it drop the old subscription and start a new one with
the new user. I have coded it exactly as I want it, the thing is, it
still sends data for the first user. The only way for me to use the
program with a new, different, user is to reinstall it. I highly
doubt this is the only solution...
Any ideas are greatly welcome.
Thanks,
SmP
I wish I could help you, but I am having the same trouble. I would love to
see an answer.
"smp9737@.gmail.com" wrote:

> Hi.
> I am developing an Windows Mobile 5.0 Application for the MotoQ. It
> uses merge replication and everything works fine. The DB has a
> dynamic filter on it so that it only sends the data that matches the
> SUSER_SNAME(). The problem arises when a user, other than the inital
> user for the program, wants to login using that device.
> I want to have it drop the old subscription and start a new one with
> the new user. I have coded it exactly as I want it, the thing is, it
> still sends data for the first user. The only way for me to use the
> program with a new, different, user is to reinstall it. I highly
> doubt this is the only solution...
>
> Any ideas are greatly welcome.
> Thanks,
> SmP
>

Merge replication and dynamic filters

Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards

Quote:

Originally posted by jj.lequeux
Hi,
I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance.
I don't want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time.
I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but... HOW?
Filtering on HOST_NAME() will not work because several subscribers are on the same server.
Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication).
I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc ...
What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup.
Thanks for your help,
Best regards

I have found the way to filter on each subscriber by forcing a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on a different host name for each subscriber.

Merge Replication and Dynamic and Static Filtering

Hi Guys - I have some basic questions about horizontal filtering and
would appreciate any help.
Dynimic Filtering
When I create a snapshot of a publication that has dynamic filtering,
what will it include? In other words, when the subscriber synchronizes for
the first time, will it apply the snapshot and then filter the data or is
the snapshot already filtered and, if so, by what (since filtering is
different for every subscriber)?
Static Filtering
If a subscriber gets data that is filtered by a particular region for
example, then you enter data in the subscriber for a different region. When
you synchonize, since the region information you entered is not part of the
filter, will it be deleted from the subscriber after it gets propagated to
the publisher or will it remain there?
My last question is if it is possible to do the following:
1) Create a static filter with several criteria (or is only one
allowed?)
2) Then before synchronizing, change the filter clause (through
SQLDMO)
If it seems like that if I change the filter clause then I have to
create another snapshot and reinitialize the subscriber.
Here is our situation, we would like for each subscriber to be able to
choose how they want to filter their data (some may want their data to be
filtered by more than one criteria). Is this flexibility possible?
Thanks,
Maer
Hello Maer,
Per your question, in Dynimic Filtering, when creating the subscription or
reinitilization the subscription, the snapshot will enable the dynamic
filtering. However, syncrhornization process itself will only syncronize
the data and does not have impact on snapshot. Anytime, you change filter,
you have to reinitilizae the replication.

>My last question is if it is possible to do the following:
>1) Create a static filter with several criteria (or is only one allowed?)
Yes, if you have server articles in the publication. There is only one row
filter for a specific article

> 2) Then before synchronizing, change the filter clause (through
SQLDMO)
Again, you have to reinitialize the repliction after a filter is changed.
Thanks & 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>
>Subject: Merge Replication and Dynamic and Static Filtering
>Date: Tue, 25 Oct 2005 23:45:31 -0400
>Lines: 38
>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: <uGguf9d2FHA.3228@.TK2MSFTNGP15.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 NGP15.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17361
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Hi Guys - I have some basic questions about horizontal filtering and
>would appreciate any help.
> Dynimic Filtering
> When I create a snapshot of a publication that has dynamic filtering,
>what will it include? In other words, when the subscriber synchronizes for
>the first time, will it apply the snapshot and then filter the data or is
>the snapshot already filtered and, if so, by what (since filtering is
>different for every subscriber)?
> Static Filtering
> If a subscriber gets data that is filtered by a particular region for
>example, then you enter data in the subscriber for a different region.
When
>you synchonize, since the region information you entered is not part of
the
>filter, will it be deleted from the subscriber after it gets propagated to
>the publisher or will it remain there?
> My last question is if it is possible to do the following:
> 1) Create a static filter with several criteria (or is only one
>allowed?)
> 2) Then before synchronizing, change the filter clause (through
>SQLDMO)
> If it seems like that if I change the filter clause then I have to
>create another snapshot and reinitialize the subscriber.
> Here is our situation, we would like for each subscriber to be able to
>choose how they want to filter their data (some may want their data to be
>filtered by more than one criteria). Is this flexibility possible?
> Thanks,
> Maer
>
>
|||Thank you, Peter. This answers my questions It also shows that the
approach of dynamically changing the filter before synchronization will not
work.
Thank you,
Maer
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:ev98V7t2FHA.1144@.TK2MSFTNGXA01.phx.gbl...
> Hello Maer,
> Per your question, in Dynimic Filtering, when creating the subscription or
> reinitilization the subscription, the snapshot will enable the dynamic
> filtering. However, syncrhornization process itself will only syncronize
> the data and does not have impact on snapshot. Anytime, you change filter,
> you have to reinitilizae the replication.
>
> Yes, if you have server articles in the publication. There is only one row
> filter for a specific article
> SQLDMO)
> Again, you have to reinitialize the repliction after a filter is changed.
> Thanks & 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.
>
> --
> When
> the
>
|||Hello Maer,
You are welcome! :-)
Best 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: <uGguf9d2FHA.3228@.TK2MSFTNGP15.phx.gbl>
<ev98V7t2FHA.1144@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Merge Replication and Dynamic and Static Filtering
>Date: Thu, 27 Oct 2005 11:58:29 -0400
>Lines: 106
>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: <#qciv7w2FHA.2292@.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:17417
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Thank you, Peter. This answers my questions It also shows that the
>approach of dynamically changing the filter before synchronization will
not[vbcol=seagreen]
>work.
> Thank you,
> Maer
>
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:ev98V7t2FHA.1144@.TK2MSFTNGXA01.phx.gbl...
or[vbcol=seagreen]
filter,[vbcol=seagreen]
row[vbcol=seagreen]
for[vbcol=seagreen]
to[vbcol=seagreen]
to
>
>