Merge SQL 7 to 2000 problem

I am having a problem running a merge replication between
a SQL 7 publisher/distributor and a SQL 2000 subscriber
(push). After the subscription is initialized, the agent
fails with the error "The process could not query row
metadata at the Subscriber." The error details
says "Could not find stored procedure ''." A clip from
the log is below. This same merge replication works fine
from SQL 7 to SQL 7, but fails to SQL 2000. I have tried
2 different SQL 2k boxes with the same error.
The replication job logs in using SQL Server
authentication. Account is system administrator and dbo
on subscriber. Distributor runs under sa. Both Servers
are at latest SP levels. KB search has turned up no help.
Thanks for any ideas!
~~~~~~~~~~~~~~~~~~snip~~~~~~~~~~~~~~~~~~~
Percent Complete: 55
Processing article 'RequestStatusHistory'
Repl Agent Status: 3
chrs4.ITWorkRequest: {call sp_MSenumcolumns (?,?)}
chrs4.ITWorkRequest: {call sp_MSenumchanges(?,?,?,?,?)}
CHHIST.ITWorkRequest: {call sp_MSgetrowmetadata
(?,?,?,?,?,?,?)}{call sp_MSgetrowmetadata(?,?,?,?,?,?,?)}
{call sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}
Percent Complete: 0
The process could not query row metadata at the
Subscriber.
Repl Agent Status: 6
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number:
Message: {call sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call
sp_MSgetrowmetadata(?,?,?,?,?,?,?)}{call sp_M
Repl Agent Status: 3
Percent Complete: 0
Category:SQLSERVER
Source: CHHIST
Number: 2812
Message: Could not find stored procedure ''.
Repl Agent Status: 3
Could not find stored procedure ''.
Disconnecting from Publisher 'chrs4'
Disconnecting from Subscriber 'CHHIST'
Disconnecting from Publisher 'chrs4'
Disconnecting from Distributor 'chrs4'
George,
this is not a supported configuration. For merge replication, a SQL 7.0
publisher can only publish to a SQL 7.0 Subscriber.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the reply. I read the "...Different Versions"
article in books online, but obviously scanned too
quickly. I see the limitation now for merge, but not for
snap and trans. You might help out "Nick Horrocks" with
an answer to his thread "Unable to create Merge
subscription".
George

>--Original Message--
>George,
>this is not a supported configuration. For merge
replication, a SQL 7.0
>publisher can only publish to a SQL 7.0 Subscriber.
>Rgds,
>Paul Ibison
>(recommended sql server 2000 replication book:
>http://www.nwsu.com/0974973602p.html)
>
>.
>
|||Thanks for the prompt - have posted to Nick as well.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

>--Original Message--
>Paul,
>Thanks for the reply. I read the "...Different Versions"
>article in books online, but obviously scanned too
>quickly. I see the limitation now for merge, but not for
>snap and trans. You might help out "Nick Horrocks" with
>an answer to his thread "Unable to create Merge
>subscription".
>George
>
>replication, a SQL 7.0
>.
>

Merge snapshot frequency

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

> Expect slowness whenever you do a data import as there is always the impact
> of the dataload and then there is the added data you have to merge.
> Are you saying if you run the snapshot you get faster sync's? This only
> makes sense if you re-initialize your subscribers after regenerating the
> snapshot.
> If you have anonymous subscribers the snapshot is always generated each time
> you run it. If you have named it is only regenerated if subscribers expire
> or require reinitialization.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
> news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>
>

Merge seems to hang at conflict and conflict is not logged

We have a SQL2005 merge replication database with 8 subscribers. Three of the
subscribers have not successfully replicated. We would appreciate any
additional ideas for correcting this. So far we have some inconsistencies.
For example, the "View Synchronization Status" displays:
"Downloaded 100 change(s) in 'OurTable' (100 updates, 1 conflict)"
and the publisher's "Microsoft Replication Conflict Viewer" displays:
"There are no conflicts to view."
If you have any ideas why conflicts are causing the replication to fail
while the conflict viewer shows no conflicts, we would appreciate your input.
Thanx.
Were there any conflicts on the subscriber side?
You can run the conflict viewer there as well, or if that's not
convenient/possible there are sp_xxx procedures to help you view the
conflict tables on the client...

Merge republish Schema changes

Hello,

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

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

1-Run Snapshot on the Consolidated instance

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

3-Run Regional Snapshot

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

5-Run alter table scripts

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

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

Thank you,

Aaron Lowe

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

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

Consolidated database (original publisher)

publication name, replicate_ddl

Consolidated, 1

Region, 0

Regional database (republisher)

publication name, replicate_ddl

Consolidated, 1

Region, 1

SQL Express database (subscriber)

publication name, replicate_ddl

Consolidated, 0

Region, 1

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

Thanks,

Aaron

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

merge replications causes insert timeout

Hi All,

i need help. i have a merge replication running every 10 minutes. the replication is for 1 database consisting around 50 tables and all 50 tables are marked as article to be published. let's say the publisher is server A and the subscriber is server B. for explanation purpose, let's pick 1 table from the database, let's say table X

in server A, there are applications running that insert and update table X. in server B, there are applications running that select and delete table X. table X at average, has tens of thousands rows.

now comes the problem, every 10 minutes whenever the sql merge agent is running, sometimes whenan application insert a row to table X in server A, it returns an error : "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.". when i stop the replication (delete the subscriber), the row insertion to table X in server A seems fine. then i come to a doubtful conclusion, that the timeout error happened because when the insert statement executes, the replication locks the table for merging.

could somebody verify this? if my conclusion is true, then how am i suppose to do this? (see bold text above). really2 appreciate the help

Yes, merge agent can fail due to deadlocking. WHat you need to ensure is that your application is taking the minimum locks necessary. This is no different than two applications accessing/modifying the same data simultaneously, consider merge replication a second application.

Review your queries, insert/update/delete statement and see how transactions are being used, what locking behavior, queries are written optimally, etc.

Merge Replication? Aaaarghhh!

Can anyone tell me what should be contained in MSrepl_identity_range tables
on both the subscriber and the publisher? Both my tables contain completly
different data - pub has 83 rows and my single sub has only 3.
When are these tables populated and how? Can I populate them manually from a
system SP?
I refer to my previous post where Hilary Cotter thought there might be an
issue with these tables.
When executing : exec sp_MSfetchidentityrange N'CommentType', 0
I get the following error:
Server: Msg 21195, Level 16, State 1, Procedure
sp_MSfetchAdjustidentityrange, Line 92
A valid identity range is not available. Check the data type of the identity
column.
Thanks in advance...
Chris,
this is a bit more complicated than it seems...
I have had cause to manually change the identity range on a subscriber - I'm
not recommending it but it did lead to a better understanding of the
mechanism involved!
If you are using automatic range management this'll be taken care of when
you synchronize (run the merge agent). However, if it is not possible for
you to connect to the publisher, you could manually update
MSrepl_identity_range on the subscriber. This table is used to check if the
subscriber has used up its range or reached the threshold. The new range you
set would be obtained from MSrepl_identity_range on the distributor, which
is the master table and is used to generate new values. The values in this
table (MSrepl_identity_range on the distributor) would need to be changed to
avoid a future potential conflict. Finally, the check constraints on the
subscriber would need updating accordingly.
As an aside, note that there are some anomalies with automatic range
management: the first range is twice the requested size and the actual range
of values enforced by the check constraint is always one less than the size
selected - SQL Server 2005 managed identities for merge replication has been
redesigned to be more consistent.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul. Thanks
I ended up removing replication from the DB and reinstating. I'm now having
a problem with creating the publication from a generated script! See later
post.
Thanks anyway for you help.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%239GGiaZrFHA.2996@.tk2msftngp13.phx.gbl...
> Chris,
> this is a bit more complicated than it seems...
> I have had cause to manually change the identity range on a subscriber -
> I'm not recommending it but it did lead to a better understanding of the
> mechanism involved!
> If you are using automatic range management this'll be taken care of when
> you synchronize (run the merge agent). However, if it is not possible for
> you to connect to the publisher, you could manually update
> MSrepl_identity_range on the subscriber. This table is used to check if
> the subscriber has used up its range or reached the threshold. The new
> range you set would be obtained from MSrepl_identity_range on the
> distributor, which is the master table and is used to generate new values.
> The values in this table (MSrepl_identity_range on the distributor) would
> need to be changed to avoid a future potential conflict. Finally, the
> check constraints on the subscriber would need updating accordingly.
> As an aside, note that there are some anomalies with automatic range
> management: the first range is twice the requested size and the actual
> range of values enforced by the check constraint is always one less than
> the size selected - SQL Server 2005 managed identities for merge
> replication has been redesigned to be more consistent.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Daleyou have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Dale
you have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>
|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
sql

Merge replication?

We have a SQL Server db in Atlanta. Rows are added to tables in that
database by a website in Atlanta. We have a number of people who use
that data in Dallas and they need to be able to modify the data. This
scheme works okay except when the Internet connection between the two
sites goes down.
So we are looking to replicate the database in Dallas. From the little
I've read, Merge replication would probably be our best bet. However,
I've never worked with replication so I'm unsure. I'd like some
resources either online or books that would help me understand the
process better and help me in implementing such a scheme. Thanks!
-John
Merge might work, bi-directional transactional is another option.
In general if you go offline frequently, your chances for generating
conflicts increase. Merge has conflict tracking mechanisms, where if a
conflict occurs it is logged to conflict tables, and the publisher or
subscriber (your choice) wins. You can roll back this conflict. With
bi-directional transactional replication your distribution agent will fail
and you will have to manually fix the problem. Depending on your data flow
this can involve a single row or thousands.
Bi-directional transactional replication is not resilient to schema changes.
Merge replication is to a degree. Transactional replication is also easier
to troubleshoot. Merge will add a tracking columns to each table you are
replicating. Bi-directional transactional replication does not.
Most dba's would pick merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:n5hus0tfqmr4sgudepget0o5ebhaer8nel@.4ax.com...
> We have a SQL Server db in Atlanta. Rows are added to tables in that
> database by a website in Atlanta. We have a number of people who use
> that data in Dallas and they need to be able to modify the data. This
> scheme works okay except when the Internet connection between the two
> sites goes down.
> So we are looking to replicate the database in Dallas. From the little
> I've read, Merge replication would probably be our best bet. However,
> I've never worked with replication so I'm unsure. I'd like some
> resources either online or books that would help me understand the
> process better and help me in implementing such a scheme. Thanks!
>
> -John
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote:

>Merge might work, bi-directional transactional is another option.
>In general if you go offline frequently, your chances for generating
>conflicts increase. Merge has conflict tracking mechanisms, where if a
>conflict occurs it is logged to conflict tables, and the publisher or
>subscriber (your choice) wins. You can roll back this conflict. With
>bi-directional transactional replication your distribution agent will fail
>and you will have to manually fix the problem. Depending on your data flow
>this can involve a single row or thousands.
>Bi-directional transactional replication is not resilient to schema changes.
>Merge replication is to a degree. Transactional replication is also easier
>to troubleshoot. Merge will add a tracking columns to each table you are
>replicating. Bi-directional transactional replication does not.
>Most dba's would pick merge replication.
We do not go offline frequently, except the past 2 weeks which has
made people scream. I don't think that we will have too many conflicts
because the one location mostly adds records and the other modifies
existing records. Is there a book that you would recommend?
-John
|||I'd probably use bi-directional transactional replication, as long as you
are going to be modifying the schema much.
It is more difficult to set up, and if you are a high volume database you
will have to watch your transaction log growth.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Baima" <john@.nospam.com> wrote in message
news:um90t0de0n4rp3dp2fsu86t988sdhnm3i2@.4ax.com... [vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote:
fail[vbcol=seagreen]
flow[vbcol=seagreen]
changes.[vbcol=seagreen]
easier
> We do not go offline frequently, except the past 2 weeks which has
> made people scream. I don't think that we will have too many conflicts
> because the one location mostly adds records and the other modifies
> existing records. Is there a book that you would recommend?
> -John
>

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Dale
you have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>
|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Merge replication?

We have 2 sites which are normally connected by a 10mb fixed line, I'm
looking for a backup should this link fail. Both sites use the same software
connected to a sql server database. If the link fails both sites should be
able to continue in a limited capacity and once the link is restored the
data should synchronise.
I've been looking at this for a little while and merge replication seem to
be the right solution, however I would like to know if anyone could give me
any other options?
Thanks
Daleyou have three options
1) merge
2) transactional replication with queued updating - good where most of the
updates happen on the subscriber
3) bi-directional replication - good when your tables are naturally
partitioned.
Merge is the best solution as you can resolve conflicts. When a conflict
occured with queued you can only view the conflict, you can resolve it in
Conflict Viewer. Queued also works best when most transactions occur on the
publisher, not the subscriber.
Bi-directional replication does not have any mechanism to handle conflicts,
the jobs fail and you have to manually fix everything - similar to queued
really. Bi-directional replication is also harder to do schema changes on.
Merge and Queued add a GUID column to your underlying tables, bi-di
transactional doesn't.
"Dale Pitman" <d@.dd.com> wrote in message
news:_1Xec.185$zC5.123@.newsfe1-win...
> We have 2 sites which are normally connected by a 10mb fixed line, I'm
> looking for a backup should this link fail. Both sites use the same
software
> connected to a sql server database. If the link fails both sites should be
> able to continue in a limited capacity and once the link is restored the
> data should synchronise.
> I've been looking at this for a little while and merge replication seem to
> be the right solution, however I would like to know if anyone could give
me
> any other options?
> Thanks
> Dale
>
>|||AND any conflicts which occur with bi-di means you have a MAJOR data cleanup
situation to deal with. You have to manually reconcile everything. That is
why bi-di is NOT recommended unless you do one of two things:
1. Enforce that transaction ONLY occur on one side or the other
2. There is a partition which ensures that no changes on one side will ever
conflict with changes on the other side
--
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com|||and how so?
When you get a conflict the distribution agent stops on the side which
detects the conflict. You can then go in and manually fix it. My expireience
is that a single conflict does not mask hundereds of conflicts coming after
it, but rather is a single occurence.
The key is, as both you and I have pointed out, to minimize the chances of
conflicts occuring; but when they do; replication does fail so that you can
fix the conflict and continue.
On the bi-directional systems I have worked on, I have delivered sound
solutions which have been relatively problem free. Bi-directional
transactional is not a good fit for all requirements, but it can work.
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u7C5gTeIEHA.3356@.TK2MSFTNGP11.phx.gbl...
> AND any conflicts which occur with bi-di means you have a MAJOR data
cleanup
> situation to deal with. You have to manually reconcile everything. That
is
> why bi-di is NOT recommended unless you do one of two things:
> 1. Enforce that transaction ONLY occur on one side or the other
> 2. There is a partition which ensures that no changes on one side will
ever
> conflict with changes on the other side
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>|||Therein lies the problem. While replication may halt, it may not also. If
you have duplicate primary keys, it will halt. If you update a nonexistent
row, update one side and delete other side, it will halt. It will NOT halt
if you update the same piece of data on both sides to different values. The
last one in will win and you will have zero indication that a previously
saved value has now been overridden which can create severe issues with the
business.
Also, replication halts in two cases. This is a very undesirable thing to
do. You can't exactly halt the transaction flow from applications, so
additional transaction begin piling up on both sides because replication is
halted. This not only dramatically increases latency, but it also has an
increasing potential to generate even more conflicts which can put you in
the same situtation with teh agents halting causing a further backup in
transactions, etc. until you hit the point where someone deems replication
to be completely nonfunctional and starts pointing fingers at the
replication engine. It has nothing to do with the replication engine, but
entirely due to the way the engine is being used and the design being
implemented.
The 2 conditions I outlined are the only way I would EVER implement a bi-di
system. I've been doing this since Sybase Rep Server 1.0 against Sybase
System 10. I've watched more than one bi-di system, 2 of them my own
designs, become complete toast because those 2 conditions were violated.
You get burned enough times, you learn not to keep sticking you hand in the
fire.
If you want to deal with conflicts being thrown in a bi-di system, that is
you choice. I certainly wouldn't want to be the one sitting in that seat,
because I know all too well just what kind of havoc a single conflict can
cause in that type of implementation. I also prefer to not have to babysit
a replication system 24 hours a day because I've placed a design that has a
flaw which can only be overcome with manual intervention and a decision by
an IT person who isn't necessarily qualified to make a call on the data.
--
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Merge replication: View Conflicts interface errors

