Merge Star Schema

Hi all,
We have two star schemas built into our warehouse: one called
vehicle data and one called parts data. For some reason the warehouse
was built with these quite separate 'data marts' (not sure if that's
the right term)? Even though the parts will always relate to the
vehicle.
How should these be merged? Do we connect via the dimension
tables? Or do we need a some other new type of tables to carry the
keys?
Would another strategy be to place all the parts facts
into the vehicle fact table and then merge the dimension tables also?
Many thanks in advance for any pointers.
Sam
Hello SGPG,
I hope you haven't given up on this thread. It's hard to get an
idea of your problem with out a bit more information. If you could
post a brief description of each fact and dimension table that would be
a great help.
Here are a couple of points to consider in the mean time
Star Schemas can be very hard to consolidate. You see it's all a
question of grain...
Grain is the lowest unique level of the fact table. In most star
schemas the grain defines the grouping of the metrics. Grain is defined
by the dimensions and the primary key of the fact table.
You can consolidate fact tables if the metrics make sense and the fact
table grains are the same.
For example
You have two star schemas each maintain a different set of metrics.
Below are Fact Invoices (what has been sold to the customer) and Fact
Orders (what the customer has ordered
Fact Invoices
InvoiceDate
CustomerNumber
ProductCode
InvoiceNumber
InvoiceLineNumber
InvoiceUnitPrice
InvoiceQty
InvoiceLineTotal
Fact Orders
OrderDate
DueDate
CustomerNumber
ProductCode
InvoiceNumber
InvoiceLineNumber
OrderUnitPrice
OrderQty
OrderLineTotal
Both fact tables have a common set of dimensions and grain. We know
that Invoice Number and Invoice line number are the same. Also we have
the common dimension of Customer and product. So the lowest level that
the metrics make sense is (CustomerNumber, ProductCode, InvoiceNumber,
InvoiceLineNumber)
In this example the order dates and the invoices do not affect the
grain of the common fact table. So the consolidate fact table would
look something this.
Fact Sales
InvoiceDate
OrderDate
DueDate
CustomerNumber
ProductCode
InvoiceNumber
InvoiceLineNumber
OrderUnitPrice
OrderQty
OrderLineTotal
InvoiceUnitPrice
InvoiceQty
InvoiceLineTotal
Although this example assumes that every order has an invoice. It is
possible to have orders that do not have invoices in this star schema.
The invoice metrics would be represented as 0 with no invoice date.
Hope this Helps
Myles Matheson
Data Warehouse Architect
|||Thanks Miles. I do not have the detail of the example I posed as it's
a while since I saw it. My question stemmed from, what I saw, as
something of a design flaw as a vehicle fact table was built with
dimension tables around it. Later a parts fact was built which seemed
to be the beginning of a data mart approach to data warehousing. Your
solution might not have worked in this case as parts were usually
aggregated up (i.e. a vehicle might have 10 or more parts installed).
I wondered how this should have been designed to incorporate both parts
and vehicles.
sql

Merge Star Schema

Hi all,
We have two star schemas built into our warehouse: one called
vehicle data and one called parts data. For some reason the warehouse
was built with these quite separate 'data marts' (not sure if that's
the right term)? Even though the parts will always relate to the
vehicle.
How should these be merged? Do we connect via the dimension
tables? Or do we need a some other new type of tables to carry the
keys?
Would another strategy be to place all the parts facts
into the vehicle fact table and then merge the dimension tables also?
Many thanks in advance for any pointers.
SamHello SGPG,
I hope you haven't given up on this thread. It's hard to get an
idea of your problem with out a bit more information. If you could
post a brief description of each fact and dimension table that would be
a great help.
Here are a couple of points to consider in the mean time
Star Schemas can be very hard to consolidate. You see it's all a
question of grain...
Grain is the lowest unique level of the fact table. In most star
schemas the grain defines the grouping of the metrics. Grain is defined
by the dimensions and the primary key of the fact table.
You can consolidate fact tables if the metrics make sense and the fact
table grains are the same.
For example
You have two star schemas each maintain a different set of metrics.
Below are Fact Invoices (what has been sold to the customer) and Fact
Orders (what the customer has ordered
Fact Invoices
InvoiceDate
CustomerNumber
ProductCode
InvoiceNumber
InvoiceLineNumber
InvoiceUnitPrice
InvoiceQty
InvoiceLineTotal
Fact Orders
OrderDate
DueDate
CustomerNumber
ProductCode
InvoiceNumber
InvoiceLineNumber
OrderUnitPrice
OrderQty
OrderLineTotal
Both fact tables have a common set of dimensions and grain. We know
that Invoice Number and Invoice line number are the same. Also we have
the common dimension of Customer and product. So the lowest level that
the metrics make sense is (CustomerNumber, ProductCode, InvoiceNumber,
InvoiceLineNumber)
In this example the order dates and the invoices do not affect the
grain of the common fact table. So the consolidate fact table would
look something this.
Fact Sales
InvoiceDate
OrderDate
DueDate
CustomerNumber
ProductCode
InvoiceNumber
InvoiceLineNumber
OrderUnitPrice
OrderQty
OrderLineTotal
InvoiceUnitPrice
InvoiceQty
InvoiceLineTotal
Although this example assumes that every order has an invoice. It is
possible to have orders that do not have invoices in this star schema.
The invoice metrics would be represented as 0 with no invoice date.
Hope this Helps
Myles Matheson
Data Warehouse Architect|||Thanks Miles. I do not have the detail of the example I posed as it's
a while since I saw it. My question stemmed from, what I saw, as
something of a design flaw as a vehicle fact table was built with
dimension tables around it. Later a parts fact was built which seemed
to be the beginning of a data mart approach to data warehousing. Your
solution might not have worked in this case as parts were usually
aggregated up (i.e. a vehicle might have 10 or more parts installed).
I wondered how this should have been designed to incorporate both parts
and vehicles.

Merge republish Schema changes

Hello,

I'm working on a replication topology that is completely merge. We have a single consolidated instance (SQL 2005 SP1 Standard) that holds all data and is a continuous push merge publication filtered by region to regional instances (SQL 2005 SP1 Standard). Then we have individual user instances (SQL Express SP1) that pulls from the republished regional instances which is filtered by user. Both publications have Replicate Schema Changes set to true.

I'm testing out changes to tables and sps on a test system I've been using this process:

1-Run Snapshot on the Consolidated instance

2-Verify all published articles have a status of 2 in sysmergearticles

3-Run Regional Snapshot

4-Verify all published articles have a status of 2 in sysmergearticles

5-Run alter table scripts

6-Once all three levels have the table changes, run the alter sp scripts

I've gotten to step 5 and and the changes get replicated to the regional instance just fine however only the existing column changes get replicated to the SQLExpress instance, not the new columns. Looking at the articles in the regional publication it shows the new columns, but they are not selected. I know I can manually select them (or probably write a script that adds them to the publication although sp_repladdcolumn has been depreciated), but isn't there a way to make this a completely automated process since it's just a republished database? Also is the process I'm using the correct one?

Thank you,

Aaron Lowe

Is your publication property replication_ddl set to true?|||I apologize for not being clearer in my original post. I had said that replicate schema changes was set to true, this is the replication_ddl property that I was referring to. Thanks, Aaron|||when you add a new column, the column should get replicated to all nodes in your topology. Is the new column not getting replicated at all? Where in your topology are you adding the new columns - publisher, republisher or subscriber?|||I'm adding the columns at my original publisher (the consolidated one). As I said it is pushed down to my subscribers that republish the data (the regional ones that are pushed from the consolidated one), it just doesn't get all the way down to my final subscribers (the individual sqlexpress ones that pull the data). Looking at the properties of the publication on the republisher it shows the columns in the publication but they are not selected.|||if replicate_ddl option is truly enabled at both the publisher and the republisher, then I'm not sure what the problem is. You verified the replicate_ddl column is set to 1 in sysmergepublications table in the published database at both the publisher and republisher?|||

Well, I believe it's correct, here's what is in the sysmergepublications:

Consolidated database (original publisher)

publication name, replicate_ddl

Consolidated, 1

Region, 0

Regional database (republisher)

publication name, replicate_ddl

Consolidated, 1

Region, 1

SQL Express database (subscriber)

publication name, replicate_ddl

Consolidated, 0

Region, 1

Also the status in sysmergearticles in the consolidated db is 2 (active). There are two sets of articles in the sysmergearticles table in the regional db, one for each the consolidated and regional publication. The records in sysmergearticles for the consolidated publication has a status of 1 (Unsynced) while the records for the regional publication have a status of 2 (active). The status in the SQLExpress pull subscriptions is all 1 (Unsynced).

Thanks,

Aaron

|||Can you try your scenario with SP2? We fixed somewhat similar issue in SP2.sql

Merge Replication Schema Sync Problems

I am using a merge publication set to synchronize schema changes. Why am I getting the following message when I try to sync after having added columns in the publication database using ALTER TABLE statements?

The schema definition of the destination table ... in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table.

Also, why doesn't reinitializing the subscription with @.upload_first = false not fix the problem?

I have encountered this problem with both merge replication and with transactional replication

When you add columns at the publisher they do not always get published correctly. You can check in the GUI for the properties of the article and will sometimes see that the new columns are not checked.

I have found that it is necessary to either use the GUI or TSQL to explicitly add the missing columns to the publication. If you have tried to merge resync before doing this then I think that you will need to reinitialise your subscriptions

In my experience this behaviour seems to happen frequently with transactional replication, but only occasionally with merge replication

reinit with upload first won't fix the problem if the merge is attempting to synchronize and enumerate data changes with different schemas on the publisher and subscriber

aero1

Merge Replication Schema Changes

We have three distinct environments that a databse resides in: a lab
environment, merge replicated with other lab servers, a production
environment, merge replicated with other production servers, and a
development environment not replicated anywhere. We do not want day-to-day
development (schema and data changes) to effect either the lab or production
environments, however periodically would like to take these changes and apply
them to the lab environment. Unfortunately, all tools that we have used to
copy these schema changes from development to lab fail since replication is
not configured in the development environment (and therefore schema migration
fails due to the lack of row guids and/or default value differences). How
can we easily and routinely (but manually) package schema changes from a
non-replicated database and push them into a replicated database, without
breaking replication itself? Once the schema is migrated, moving data seems
trivial.
It seems that your problem is due to the guids and related metadata not
being present in the dev environment. I think the best way to do this would
be to either merge publisher the dev environment, or to add the guid columns
with the unique indexes on each table you are replicating like this:
alter table MyTable
add [rowguid] [uniqueidentifier] ROWGUIDCOL
NOT NULL
CONSTRAINT [MSmerge_df_rowguid_MyTable] DEFAULT (newid())
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
"Zer0Day" <Zer0Day@.discussions.microsoft.com> wrote in message
news:2B2E7210-1416-482D-AC33-31DD7CEA1A67@.microsoft.com...
> We have three distinct environments that a databse resides in: a lab
> environment, merge replicated with other lab servers, a production
> environment, merge replicated with other production servers, and a
> development environment not replicated anywhere. We do not want
> day-to-day
> development (schema and data changes) to effect either the lab or
> production
> environments, however periodically would like to take these changes and
> apply
> them to the lab environment. Unfortunately, all tools that we have used
> to
> copy these schema changes from development to lab fail since replication
> is
> not configured in the development environment (and therefore schema
> migration
> fails due to the lack of row guids and/or default value differences). How
> can we easily and routinely (but manually) package schema changes from a
> non-replicated database and push them into a replicated database, without
> breaking replication itself? Once the schema is migrated, moving data
> seems
> trivial.

Merge replication schema changes

I want to implement merge replication between SQL Server 2005 and SQL Server
2005 express. My problem is, when we make table schema changes like adding
and removing columns, we drop and re-create the table. Is there any way that
this methodology can work with merge replication. I know the replication
trigger only track ATLER TABLE statements.
Thanks
Mark,
I'm not too sure what is happeng here. Are you saying that the ALTER TABLE
statement causes reinitialization? Or is a new snapshot being created? Or do
you manually drop the table, change the columns and then add the table
again? Just trying to get the gist of the issue
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
We manually drop the table, change the columns and then add the table again.
The documentation I've read says that merge replication only tracks ATLER
TABLE statements. (Which we don't use).
Thanks for the quick response.
"Paul Ibison" wrote:

> Mark,
> I'm not too sure what is happeng here. Are you saying that the ALTER TABLE
> statement causes reinitialization? Or is a new snapshot being created? Or do
> you manually drop the table, change the columns and then add the table
> again? Just trying to get the gist of the issue
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Mark - not sure I follow - how do you make the changes to the table's
schema?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
When we need to change a tables schema, we copy all the data out into a new
table with the new table structure. Then we delete the old table and rename
the new table to be the old table. If we want to add a new column to a table
we do not issue and ALTER TABLE add <column_name>... statement. We create a
new table with the new column in it and then copy the data over from the old
table. Update the new column with appropriate values. DROP the old table and
RENAME the new table. How can we get these schema changes propegated to the
subscribers.
Can we remove the table first from the publication. Drop and CREATE the
table and then add it into the publication again? What about pending
subscriber changes. Would they be lost in that case?
Thanks again.
"Paul Ibison" wrote:

> Mark - not sure I follow - how do you make the changes to the table's
> schema?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||I would advise to use the inbuilt merge replication's support to ALTER
TABLES and propagate schema changes.
However I can also understand that it may be difficult to change the app. In
that case, yes you could drop the article and readd it after you change the
schema. The subscriber's unsent data will cause a problem in this case.
You may think that setting a pre_creation_cmd of 'none' to save the
subscriber's unsent changes. But remember that the schema is now different
between publisher and subscriber.
One thing you can do is to save off subscriber's unsent changes into a temp
table and then reinsert these rows after re-synchronizing the (new schema)
table.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Mark (WHSCC)" <MarkWHSCC@.discussions.microsoft.com> wrote in message
news:63DB1088-15DE-4A3C-8FEE-A91C79D4AC9E@.microsoft.com...[vbcol=seagreen]
> Hi Paul,
> When we need to change a tables schema, we copy all the data out into a
> new
> table with the new table structure. Then we delete the old table and
> rename
> the new table to be the old table. If we want to add a new column to a
> table
> we do not issue and ALTER TABLE add <column_name>... statement. We create
> a
> new table with the new column in it and then copy the data over from the
> old
> table. Update the new column with appropriate values. DROP the old table
> and
> RENAME the new table. How can we get these schema changes propegated to
> the
> subscribers.
> Can we remove the table first from the publication. Drop and CREATE the
> table and then add it into the publication again? What about pending
> subscriber changes. Would they be lost in that case?
> Thanks again.
> "Paul Ibison" wrote:

Merge Replication Schema Change

Using merge replication do schema changes made to the publisher automatically get replicated to the subscriber or is it necessary to reinitialize the subscription.
Thanks,
Lance
Replication Newbie
Assuming you're using sp_repladdcolumn, sp_repldropcolumn then it is
automatic.
HTH,
Paul Ibison
|||Thanks, What about new tables, indexes, and constraints?
|||Lance,
for new tables, you can use sp_addmergearticle. When you add the article,
you can choose whether to take indexes, constraints using @.schema_option.
HTH,
Paul Ibison
sql

Merge Replication Questions [SQL2k5 non express]

I can choose synchronization direction for articles: a) Bidirectional b) one way

1) Is that possible somehow to replicate the schema only of an article but no synchronization / zero direction :-)/

