Merge Replication with filters throwing error

I'm synchronizing SQL Mobile 2005 with SQL Server 2005 using Merge
Replication. I've filtered a table like this:
WHERE HOST_NAME() = convert(nvarchar(100), patientId)
"patientId" is uniqueidentifier type column, the primary key of
"Patient" table.
The error when synchronizing is the following:
"The row operation cannot be reapplied due to an integrity violation.
Check the Publication filter. [ Table = Appointment,Operation =
Insert/Update,RowGuid = {F64E1023-01AD-DA11-8ED9-0004E2B45615} ]}"
Table Patient is joined with table Appointment but I don't understand
the error.
I think this sentence worked in SQL Server 2000 and SQL Server E 2.0.
I would appreciate any help on this.
Regards.
I've also tried same filter for Appointment database, and here I don't
receive any error, but the filtering is not correctly done.
Can anybody tell me if the following filtering is correct: WHERE
HOST_NAME() = convert(nvarchar(100), patientId)
Be sure this filtering has correctly worked in the past.
Any ideas please?
Regards.

merge replication where deletes are required

Hello all,
I am desperatly need help, our setup is a SQL 2005 server, with merge
replication.
Filtered rows and 120 pocket pcs (subscribers).
We need to delete 100000 rows each night, plus minor inserts, and every time
each pocketpc gets all deletes which kills me and them. I have searched the
net how to achive this without any luck.
I have tried to "reinitilize all" with upload changes, + create new snapshot
+ delete the subscriber and nothing help, all deletes get sent to the
subscriber.
I cannot believe that there are no solution for this common problem, and why
does all the deletes get out to all the subscribers when we have filtered
rows and it works fine with inserts or update?
Please help, anything is better than what we have, and the nightly deletes
are required.
Perhaps you could temporarily turn off the delete tracking:
http://msdn2.microsoft.com/en-us/library/ms146947.aspx?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Merge Replication Updates on Initialise (SQLServer 2005)

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?

I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

Hi Steve,

When you initialize the client, you are probably using dynamic snapshot, and when those dynamic snapshots become old the initializing will also need to get the incremental changes which happened after the old dynamic snapshot generated.

You can choose to have the snapshots refreshed on a schedule so new Subscribers that subscribe to a partition for which a snapshot has been created will receive an up-to-date snapshot.

For more information, please refer to Books online topic: Snapshots for Merge Publications with Parameterized Filters

Hope it helps.

Wanwen

|||I assume you mean the snapshot agent running regularly - which it does every night.

Seems to be something else - I'll just have to keep looking|||

If you are using dynamic filtering, there are 2 snapshots to consider:

1. Regular snapshots

2. Dynamic snapshots

The regular snapshot do not have data in them.

The dynamic snapshots are the ones that have data in them.

You need to make sure that both are run before your subscriber syncs so that they can use the bcp files from the dynamic snapshot to complete the sync process in a much more timely manner (faster).

sql

Merge Replication Problem (814916)

I got a big problem with the Merge replication. detail as below:
Failed to enumerate changes in the filtered articles.
Category: NULL Source:
Merge Replication Provider
Number: -2147200925
Message: Failed to enumerate changes in the filtered articles.
Category: COMMAND
Source: Failed Command
Number: 0
Message: create table #belong_agent_-2147483646 (tablenick int NOT NULL,
rowguid uniqueidentifier NOT NULL,generation int NULL, lineage
varbinary(255) NULL, col v varbinary(2048) NULL)
Category: SQLSERVER
Source: ServerName
Number: 170
Message: Line 1: Incorrect syntax near '-'.
the Bug# is 363726 (SHILOH_BUGS)
KB814916
I do know how to fix the problem. It seems the service pack3 does not solve
the problem. i am very very upset with this problem since the boss order me
to finish the replication within two weeks.
would anybody help me. save my life.
As far as I know this isn't fixed in sp3a, but is a separate requested fix
from MS. Details are in
http://support.microsoft.com/default...;en-us;814916.
HTH,
Paul Ibison
|||Sorry, i have saw this article much times. but Microsoft have not given the
hot fix. just said "To resolve this problem immediately, contact Microsoft
Product Support Services to obtain the fix. For a complete list of Microsoft
Product Support Services phone numbers and information about support costs,
visit the following Microsoft Web site"
how can i got the hot fix.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> д?
:epZtOjhdEHA.3964@.TK2MSFTNGP10.phx.gbl...
> As far as I know this isn't fixed in sp3a, but is a separate requested fix
> from MS. Details are in
> http://support.microsoft.com/default...;en-us;814916.
> HTH,
> Paul Ibison
>
|||I'm not too sure if I have understood you - did you get in
touch with PSS through the link provided, quote the KB
article and then the MS representative didn't give you the
hotfix? If this is the case what reason was given?
Regards,
Paul Ibison

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production

