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 through FTP/Emails

NNTP-Posting-Host: 61.95.221.55
Path: TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.connect:42289 microsoft.public.sqlserver.dts:47716 microsoft.public.sqlserver.programming:440288 microsoft.public.sqlserver.replication:50654 microsoft.public.sqlserver.server:339057 microsoft.public.s
qlserver.xml:23307 microsoft.public.sqlserver.clients:28442
Hi,
I want to use SQL Server replication in a scenario where the Publisher and
multiple subscribers are connected over a very low bandwidth.
Is that possible to write down some scripts/programs which does the
following
1. Create a file containing changes made locally during that day on each of
the subscribers. (This size of this file should be as small as possible)
2. Compress these files and Upload to the Publisher throught FTP or by
emails.
3. Publisher after receiving files from all the subscribers will merge the
changes from each of the subscribers.
4. Publisher then makes a fresh file containing changes for each of the
subscriber. This would contain collective changes from all the subscribers
which are releavant to each of the subscribers.
5. These files are then downloaded by each of the subscribers and the
changes are merged into their copy.
I'd prefer to use it in a scenario where data is partially replicated at
each of the subscribers however even a full replication is better than not
having anything.
Thanks in Advance,
Puneet
Puneet,
you require updating subscribers who have autonomy. In replication topologies, this limits the choice to merge replication or transactional replication with queued subscribers. In either case you can't take the changes and email them - they must be read
by the appropriate agent. You could implement what you want using DTS to export/import CSV files. Obviously it would be a highly customised solution, but I have done similar things this way.
HTH,
Paul Ibison
|||Hi Paul,
I understand it requires an agent which is capable of understanding the
format in which the changes have been recorded and put into the file. The
point I'm trying to make is the requirement that the agent should be able to
do its work using one server at a time. The problem area is access to both
the servers together is not possible, or if it is possible, it is over a
very slow connection.
Using DTS would be a huge task. I strongly feel that it is possible to
develop a standard solution for requirements like mine and hope one already
exists.
Thanks,
Puneet
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:C156E629-4BDE-4EF4-B338-95F1888CA619@.microsoft.com...
> Puneet,
> you require updating subscribers who have autonomy. In replication
topologies, this limits the choice to merge replication or transactional
replication with queued subscribers. In either case you can't take the
changes and email them - they must be read by the appropriate agent. You
could implement what you want using DTS to export/import CSV files.
Obviously it would be a highly customised solution, but I have done similar
things this way.
> HTH,
> Paul Ibison
|||Puneet,
such a solution is not one that I know of in straightforward replication. I
suppose you could cheat a little and use snapshot replication (although this
ends up doing the same as the DTS suggestion). You'd need to flag new
records and reset the flag after replication. If data was partitioned
according to server, then each server could be designated as a publisher,
and records appended to each subscriber. This will only work if the
partitions are strict and data cannot be changed in more than one place.
HTH,
Paul Ibison

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

Hi All,
I have an SQL server 2000 as a distributor & Publisher, in other box SQL
Server 2000 as a subscriber.
Also I have one replication successful running and when I try to create a
new Publication I got the same error for many tables.
SQL could not initialize an article base on table 'table name'
Error 208. Invalid object name 'sysmergeextendedarticleview'
What is the problem? How can I fixed?
Tks in advance
Johnny
Can you please provide more detail about your setup:
1. What type of publication is setup? Is it working correctly?
2. What commands are you using to add publication?
3. If you provide the script used for setting up replication - it will be
easier to understand your issue.
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
"JFB" <jfb@.newSQL.com> wrote in message
news:ORgKceb%23EHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I have an SQL server 2000 as a distributor & Publisher, in other box SQL
> Server 2000 as a subscriber.
> Also I have one replication successful running and when I try to create a
> new Publication I got the same error for many tables.
> SQL could not initialize an article base on table 'table name'
> Error 208. Invalid object name 'sysmergeextendedarticleview'
> What is the problem? How can I fixed?
> Tks in advance
> Johnny
>

Merge replication error

Hi,
I got this error from my SQL Server in merge replication:
[298] SQLServer Error: 8198, Could not obtain information about Windows NT
group/user 'DESIG\CHarchaoui'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
help please
Regards
is your merge agent on your subscriber listed in the PAL?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"chouaib harchaoui" <chouaibharchaoui@.discussions.microsoft.com> wrote in
message news:83785A78-D0B0-437A-9430-7284A260952B@.microsoft.com...
> Hi,
> I got this error from my SQL Server in merge replication:
> [298] SQLServer Error: 8198, Could not obtain information about Windows NT
> group/user 'DESIG\CHarchaoui'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
> help please
> Regards
>
|||Hilary ,
i'm a new DBA and is my first replication,
PAL?
"Hilary Cotter" wrote:

> is your merge agent on your subscriber listed in the PAL?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "chouaib harchaoui" <chouaibharchaoui@.discussions.microsoft.com> wrote in
> message news:83785A78-D0B0-437A-9430-7284A260952B@.microsoft.com...
>
>
|||Publication Access List - available on the publication properties. Basically
this is a list of the logins that are granted access to the publication.
When a user or replication agent attempts to log in to a Publisher, SQL
Server 2000 first checks to see if the login is in the PAL
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank's, now my replication start but i have some problems in merge agent,
because i have one table with a column data type : Timestamp , how change
this data type with another.
help me please .
"Paul Ibison" wrote:

> Publication Access List - available on the publication properties. Basically
> this is a list of the logins that are granted access to the publication.
> When a user or replication agent attempts to log in to a Publisher, SQL
> Server 2000 first checks to see if the login is in the PAL
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Merge Replication and FK constraints

This question is also posted in public.sqlserver.ce, Sorry!
Scenario:
SQL 2000 with 3 tables A, B ans C related to each other with FK constraints.
B has a relation to A and C has a relation to B
In the FK columns the option 'Enforec relationship for replication' is
unchecked
Merge replication to SQL CE
I would like to take some rows from A set up a filter to include related
rows from B and ALL rows from C
In my mind this should work. Despite the fact that there will be some rows i
C with no parent row i B the replication should execute whitout errors.
I got the following confusing error message:
@."Exception 1: Error Code: 80004005 Message : Run Minor Err.: 28557 Source
: Microsoft SQL Server 2000 Windows CE Edition Err. Par. : data
source=\Program Files\VPJ\VPJ.sdf;Exception 2: Error Code: 80040E14 Message
: The OLE DB Execute method failed. The SQL statement is not valid. [,,,SQL
statement,,] Minor Err.: 28560 Source : Microsoft SQL Server 2000 Windows
CE EditionException 3: Error Code: 80040E14 Message : There was an error
parsing the query. [Token line number,Token line offset,,Token in error,,]
Minor Err.: 25501 Source : Microsoft SQL Server 2000 Windows CE Edition
Num. Par. : 1 Num. Par. : 145 Err. Par. : NOT"
Anyone who have succeeded to replicate between SQL 2000 and SQL CE in a
similar scenario
/Thomas B
We do not support the NOT FOR REPLICATION option with SQL Server CE. Make
sure that the box is checked
thanks
gopal

Merge replication after a restore publisher

Merge replication works fine, but if I restore publisher database on SQL
Server 2000,
and I have some records in SQL Server CE database was already merged (after
last backup),
when I synchronize, these records exists on SQL Server CE, but they are lost
on
SQL Server 2000.
I have to put these records on SQL Server 2000, but how can I compare
records that are
both on SQL Server 2000 and SQL Server CE ?
Thanks.
You can update these records in the SQL CE database again, and they should
replicate to back to the publisher the next time you run the activeX
program.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"El Ricoun" <elricoun@.yahoo.fr> wrote in message
news:4207ca97$0$6615$8fcfb975@.news.wanadoo.fr...
> Merge replication works fine, but if I restore publisher database on SQL
> Server 2000,
> and I have some records in SQL Server CE database was already merged
(after
> last backup),
> when I synchronize, these records exists on SQL Server CE, but they are
lost
> on
> SQL Server 2000.
> I have to put these records on SQL Server 2000, but how can I compare
> records that are
> both on SQL Server 2000 and SQL Server CE ?
> Thanks.
>
|||Ok, thanks, it works fine, but I have to update all records of all tables
on SQL Server CE. Is it possible to use s_Generation column to know
which record that I have to update ?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> a crit dans le message de
news:egF1ekZDFHA.936@.TK2MSFTNGP12.phx.gbl...
> You can update these records in the SQL CE database again, and they should
> replicate to back to the publisher the next time you run the activeX
> program.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Now available on Amazon.com
>
http://www.amazon.com/gp/product/off...?condition=all
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "El Ricoun" <elricoun@.yahoo.fr> wrote in message
> news:4207ca97$0$6615$8fcfb975@.news.wanadoo.fr...
> (after
> lost
>