2) Same question about columns, I should replicate schema only for few columns, but without data synch. These columns are freely updateable at anywhere (publisher and subscribers), but the data changes shouldn't be replicated.

Thanks for the answers in advance

I guess that you want to keep same schema's at two or more machines?

I do not know whether you can do it using replication, actually I think that there is no way to do something like that.

What I would do is that I would script database, and make same copies at all locations. Later when you need some updates/changes to schema, you can script those also. Not only that, but you can build your own schema replication system, so everything could go, kind of, semi-automatic.

|||

Sorry, my initial question was not clear.

I would like to replicate all tables in the db, except 1-2 tables and 3-4 columns only.

Let's see the following example:

There are about 30 tables to replicate let's name those T1, T2, T3, ... T30 and the column names are T1C1, T1C2, ... T2C1, T2C2, .... etc

I would like to replicate all tables, except T15 and T16 tables (all columns) and 4 columns T8C4, T8C5, T9C2 and T9C3. But the schema should be the same at all places, so T15 and T16 should be exist at subscribers and publishers and the mentioned columns also, but data should not be replicated to-from that 2 table and from/to that 4 columns.

|||

Now it is much clearer to me.

As I said, you can copy your schema to be same on all databases, but you can filter out your publication so you just replicate tables T1-T14 and T17-T30, and to replicate all columns except T8C4, T8C5, T9C2 and T9C3.