Hi,

Not sure if this is the correct place to post this but I'm doing merge replication between SQL Server 2005 SP1 and SQL Server Mobile 2005 (on mobile devices) and I'm having some problems with data conflict resolution in the "View Conflicts" interface on the server management studio. The "Select Conflict Table" comes up and there are 5 tables listed. When I click on any of these tables I get an error dialog box titled "Microsoft Replication Conflict Viewer" and message "[ColumnName] is neither a DataColumn nor a DataRelation for table summary (System.Data)" with details:

===================================

CSRTechnicianID is neither a DataColumn nor a DataRelation for table summary. (System.Data)

Program Location:

at System.Data.DataRowView.get_Item(String property)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetWinnerSQL(DataRow loserRow, Boolean blockFetch)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.GetSourceRow()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetailData()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.FillDetail()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.summaryInfoGrid_SelectionChanged(Object sender, SelectionChangedEventArgs args)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.OnSelectionChanged(BlockOfCellsCollection selectedCells)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.SelectedCellsInternal(BlockOfCellsCollection col, Boolean bSet)
at Microsoft.SqlServer.Management.UI.Grid.GridControl.set_SelectedCells(BlockOfCellsCollection value)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.ResetSummaryGrid()
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.LoadConflict()

It then opens the conflict viewer window, and lists a conflict in the top grid, but nothing in the lower grid (usually shows details of the winner and loser records). In the top grid it shows conflict type as "4(Update/Delete, Update wins)" and the conflict loser is the mobile device subscriber. If I click on anything (Submit Winner, Remove) I get an error message "Column [Column Name] does not belong to table summary (System.Data)" with details:

===================================

Column 'CSRTechnicianID' does not belong to table summary. (System.Data)

Program Location:

at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.get_Item(String columnName)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.SetLogInfo(DataRow logRow, String sql)
at Microsoft.SqlServer.Management.UI.ConflictViewer.ViewerForm.btnSubmitWinner_Click(Object sender, EventArgs eg)

It seems that the conflict resolution function has gotten corrupted. Does anyone have any tips on how to fix this?

Regards,

Greg

Hi Greg,

This is a known issue which is now fixed in SQL Server 2000 SP2. Please try it and let us know if the problem persists.

The problem is that your table already had a roguid column which was named differently than what the conflict viewer was expecting.

sql

Merge Replication: transaction log growing too fast

The merge replication running continuously between two servers (sql server
7.0) on a 100MB database with 300 tables. The transaction log size grows to
more than 100MB every hour, even though there are no data changes. Is this
normal?
BTW, I am using 'PUSH to Subscriber' and both Publisher and Subscriber's
transaction log has same issue.
|||Madan,
something very fishy here - can you run profiler to see what is happening
behind the scenes.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||--from transaction log using log explorer--
Table Insert Delete
Modify
dbo.MSmerge_genhistory 10600 10600 0
dbo.sysmergearticles 0 0
2120
Even thought there were no data changes, MSmerge_genhistory and
sysmergearticles tables were busy doing something.
I also turned off the 'auto update stats' and 'auto create stats', and
results still the same.
|||Paul/Hillary any idea? Please help.