environment. The architecture is filtered merge replication between two SQL

Server 2000 SP4 databases. The publisher is standard edition and the subscriber

is personal edition.

The issue is that is I update certain rows on the subscriber the data is not

replicated to the publisher. Inserts and deletes seem to replicate correctly.

The issue is specific to certain rows in some tables. If I update other rows

the updates replicate correctly. The subscription has been re-initialized once

and it did not fix the issue. The replication process indicates that there was

not data to merge and the subscriber updates remain and are at that point

different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and

checking the publisher database. The synchronization process is executed using

the merge agent ActiveX control.

This issue started occurring after a large conflict was generated during an

earlier synch on this particular subscriber. We have other subscribing

databases that are working fine.

I have already spent 8+ hours on the phone with TCS trying to solve this issue.

If anyone has any ideas or has seen this type of issue before I would appreciate

any direction to try to fix this issue.

Thanks

I ran another test today. I created a new database on a separate

server and added a subscription to the publication and synced. The data came

down correctly but my local updates to the specific rows where still ignored

during the sync process.

I then updated the problem row in the publisher, synced to the new subscriber

and now I can update the local row and it will sync back up to the publisher.

Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on

the subscriber will not replicate to the publisher. These rows where all

involved in a conflict at one time that was resolved. I setup a new database,

subscriptions and synced and updates to these 5 rows will not propagate to the

server.

I am updating a GUID field that is not the primary key and has no relation to

the partition which is based on a join filter between these tables. The field I

am updating is a last update key and its only purpose it to have a unique GUID

that changes with each update. I can change other fields and the changes do not

go to the publisher as well. The issue is definitely related to these rows as

other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the

rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.|||

I have a question related to this issue:

I executed the sp_showreplicainfo stored procedure on the rows that will not

update on the subscriber. The row info returned two rows. One with the server

name set to the publisher database and another with the server name set to one

of the subscribers (not the one I am testing with).

Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.|||

Something interesting came up related to this issue while

working with Microsoft tech support. I have twotables in my database that

have a one-to-one relationship with each other. In the two tables the rowid is the primary

key. This means that I have duplicate rowid values in different tables (The

same rowid value is in 2 tables at the same time).

Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production environment. The architecture is filtered merge replication between two SQL Server 2000 SP4 databases. The publisher is standard edition and the subscriber is personal edition.
The issue is that is I update certain rows on the subscriber the data is not replicated to the publisher. Inserts and deletes seem to replicate correctly. The issue is specific to certain rows in some tables. If I update other rows the updates replicate correctly. The subscription has been re-initialized once and it did not fix the issue. The replication process indicates that there was not data to merge and the subscriber updates remain and are at that point different form the publisher row values after the sync.
I can reproduce the issue by updating the row, running the sync process, and checking the publisher database. The synchronization process is executed using the merge agent ActiveX control.
This issue started occurring after a large conflict was generated during an earlier synch on this particular subscriber. We have other subscribing databases that are working fine.
I have already spent 8+ hours on the phone with TCS trying to solve this issue. If anyone has any ideas or has seen this type of issue before I would appreciate any direction to try to fix this issue.
Thanks