If you then choose to initialize, you will loose tables/columns that are not in replication, but you can add them later.

Or you can make publication, then copy db schema using script to subscriber (so you have rowguid) and choose do not initialize.

Test it, play around with it. Make on your own sql server two tiny db's with two tables (one publisher and one subscriber) and play with it.

|||

THis can be done with replication. What you do is create a publication containing all the tables and columns you want. You can then script out table T15 and T16, put it in a file, and reference it in parameter @.post_snapshot_script for stored procedure sp_addpublication or sp_addmergepublication.

|||

Thanks for the answer. It took a bit longer, because I ran into a little problem, I got the following error message on one of my stored procedure in post-snapshot script:

"The query processor could not produce a query plan. For more information, contact Customer Support Services."

It was because i forgot to include the:

set QUOTED_IDENTIFIER ON

maybe this is a bug of sql2k5 SP1

btw, I moved the T8C4, T8C5, T9C2 and T9C3 columns to separate tables as well, and there are foreign keys pointing back to the original tables PKs

Thanks again for the solution.

Merge Replication over internet Problem: Couldn't propagate schema to the subscriber.

Hello All,
I am implementing Pull merge replication over internet, I have a sql
server which I have set as Publishe/Distributor and MSDE which I have
set a subscriber. I have a sample Database FTP_REPL which I have
published. whenever I try to run merge Agent it succssfully connects
to the publisher, Distributor,but it gives error "The schema script
<ScriptName> could not be propagated to the subscriber".
Plz anyone help me to resolve this problem.
Locate your agent, right click on it, select agent properties, and ensure
the job owner is sa. Then restart your agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ruchir" <ruchirdhar@.gmail.com> wrote in message
news:88216eb7.0501100407.d4c3f56@.posting.google.co m...
> Hello All,
> I am implementing Pull merge replication over internet, I have a sql
> server which I have set as Publishe/Distributor and MSDE which I have
> set a subscriber. I have a sample Database FTP_REPL which I have
> published. whenever I try to run merge Agent it succssfully connects
> to the publisher, Distributor,but it gives error "The schema script
> <ScriptName> could not be propagated to the subscriber".
> Plz anyone help me to resolve this problem.