Merge replication: subscriber jobs missing.

I didn't actually see this but my customer swears:
Merge replication subscriber sql 2000 sp3a had the jobs disappear from the
agent for about 10 hours and the publisher said it could not connect. This
morning the jobs are back and replication was able to be started manually.
Help.
There was a set of conditions which would cause the replication agents to
disappear. Basically if you restored a published database, and then create a
publication with the same name as the old one, everything would disappear.
By chance, where are the agents running? On the subscriber or publisher?
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
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:770297FF-ADE0-46FA-A235-93D0007C62BC@.microsoft.com...
>I didn't actually see this but my customer swears:
> Merge replication subscriber sql 2000 sp3a had the jobs disappear from the
> agent for about 10 hours and the publisher said it could not connect.
> This
> morning the jobs are back and replication was able to be started manually.
> Help.
|||They run on the publisher. When you say everything would disappear, did it
ever come back?
"Hilary Cotter" wrote:

> There was a set of conditions which would cause the replication agents to
> disappear. Basically if you restored a published database, and then create a
> publication with the same name as the old one, everything would disappear.
> By chance, where are the agents running? On the subscriber or publisher?
> --
> 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
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:770297FF-ADE0-46FA-A235-93D0007C62BC@.microsoft.com...
>
>
|||Not until I removed some publications.
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
"SQLdba" <burt_king@.yahoo.com> wrote in message
news:E20EC89D-A27E-4C0A-9B2F-4311220DFDDF@.microsoft.com...[vbcol=seagreen]
> They run on the publisher. When you say everything would disappear, did
> it
> ever come back?
>
> "Hilary Cotter" wrote:
|||Two things. First, it's transactional replication, not merge. Sorry for
that mistake.
Second, I was able to see the jobs when I looked at the server and was able
to restart the replication manually.
"Hilary Cotter" wrote:

> Not until I removed some publications.
> --
> 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
>
> "SQLdba" <burt_king@.yahoo.com> wrote in message
> news:E20EC89D-A27E-4C0A-9B2F-4311220DFDDF@.microsoft.com...
>
>

Merge Replication: sp_MSgetmetadatabatch Duration

I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
pull subscribers.
A few of my users are having problems with replication. This is all
done via VPN connections to our site (so they're not on our LAN.)
The merge agent fails with the error "The process could not query row
data at the 'Publisher'."
After running Profiler, it appears that the this is the longest
duration of anything run.
I can't find any real details on this stored procedure, so I'm not
real sure what its doing. I'm also NOT a replication expert. I know
enough to have gotten it running and its been working okay, but these
problems have slowly been creeping up. We can normally get the
synchronization to go through, but its frustrating to my users, and I
may be facing a mutiny! Help! I'll be glad to provide any more details
that anyone requests, and if anyone has any details on what this
stored procedure is doing or how I can find out, that would be most
appreciated!
Try setting querytimeout to a large value or using the slowlink
profile.
On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
|||On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> Try setting querytimeout to a large value or using the slowlink
> profile.
> On Nov 15, 12:26 pm, dday...@.gmail.com wrote:
>
>
>
>
> - Show quoted text -
I've done both of those (Set QueryTimeout = 6000) and it still occurs.
|||Index fragmentation could be cuase, you should be rebuilding your merge
system table indexes on a regular basis:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"dday515@.gmail.com" wrote:

> I'm running SQL 2000 SP4 publisher with approximately 15 MSDE 2000
> pull subscribers.
> A few of my users are having problems with replication. This is all
> done via VPN connections to our site (so they're not on our LAN.)
> The merge agent fails with the error "The process could not query row
> data at the 'Publisher'."
> After running Profiler, it appears that the this is the longest
> duration of anything run.
> I can't find any real details on this stored procedure, so I'm not
> real sure what its doing. I'm also NOT a replication expert. I know
> enough to have gotten it running and its been working okay, but these
> problems have slowly been creeping up. We can normally get the
> synchronization to go through, but its frustrating to my users, and I
> may be facing a mutiny! Help! I'll be glad to provide any more details
> that anyone requests, and if anyone has any details on what this
> stored procedure is doing or how I can find out, that would be most
> appreciated!
>
|||On Nov 15, 1:48 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> Index fragmentation could be cuase, you should be rebuilding your merge
> system table indexes on a regular basis:
> DBCC DBREINDEX (MSmerge_contents, '', 80)
> DBCC DBREINDEX (MSmerge_genhistory, '', 80)
> DBCC DBREINDEX (MSmerge_tombstone, '', 80)
> DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
> DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
> ChrisB MCDBA
> MSSQLConsulting.com
>
> "dday...@.gmail.com" wrote:
>
>
> - Show quoted text -
I've done a full reindex as well prior, still the same problem!
|||Did you try the slow link profile?
Is it possible also that your network link is going down during your sync?
Can you run a ping -t between the publisher and subscriber to verify that
the link stays up during the sync?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
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
"Daniel Day" <dday515@.gmail.com> wrote in message
news:3d27eaf9-9105-47e0-96bc-c6afe7763da0@.v4g2000hsf.googlegroups.com...
> On Nov 15, 1:06 pm, Hilary Cotter <hilary.cot...@.gmail.com> wrote:
> I've done both of those (Set QueryTimeout = 6000) and it still occurs.

Merge replication: problems upgrading SQL-2000 to 2005

Hello!

We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.

A few questions to experts, familiar with Merge Replication in SQL-2005:

1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.

2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @.encrypted_password parameter anymore, and subscription creation process failed.

3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?

Thanks!

1. For merge replication, the version of the distributor has to be greater than or equal to the publisher, and the publisher has to be greater than or equal to the subscriber. So no, a SQL 2000 publisher and SQL 2005 subscriber will not work.

2. This is a very vague question, it's best if you post specific questions and we can answer them. But yes there are changes to some of the tables and stored procedures, but for the most part your SQL 2000 scripts should work in SQL 2005 with a few caveats (such as the sp_addmergesubscription_agent parameter change).

3. The best thing you can do is set up a test environment and try things out, and make any changes necessary. Once your new environment is set up correctly, you can generate new scripts to be used.

|||

I have tested creating publication on sql 2000 with RMO interface. (publisher and subscriber are sql 2000 or 2005 . of course, publisher has to be the same or higher version than the subscriber)

it doesn't work and some of the classes only work for 2005. By using scripts to do that job, I found some of the system sps on 2005 and 2000 are different. for instance,

sp_startpublication_snapshot doesn't exist in sql 2000 server.

I believe scripting are the only choice.

I browsed msdn for this topic. it 's interesting that microsoft only talks about what RMO can do and doesn't mention what it can't do.

there are few topics discussing hybrid replication snario. if you found anything useful, please reply.

ta.

Merge Replication: Missing rows at Subscriber with no conflicts

Hi all,

We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).

Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.

We are using Merge Replication, with no filters and published every 2 minutes.

Any ideas?

TIA,

Michael

I have seen this before with SQL 2000 replication. It not uncommon especially for very high volumn data transfer. Unfortunately I cannot tell you what the issue is or why it happens.

Although I would advise that a 2 minute sync interval is probibly not useful for you at all. You should run the merge agent continuiously with the -continuious flag if you want it to sync this often.

I would change to continuious, reconcile the data then hopefully it doesnt happen again. If you can you would be better moving to an all SQL 2005 scenario.

Martin

sql

Merge Replication: Insert Trigger ONLY when replicating

Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
Nest level check allows to avoid ping-pong traffic from subscriber to
publisher and vice versa
But in your case that should matter only if you are also replicating table
tblTest
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
Hello,
I have a scenario where clients enter data into a MSDE local database on
their laptop. I would like to add a trigger on that table that fires only
on the Server (SQL2K Ent.) when they are synchronizing when a new records
has been inserted. By looking at the Merge trigger automaticcaly generated
on the table, I found something interesting:
"if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1"
Therfore, I've created my trigger like the following:
Create Trigger tg_Inserted On tblBlaBla FOR INSERT
AS
if not sessionproperty('replication_agent') = 1
return
Insert Into tblTest (Account_Code, Product_Code, DateCreation)
Select ins.Account_code, ins.Product_Code, GetDate()
From Inserted As Ins
I've done some test and everything works fine. However, before putting this
in Production, I was wondering if I absolutely need to put the "Select
trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
saying you need to, some are saying you don't need to... Since it is a very
well documented feature, can anyone confirm me the proper way of doing this?
That would be greatly appreciated. Thanks.
|||Great. I'm not replicating this table. Thanks for the information.
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:eVz8BD8WFHA.1796@.TK2MSFTNGP15.phx.gbl...
> Nest level check allows to avoid ping-pong traffic from subscriber to
> publisher and vice versa
> But in your case that should matter only if you are also replicating table
> tblTest
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Christian Hamel" <chamel@.NOSPAM.com> wrote in message
> news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>
|||I think it is to prevent recursive triggers from causing duplicate entries
in msmerge_contents. I could be wrong 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
"Christian Hamel" <chamel@.NOSPAM.com> wrote in message
news:udebeQ7WFHA.2692@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a scenario where clients enter data into a MSDE local database
on
> their laptop. I would like to add a trigger on that table that fires only
> on the Server (SQL2K Ent.) when they are synchronizing when a new records
> has been inserted. By looking at the Merge trigger automaticcaly
generated
> on the table, I found something interesting:
> "if sessionproperty('replication_agent') = 1 and (select
> trigger_nestlevel()) = 1"
> Therfore, I've created my trigger like the following:
> Create Trigger tg_Inserted On tblBlaBla FOR INSERT
> AS
> if not sessionproperty('replication_agent') = 1
> return
> Insert Into tblTest (Account_Code, Product_Code, DateCreation)
> Select ins.Account_code, ins.Product_Code, GetDate()
> From Inserted As Ins
> I've done some test and everything works fine. However, before putting
this
> in Production, I was wondering if I absolutely need to put the "Select
> trigger_nestlevel() ..." or not. I've read in the newsgroups and some are
> saying you need to, some are saying you don't need to... Since it is a
very
> well documented feature, can anyone confirm me the proper way of doing
this?
> That would be greatly appreciated. Thanks.
>