I ran another test today. I created a new database on a separate server and added a subscription to the publication and synced. The data came down correctly but my local updates to the specific rows where still ignored during the sync process.
I then updated the problem row in the publisher, synced to the new subscriber and now I can update the local row and it will sync back up to the publisher. Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on the subscriber will not replicate to the publisher. These rows where all involved in a conflict at one time that was resolved. I setup a new database, subscriptions and synced and updates to these 5 rows will not propagate to the server.
I am updating a GUID field that is not the primary key and has no relation to the partition which is based on a join filter between these tables. The field I am updating is a last update key and its only purpose it to have a unique GUID that changes with each update. I can change other fields and the changes do not go to the publisher as well. The issue is definitely related to these rows as other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.
|||

I have a question related to this issue:
I executed the sp_showreplicainfo stored procedure on the rows that will not update on the subscriber. The row info returned two rows. One with the server name set to the publisher database and another with the server name set to one of the subscribers (not the one I am testing with).
Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.

|||

Something interesting came up related to this issue while working with Microsoft tech support. I have twotables in my database that have a one-to-one relationship with each other. In the two tables the rowid is the primary key. This means that I have duplicate rowid values in different tables (The same rowid value is in 2 tables at the same time).
Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan

|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count,*FROM sysmergearticles WHERE name LIKE'%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline
|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication not replicating updates

I have an issue that is only occurring in a production

environment. The architecture is filtered merge replication between two SQL

Server 2000 SP4 databases. The publisher is standard edition and the subscriber

is personal edition.

The issue is that is I update certain rows on the subscriber the data is not

replicated to the publisher. Inserts and deletes seem to replicate correctly.

The issue is specific to certain rows in some tables. If I update other rows

the updates replicate correctly. The subscription has been re-initialized once

and it did not fix the issue. The replication process indicates that there was

not data to merge and the subscriber updates remain and are at that point

different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and

checking the publisher database. The synchronization process is executed using

the merge agent ActiveX control.

This issue started occurring after a large conflict was generated during an

earlier synch on this particular subscriber. We have other subscribing

databases that are working fine.

I have already spent 8+ hours on the phone with TCS trying to solve this issue.

If anyone has any ideas or has seen this type of issue before I would appreciate

any direction to try to fix this issue.

Thanks

I ran another test today. I created a new database on a separate

server and added a subscription to the publication and synced. The data came

down correctly but my local updates to the specific rows where still ignored

during the sync process.

I then updated the problem row in the publisher, synced to the new subscriber

and now I can update the local row and it will sync back up to the publisher.

Does anyone have any idea what happened to cause this?

|||

can you tell us what's specific about the columns/rows you're updating? Are you updating the primary key column? Are you updating a column that's moving in and out of a partition?

|||

There are actually 5 tables with 6 rows that if I update on

the subscriber will not replicate to the publisher. These rows where all

involved in a conflict at one time that was resolved. I setup a new database,

subscriptions and synced and updates to these 5 rows will not propagate to the

server.

I am updating a GUID field that is not the primary key and has no relation to

the partition which is based on a join filter between these tables. The field I

am updating is a last update key and its only purpose it to have a unique GUID

that changes with each update. I can change other fields and the changes do not

go to the publisher as well. The issue is definitely related to these rows as

other rows will propagate to the publisher in the same tables.

Updating the row on the server seemed to fix one of the

rows. The others are still broken.

Thanks for your help on this tricky issue.

|||

Since you say that creating a new subscription to this publication still faces the same problem, can you try this:

1. Run the snapshot agent at the publisher.

2. Create a new subscription to this publication

3. Synchronize this subscription

4. Try the updates at the subscriber and synchronize

5. See if your updates are sent or not.

|||I have tried this test and the updates for these specific rows do not replicate from the subscriber to the publisher.|||

I have a question related to this issue:

I executed the sp_showreplicainfo stored procedure on the rows that will not

update on the subscriber. The row info returned two rows. One with the server

name set to the publisher database and another with the server name set to one

of the subscribers (not the one I am testing with).

Is this expected behavior? The documentation on this is a bit thin.

|||

Hi Jeff , I'm having the same issue that you , but for me this start happeing after i install SP4.

I can't reinit all my suscriber (500+) , did you find the problem or a solution to this issue.

thxs. You can replay to my email at tutipedro@.hotmail.com

|||I am still working with Microsoft Technical support to try to find out why this happened. I did notices that when I update the rows that have the issue on the publisher side it seems to "unstick" the row and subscriber updates now work.|||

Something interesting came up related to this issue while

working with Microsoft tech support. I have twotables in my database that

have a one-to-one relationship with each other. In the two tables the rowid is the primary

key. This means that I have duplicate rowid values in different tables (The

same rowid value is in 2 tables at the same time).

Could this have caused my issue? Is this a supported schema for replication?

|||

No is not an issue since the msmerge_contents include the article id table_nick.

|||Sorry - I posted this to the wrong thread - not sure how to delete

It seems like I've come accross the same issue this weekend (or something close) - that is, we are using merge replication on SQL2K SP4 - I wanted to increase a column size slightly so using a variation of the post on the Replication Answers website.

That is, store the data in a temp table, drop the column, re-add the column with the increase value, and then perform an update on the column - the first updates will make it to all the republisher and children. I've noticed that performing updates on this column for any value that was included in the first update does not propagate to the children. In the profiler, I can see the system procedure being called, but when one should field the new value it has "default".

What ended up being the issue is that we are using continuous replication so the skema changes got replicated to the subscribers before the snapshot finished running. What fixed the issue was to manual stop the merge agent, run the skema script, run the snapshot, and then sync. This time all was good.

-Ryan|||

I have a question for you ..

run this query on your server

SELECT missing_cols, missing_col_count, * FROM sysmergearticles WHERE name LIKE '%<YOUR TABLE NAME>%'

Open the replication update triger on your server look out for this line of code

/* only do the map down when needed */

set @.missingbm = <SOME VALUE>

Are the missing_cols value equal to the @.missingbm in the trigger ?

|||Hello Pedro

I've got a similar problem, but changes from the publisher are not send to the subscriber after schema and datachanges. (No error message nor any conflicts indicated)

I've tried your instructions. And yes, I've got the same values (0x18). What does this mean? Can you explain it to me?

Thanks a lot
Aline|||

Aline i'm short of time now a quick solution is , the replication triger for the tables that you know that have that problem should be the same in the susciber that in the server. Take the server (publisher) triger and run in the suscriber.

Merge Replication Filtered Publication

Hi People,
I need some help.
I am using SQL Server 2000 and have created a publication using Merge
Replication. One of the tables are filtered as follows:
SELECT <published_columns> FROM [dbo].[tblFingerPrint] WHERE
recordid in (select fingerprintid from tblKeyHolder)
The reason I do this is beacause I only want rows from fingerprint table to
be at the subscriber where the fingerprint ID is being used.
But for some reason it doesnt work, it will work when I reinitilize the
subscription, but not when I do a normal synch.
Can anyone help?
Thanks in advance
Warren
Warren,
can you try adding the table tblKeyHolder to the publication and having an
explicit join?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul I will give that a try.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OlRQ7lcvFHA.664@.tk2msftngp13.phx.gbl...
> Warren,
> can you try adding the table tblKeyHolder to the publication and having an
> explicit join?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi,
I tried the following
SELECT <published_columns> FROM [dbo].[tblFingerPrint] INNER
JOIN [dbo].[tblKeyHolder] ON fingerprintid =
[dbo].[tblFingerPrint].recordid
and got this error:
Error 107: The column prefix 'dbo.tblFingerPrint' does not match with a
table name or alias name used in the query.
A column used in filter clause 'fingerprintid =
[dbo].[tblFingerPrint].recordid' either does not exist in the table
'tblKeyholder' or cannot be excluded from the current partition.
Any ideas?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OlRQ7lcvFHA.664@.tk2msftngp13.phx.gbl...
> Warren,
> can you try adding the table tblKeyHolder to the publication and having an
> explicit join?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Hi Warren,
what I was thinking is to use the Filter Rows tab in the publication
properties and using the create join option. You can add 1=1 to the Filter
clause if this is not needed.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication Error - Failed to enumerate changes in the filtered article