Merge Replication over internet Problem: Couldn't propagate schema to the subscriber.

Yesterday I posted my problem on the group I would like to explain it
a bit so that I be able to make things more clear.
1. I have set up a Sql server as publisher/Distributor.
2. Setup MSDE as subscriber
3. Login mode to publisher/subscriber/Distributor is Sql Server
Authentication
and Login Name is SUPERVISOR.
4. Setup a merge pull replication over internet, using FTP snapshot to
be
downloaded from my FTP site 61.11.103.3
5. After all this setting I ran merge agent at the subscriber site
(owner of
the agent is SUPERVISOR)using sp_start_job stored procedure.
6. Following is the list of message that I can see in Job history of
merge
agent.
A. Connecting to Subscriber.
B. Connecting to Distributor.
C. Initializing.
D. Connecting to Publisher.
E. Retrieving publication information.
F. retrieving subscription information.
G. Snapshot files will be downloaded via FTP.
H. connecting to FTP site '61'11.103.3'
I. The subscription has been marked for reinitialization,
reinitialization
will occur next time you synchronize this subscription.
J. The Schema script '\\IM19\C$\Program Files\Microsoft Sql
Server\MSSQL\Repldata\FTP\IM19_FTP_REPL_FTPREPL\20 050110104749
\FTPDB_1.sch'could not be propagated to the subscriber.
K.Process could not retrieve file
'IM19_FTP_REPL_FTPREPL\20050110104749
\FTPDB_1.sch' from FTP site 61.11.103.3
L. The schema script could not be propagated to the
subscriber,Access denied:
File not Found.
M. The job failed. The job invoked by supervisor,the last step was
step 1.
Plz help me out, Hilary has mentioned in reply to my last mail to
change owner of the merge agent to 'Sa' but this is not working out
still I m getting the same problem.
Its very urgent and important too..
Ruchir,
Based on the error message it seems ftp login doesn't have the permission to
access the snapshot folder share on your distributor box. Please make sure
that ftp login has read permission to your snapshot folder. You can verify
it by using the ftp get cmd for your ftpdb_1.sch file.
thanks - Deepak
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Ruchir" <ruchirdhar@.gmail.com> wrote in message
news:88216eb7.0501102129.51fa8110@.posting.google.c om...
> Yesterday I posted my problem on the group I would like to explain it
> a bit so that I be able to make things more clear.
> 1. I have set up a Sql server as publisher/Distributor.
> 2. Setup MSDE as subscriber
> 3. Login mode to publisher/subscriber/Distributor is Sql Server
> Authentication
> and Login Name is SUPERVISOR.
> 4. Setup a merge pull replication over internet, using FTP snapshot to
> be
> downloaded from my FTP site 61.11.103.3
> 5. After all this setting I ran merge agent at the subscriber site
> (owner of
> the agent is SUPERVISOR)using sp_start_job stored procedure.
> 6. Following is the list of message that I can see in Job history of
> merge
> agent.
> A. Connecting to Subscriber.
> B. Connecting to Distributor.
> C. Initializing.
> D. Connecting to Publisher.
> E. Retrieving publication information.
> F. retrieving subscription information.
> G. Snapshot files will be downloaded via FTP.
> H. connecting to FTP site '61'11.103.3'
> I. The subscription has been marked for reinitialization,
> reinitialization
> will occur next time you synchronize this subscription.
> J. The Schema script '\\IM19\C$\Program Files\Microsoft Sql
> Server\MSSQL\Repldata\FTP\IM19_FTP_REPL_FTPREPL\20 050110104749
> \FTPDB_1.sch'could not be propagated to the subscriber.
> K.Process could not retrieve file
> 'IM19_FTP_REPL_FTPREPL\20050110104749
> \FTPDB_1.sch' from FTP site 61.11.103.3
> L. The schema script could not be propagated to the
> subscriber,Access denied:
> File not Found.
> M. The job failed. The job invoked by supervisor,the last step was
> step 1.
>
> Plz help me out, Hilary has mentioned in reply to my last mail to
> change owner of the merge agent to 'Sa' but this is not working out
> still I m getting the same problem.
> Its very urgent and important too..