Merge Replication: Import Data

Hi,
I have MERGE setup between SERVER A (Publisher)-- SERVER B (Subscriber).
in a table on SERVER A i am importing data from an excel sheet say 100
records. But these records are not reflecting on SERVER B even after merge
agent runs several time.
What would be be possible cause?
Thanks in advance.
Regards
Javed
When you use DTS by default triggers are not fired; in the options tab of
Transform Data Task Properties uncheck the Use Fast Load option to have your
triggers fired. You need to issue a sp_addtabletocontents 'tableName' to
replicate its contents.
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
"Javed Iqbal" <javediqbal98@.hotmail.com> wrote in message
news:eyvd3r5CGHA.984@.tk2msftngp13.phx.gbl...
> Hi,
> I have MERGE setup between SERVER A (Publisher)-- SERVER B (Subscriber).
> in a table on SERVER A i am importing data from an excel sheet say 100
> records. But these records are not reflecting on SERVER B even after merge
> agent runs several time.
> What would be be possible cause?
> Thanks in advance.
> Regards
> Javed
>

Merge Replication: Dropping an article from existing Publication

Hi All,
How to drop a single article (TABLE) from merge replication keeping
publication intact.
Regards
Javed Iqbal
Javed,
unlike transactional, this is not possible. The nearest you can get is to
drop the publication hten recreate it without the article and do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the help.
Actually I want to change a column width in a replicated table which is
appearing at first position.
I can add dummy column then update dummy column with data. Then drop the
main column. Recreate main column with new size and update it. And finally
removing the dummy column using sp_repladdcolumn and sp_repldropcolumn.
But it will add the column at last position.
Is there any way to redefine column position on a replicated table?
OR
How to add a column at specific position?
Thanks in advance.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ucTtJkYTGHA.4132@.TK2MSFTNGP11.phx.gbl...
> Javed,
> unlike transactional, this is not possible. The nearest you can get is to
> drop the publication hten recreate it without the article and do a nosync
> initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
ideally the application is not dependant on the column position. We have no
TSQL to specify it, and the only practical way is to recreate the table. In
terms of merge, this would be a reinitialization. If at all possible, make
your application use column names rather than position numbers. As an
interim measure can you perhaps define a view?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul for your prompt help.
Ideally my TSQL should refer column names but in my applications I am not
sure about what programmers have coded in TSQL. And it is not possible to
check those programs .
There are many reports based on this table hence I do not want to play with
the column position.
Correct me if I wrong at following.
Scenario - Merge Replication with NOSYNC.
I have only option to drop subscription / publication and recreate it with
the earlier generated scripts.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23x2oERZTGHA.1160@.TK2MSFTNGP09.phx.gbl...
> Javed,
> ideally the application is not dependant on the column position. We have
no
> TSQL to specify it, and the only practical way is to recreate the table.
In
> terms of merge, this would be a reinitialization. If at all possible, make
> your application use column names rather than position numbers. As an
> interim measure can you perhaps define a view?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Javed,
a nosync initialization would be ok. You could change the column position on
the publisher and subscriber before synchronising. In this case you'd take
over control of the identity range management. You could only use the
previous scripts if they were themselves defined as nosync.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes Paul,
I am using NOSYNC since MERGE Replication is created / started.
Identities are ODD on Publisher EVEN on subscriber.
Any further inputs are highly appreciated.
Thanks a lot again Paul.
Regards
Javed Iqbal
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ecN9afZTGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Javed,
> a nosync initialization would be ok. You could change the column position
on
> the publisher and subscriber before synchronising. In this case you'd take
> over control of the identity range management. You could only use the
> previous scripts if they were themselves defined as nosync.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>