Hi,
I am using Merge Replication and have filtered some of the tables for teh
subscriptions, however, everything was working fine, then all of a sudden, I
get this error. (See below)
Does anyone know what would cause this and how I can overcome it?
Thanks in Advance
Warren
************************************************** *
******************ERROR DETAILS******************
************************************************** *
Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
Error Message: Failed to enumerate changes in the filtered articles.
Error Details:
Failed to enumerate changes in the filtered articles.
(Source: Merge Replication Provider (Agent); Error number: -2147200925)
Incorrect syntax near the keyword 'where'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
Incorrect syntax near the keyword 'and'.
(Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
************************************************** *
*******************END OF ERROR******************
************************************************** *
Could you provide more information on your database and replication filters
?
Are replicated objects owned by dbo ?
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:O%23o3zZrKFHA.2640@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using Merge Replication and have filtered some of the tables for teh
> subscriptions, however, everything was working fine, then all of a sudden,
I
> get this error. (See below)
> Does anyone know what would cause this and how I can overcome it?
> Thanks in Advance
> Warren
> ************************************************** *
> ******************ERROR DETAILS******************
> ************************************************** *
> Last command: {call sp_MSsetupbelongs(?,?,?,?,?,1,?,?,1,?,?,?,?,?,?)}
> Error Message: Failed to enumerate changes in the filtered articles.
> Error Details:
> Failed to enumerate changes in the filtered articles.
> (Source: Merge Replication Provider (Agent); Error number: -2147200925)
> ----
--
> --
> Incorrect syntax near the keyword 'where'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> Incorrect syntax near the keyword 'and'.
> (Source: GENCENTRIC_SVR1 (Data source); Error number: 156)
> ----
--
> --
> ************************************************** *
> *******************END OF ERROR******************
> ************************************************** *
>
|||Hi,
Thanks for your reply.
The objects are owned by DBO.
We are using Merge replication will pull subscriber.
Some tables are filtered, in the publication properties, if I go to FILTER
ROWS, then FILTER CLAUSE column, some tables are filtered like so
depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx'
where x makes up the guid.
is that enough information? what else do you need?
Many thanks
"Kestas" <kicker.lt@.noospamm-tut.by> wrote in message
news:%232ddCNsKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Could you provide more information on your database and replication
filters[vbcol=seagreen]
> ?
> Are replicated objects owned by dbo ?
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:O%23o3zZrKFHA.2640@.TK2MSFTNGP09.phx.gbl...
teh[vbcol=seagreen]
sudden,
> I
> ----
> --
> ----
> --
> ----
> --
>
|||Also would be good to know which exact SQL Server version you are runing
SELECT @.@.VERSION
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:%23nEFf6uKFHA.1280@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Thanks for your reply.
> The objects are owned by DBO.
> We are using Merge replication will pull subscriber.
> Some tables are filtered, in the publication properties, if I go to FILTER
> ROWS, then FILTER CLAUSE column, some tables are filtered like so
> depot_system_id = 'xxxxx-xxxxx-xxxxx-xxxxxx'
> where x makes up the guid.
> is that enough information? what else do you need?
> Many thanks
|||Hi,
Publisher:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: )
Subscriber:
MSDE
Kind Regards
Warren
"Kestutis Adomavicius" <kicker.lt@.noospamm-tut.by> wrote in message
news:um0A7yvKFHA.3960@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Also would be good to know which exact SQL Server version you are runing
> SELECT @.@.VERSION
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:%23nEFf6uKFHA.1280@.TK2MSFTNGP09.phx.gbl...
FILTER
>
|||Anyone have any ideas?
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:uZZOIA4KFHA.2860@.TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Publisher:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.2 (Build 3790: )
> Subscriber:
> --
> MSDE
>
> Kind Regards
> Warren
>
> "Kestutis Adomavicius" <kicker.lt@.noospamm-tut.by> wrote in message
> news:um0A7yvKFHA.3960@.TK2MSFTNGP09.phx.gbl...
> FILTER
>
|||can you post your schema and publication script here?
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
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:uj$i292LFHA.4080@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Anyone have any ideas?
>
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:uZZOIA4KFHA.2860@.TK2MSFTNGP10.phx.gbl...
runing
>
|||Hi Hilary,
By publication script, I assume you mean the script to create the
publication (right-click publication --> Generate SQL Script)?
And the schema? Do you want the published databases schema?
can you advise,
thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%2317MDe6LFHA.3988@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> can you post your schema and publication script here?
> --
> 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
> "Warren Patterson" <des@.newsgroups.nospam> wrote in message
> news:uj$i292LFHA.4080@.TK2MSFTNGP10.phx.gbl...
> runing
to
>
|||Anyone able to help on this?
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:OGkKj9FMFHA.3420@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Hilary,
> By publication script, I assume you mean the script to create the
> publication (right-click publication --> Generate SQL Script)?
> And the schema? Do you want the published databases schema?
> can you advise,
> thanks
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%2317MDe6LFHA.3988@.tk2msftngp13.phx.gbl...
> to
so
>