Merge replication Option

Hi
I am looking for a way to avoid the option of 'Yes initialize the Schema and
the data' (which I suspect is the source of some problems) when I create a
new subscription
Thank you,
Samuel
Its no problem to do this with named subscriptions in merge replication.
Anonymous requires a snapshot.
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
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:%23768oty4GHA.1460@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am looking for a way to avoid the option of 'Yes initialize the Schema
> and the data' (which I suspect is the source of some problems) when I
> create a new subscription
> Thank you,
> Samuel
>
>
|||How can I cheese a Named Subscription?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23owZLBz4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Its no problem to do this with named subscriptions in merge replication.
> Anonymous requires a snapshot.
> --
> 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
>
> "Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
> news:%23768oty4GHA.1460@.TK2MSFTNGP05.phx.gbl...
>
|||What you are referring to is a nosync initialization. I have a couple of
articles on this that will hopefully help a bit:
http://www.replicationanswers.com/No...alizations.asp
http://www.replicationanswers.com/NoSyncOn2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Merge replication nested view problem

Using SQL 2005 as pub and SQL EXPRESS as sub using Merge replication. Got the following error message

The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber.
Error Detail:
The schema script 'CD_InTransit_v_153.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164)
Get help: http://help/MSSQL_REPL20164
Invalid object name 'dbo.Debit_v'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