Merge Replication Error

i am having the following error with merge replication:
failed to enumerate changes in the filtered article (source: Merge replication Provider(Agent);error number -2143200925)
does anyone have a resolution for this please.
thanks.Make sure the accounts used have sa privileges on both subscriber server.

Merge replication causing vast amount of data to move.

Hi,
We have setup merge replication with 1 publisher and 5 subscribers. 4 of
which are filtered.
All databases were synched up and everything was great. I then proceeded to
update the data at one
of the subscribers, by inserting 15000 rows or so. Then when next the
subscribers began their synching all manners of amounts of rows of data were
being sent all over the show. I was expecting 15000 rows to be updated at
each subscriber. but no, this was not to happen, seems that there are
updates and inserts happening at the subscribers. Is this a cause of
replication, being caused by the databases trying to make themseleves exact
replicas of each other and hence changing of data via inserts and updates?
Is this going to continue until all the data is exactly the same at each
site.
Is there some way to predict all this? The reason i ask is because we are
using satellite to do the synching and it costs $3.50 per minute.
Can anyone shed some light on this issue? I am trying to get an
understanding of why this is happening?
Warren,
For this 15000 inserts I would expect 15000 inserts on the publisher,
followed by inserts at each subscriber. The number of inserts at the
subscribers would depend on the filters involved, but if there weren't any
filters then I'd expect 15000 records downloaded when the individual
subscriber merge agents run. If you have only performed inserts at one
subscriber and there's no other activity then I'm not sure what is causing
the updates. Can you look at MSmerge_history for the period when you did the
insert so we can see some figures?
select publisher_insertcount, publisher_updatecount, publisher_deletecount,
subscriber_insertcount, subscriber_updatecount, subscriber_deletecount from
dbo.MSmerge_history
Regards,
Paul Ibison
|||Hi Paul,
Thanks for your response.
I forgot to mention that updates were run on the publisher a few days ago.
So perhaps this is the reason for all the updates and deletes that are going
on. In fact I am sure of it. I am sure you are going to agree with me on
this as well. The problem I find is that a subscriber will synch up with the
subscriber today (Updates inserts etc) and tomorrow it will have new updates
and inserts when it synchs again. This is weird surely 1 resynch is enough
especially seeing as no data is being changed by us?
All we want is all the subscribers to be at a point when they all return "No
data Needed to be Merged".
Regards
Warren Patterson
"Warren Patterson" <des@.newsgroups.nospam> wrote in message
news:usvs4yUTEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> We have setup merge replication with 1 publisher and 5 subscribers. 4 of
> which are filtered.
> All databases were synched up and everything was great. I then proceeded
to
> update the data at one
> of the subscribers, by inserting 15000 rows or so. Then when next the
> subscribers began their synching all manners of amounts of rows of data
were
> being sent all over the show. I was expecting 15000 rows to be updated at
> each subscriber. but no, this was not to happen, seems that there are
> updates and inserts happening at the subscribers. Is this a cause of
> replication, being caused by the databases trying to make themseleves
exact
> replicas of each other and hence changing of data via inserts and updates?
> Is this going to continue until all the data is exactly the same at each
> site.
> Is there some way to predict all this? The reason i ask is because we are
> using satellite to do the synching and it costs $3.50 per minute.
> Can anyone shed some light on this issue? I am trying to get an
> understanding of why this is happening?
>
>
|||Warren,
what I'd do is track the generation numbers on publisher and subscriber to see why this is happening. To do this you need to identify which rows are coming over and why/at what point they are changed to cause the merge agent to think they should be replic
ated. The generation numbers are held in MSmerge_contents,MSmerge_genhistory and MSmerge_replinfo. Using these tables you can see what changes are ready to be downloaded per subscriber.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the reply.
Is it possible for you to give me any more detailed info. on how to track
the generation numbers? I havent done this before.
Just a bit of extra info, the subscribers are in another country, so
accessing them is not possible.
Subscriber initiates the satellite dial up. We cant initiate it.
Regards
Warren
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:17D601FE-18D2-4C2B-8A85-6DA521A42003@.microsoft.com...
> Warren,
> what I'd do is track the generation numbers on publisher and subscriber to
see why this is happening. To do this you need to identify which rows are
coming over and why/at what point they are changed to cause the merge agent
to think they should be replicated. The generation numbers are held in
MSmerge_contents,MSmerge_genhistory and MSmerge_replinfo. Using these tables
you can see what changes are ready to be downloaded per subscriber.
> HTH,
> Paul Ibison
|||Warren,
on a subscriber you can get the generation numbers for your article using an inner join:
SELECT MSmerge_contents.generation, testMerge.id, testMerge.name
FROM testMerge INNER JOIN
MSmerge_contents ON testMerge.rowguid = MSmerge_contents.rowguid
There should be different generation values returned in your case. To see which ones haven't yet been synchronized, compare these values to the relevant value of sentgen in MSmerge_replinfo for your subscription (sysmergesubscriptions will tell you the ID
).
The query above in your case might be enough. Just look at the highest value generation number and examine this row. Once you've found the row that has changed, hopefully you can find out who/what has changed it.
Regards,
Paul Ibison
|||Hi Paul,
Thanks for your response, unfortunately, the subscriber is in another
country and I cant access the database from here. But, the data movement
seems to be settling down now. I will have to monitor it for a while and see
what happens and then try and to follow your advice somehow.
Thanks for your help.
Kind Regards
Warren
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3A516C99-34A5-4C21-AA49-716693096412@.microsoft.com...
> Warren,
> on a subscriber you can get the generation numbers for your article using
an inner join:
> SELECT MSmerge_contents.generation, testMerge.id, testMerge.name
> FROM testMerge INNER JOIN
> MSmerge_contents ON testMerge.rowguid =
MSmerge_contents.rowguid
> There should be different generation values returned in your case. To see
which ones haven't yet been synchronized, compare these values to the
relevant value of sentgen in MSmerge_replinfo for your subscription
(sysmergesubscriptions will tell you the ID).
> The query above in your case might be enough. Just look at the highest
value generation number and examine this row. Once you've found the row that
has changed, hopefully you can find out who/what has changed it.
> Regards,
> Paul Ibison

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