According to error message, it seems that debit_v is missing. However, I cannot control the sequence of view to replicate. How can I solve this problem

That's why we stopped replicating views and stored procedures.
We first subscribe (tables) and then run a script which creates views and SPs.
Occuring db-changes are quite easily: change them on the publisher, reinit all subscribers and after the first synch remove and recreate all views and SPs.
Might not be the most elegant solution, but it works (as long as you "know" your subscribers).

regards
Aline
|||So that is the nested view problem.. however, I just tried it on transactional with update replication.. everything goes fine. error only happens on merge replication.

Anyways, any other work around.. I really don't want to do any manual work. Any option that i can turn off so that it will not check reference view when create new view.

|||either deploy them as a post snapshot script, or add them to all subscribers using sp_addscriptexec (only if your subscribers were deployed via a unc).

You might also want to look at using snapshot replication to deploy them.|||I should have mention that this happen for the first time replication to subscriber. So when apply the new snapshot to subscriber and got this error. Probably caused by nested view.

So, do you mean I manually copy all those snapshot file to subscriber pc and run them?

May be I dont' understand your answer. Can you give a little more detail on it? THX.. I am really newbie on replication. Please kindly help.

Merge replication- How to drop article and than add it again

Hi All,
I need to drop an article from live merge publication and than add it back
again with different schema (column, PK etc’) . I can’t use
sp_dropsubscription for every subscriber and sp_droparticle because it is not
transactional / snapshot publication.
Do I have any other alternative other than reinitialize the whole
publication? .
Thanks,
Eyal
Unfortunately not in the case of merge.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||There is more info here
http://msdn.microsoft.com/library/de...limpl_22pf.asp
about managing schema changes with merge replication.
Thanks
Nabila Lacey
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:05D80E20-9EC0-4A00-BF56-210C868F3617@.microsoft.com...
> Hi All,
> I need to drop an article from live merge publication and than add it
back
> again with different schema (column, PK etc') . I can't use
> sp_dropsubscription for every subscriber and sp_droparticle because it is
not
> transactional / snapshot publication.
> Do I have any other alternative other than reinitialize the whole
> publication? .
>
> Thanks,
> Eyal

Merge Replication Between two Existing Databases?

Hi gang,

We have a couple of databases on separate servers that have exactly the same schema, and we would like to set up merge replication between them.

Is that possible? The few times we've experimented on test databases, the subscriber database has been trashed and rebuilt using data from the publisher, rather than preserving data from both databases.

I'm really new to replication in general, so any kind of advice would be helpful.

Cheers,
Matt

Yes it's possible, see topic in Books Online "Initializing a Merge Subscription Without a Snapshot".

|||Thanks Greg. I guess sometimes the help really is helpful!

Merge replication between same schema databases but different data

Hi all.

I 'm trying to set up merge replication between two servers that have the same schema databases. The two database have the majority of there data the same but as well as data inserted at a later time independently on the two servers. (The two servers were connected in a merge replication scheme that failed at some later time and replication was paused, but users continout to insert data indepentedly at the two servers.)

I need to get them up and running.

I cleaned replication at both servers, I recreated the publication at the puplisher distributor and all is fine.

When I create a push subscription to the subscriper I get the error invalid column name 'rowguidcol' .

I so far managed to have merge replication running on two identical databases (schema and data).

Just some thoughts. After some reading I found that it might be related to identities and identity range or indexes. I set the identity seed and increment at 2, 2 at publisher and at 1,2 at subscriper. (On the same tables at puplisher and subscriber.) Is that ok? is that the way to do it?

Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.

Thanks allot for any help

Version.

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Hello,

I suspect there is an Identity range management issue in your app. If you just got publisher and one subscriber, suppose you can partition the Identity range as

Publisher from 1 - 1000 and Subscriber 1001 - 2000. Both step = 1.

So when you create the the merge article in sp_addmergearticle, you can specify

@.auto_identity_range='true', @.pub_identity_range = 1000, @.identity_range = 1000, @.threshold = 85.

For more details, please refer to http://msdn2.microsoft.com/en-us/library/aa237098(SQL.80).aspx.

Thanks.

|||

Thanks for the advise L Zhou

Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.

|||

Hello George,

Please try this, running the follow DDL on the subscriber database.

ALTER TABLE [table_need_to_be_merged] ADD rowguid uniqueidentifier ROWGUIDCOL

Drop the subscription and re-create it with "NOSYNC" option. (SELECT "No, the Subscriber already has the schema and data" from the "Push Subscription Wizard" on the "Initialize Subscription" Page).

The subscription is created and the error message "Invalid column name 'rowguidcol'" should be disappeared.

If this method is still not working for you, I may need to look at your table schema.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Thanks for the answer Zhou

I did as you said and now I do not get the rowguidcol error, but now the conflict tables are not created

Any sugestions?

Thanks

|||

How about drop pubilcation and subscription by executing sp_replicationdboption and re-create the merge publication and subscription, still use "NoSync" option. Please make sure that rowguid column is not dropped.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

merge replication and snapshots

I am having the huge db of 80 GB and trying to configure merge replication. The intial snapshot application is failing due to some schema issues. I have made some necessary changes to avoid the schema issues. If I try start the merge agent it is going to re-initialize the snapshot and reapplying the whole snapshot will take atleast 10-15 hrs.

Is there any way to resume the snapshot from the point of failure.. avoiding the reapplying all the data which has already got transfered to subscriber.You cannot restart from the point of failure. You can do it w/o applying the snapshot. Look here:

http://technet.microsoft.com/en-us/library/ms152488.aspx

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 conflict tables.

If you are making schema changes you should be using sp_repladdcolumn, and
sp_repldropcolumn.
You can't delete the conflict tables using EM, but you can using
sp_droptable through ISQLW
You really don't have to delete these tables. If you are creating a new
publication SQL Server will detect these existing tables and then create new
ones based on the existing name but incrementing by 1 letter.
So conflict_pubs2_authors would be the first table, aonflict_pubs2_pubs the
second, bonflict_pubs2_pubs the third, donflict_pubs2_pubs the fourth, etc.
"mary" <mary@.dbagua.com> wrote in message
news:eN3enz0FEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi, I configured a merge replication with pull subscription. I need to
> do some changes on the table structure so, i delete the replication, do
> the changes and configure the replication again.
> I saw that every time the publication is configured it creates some
> conflict tables but dont delete the conflict tables that the first
> publication used.
> I try to delete this tables but i cant because are system tables.
> Do you know if there is a way that i can delete this tables?
> Thanks a lot for your help.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
I was having trouble adding column to tables that are=20
being replicated using EM and I get a message saying=20
that "Cannot alter column ... because it is 'REPLICATED'.=20
I was directed to the following knowledge base article=20
listed below to correct the problem. Will using=20
sp_repladdcolumn and sp_repldropcolumn prevent this?
http://support.microsoft.com/default.aspx?scid=3DKB;EN-
US;811899

>--Original Message--
>If you are making schema changes you should be using=20
sp_repladdcolumn, and
>sp_repldropcolumn.
>You can't delete the conflict tables using EM, but you=20
can using
>sp_droptable through ISQLW
>You really don't have to delete these tables. If you are=20
creating a new
>publication SQL Server will detect these existing tables=20
and then create new
>ones based on the existing name but incrementing by 1=20
letter.
>So conflict_pubs2_authors would be the first table,=20
aonflict_pubs2_pubs the
>second, bonflict_pubs2_pubs the third,=20
donflict_pubs2_pubs the fourth, etc.
>
>"mary" <mary@.dbagua.com> wrote in message
>news:eN3enz0FEHA.2876@.TK2MSFTNGP09.phx.gbl...
subscription. I need to
the replication, do
creates some
that the first
system tables.
tables?
http://www.codecomments.com ***
>
>.
>
|||Hi Emma,
Yes. Using sp_repladdcolumn and sp_repldropcolumn would solve your problem while changing the schema for any table.
Regards,
Karthik.

Merge replication and Clustered Indexes

Group,

I am getting the following error during replication of Database to a client:

The schema script 'Statutes_6.dri' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

Invalid locale ID was specified. Please verify that the locale ID is correct and corresponding language resource has been installed. (Source: MSSQLServer, Error number: 7696)
Get help: http://help/7696

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. (Source: MSSQLServer, Error number: 319)

The database is relatively small, only about 5 tables but there is a clustered Full-text Index.

Any ideas?

Thanks,

Frank

Frank,

Can you figure out which statement in Statutes_6.dri is causing the error? We have some known issue of Full text index when invalid LCID is used. (You can find the problemetic statement by running profiler trace, or just open up Statutes_6.dri and try to execute statements in that file one by one, of course, some other snapshot files may need to be run so proper required schemas are present at subscriber db).

Also can you provide more info about your environment, like version of SQL Server (2005, rtm, or sp1?), same collation settings at publisher and subscriber db/table?

Thanks,

Zhiqiang Feng