Merge replication, Identity not for replication, identity range

Hi all,
When setting up a (merge) publication using the wizard, you can click
the three-dotted button in the "specify articles" and then setup the
identity range for publisher and subscribers.
While this is an interesting option, I see that the default is not
checked (yes, I have the identity column setup as not for replication).
Now my database only has about 50 tables but nevertheless, not my
favorit waste of time to call up each table one by one and adjust these
values one by one (tabbing to the correct tab, checking the option,
adjusting the range-values).
So, is there any way to get this checked by default (that would be one
step forward) and preferably also adjust the values while we're at it?
Many thanks in advance,
Ferry
Ferry,
there's no way that I'm aware of. One posibility is to script out the
publication and use a find and replace in notepad to make the alteration
then drop the original and recreate the publication using this script -
admittedly not nice but would save a lot of leg-work.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||From Paul Ibison :
> Ferry,
> there's no way that I'm aware of. One posibility is to script out the
> publication and use a find and replace in notepad to make the alteration then
> drop the original and recreate the publication using this script - admittedly
> not nice but would save a lot of leg-work.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Thanks Paul. I guess I'll have a look and see if I can do either that
or perhaps find some 'backdoor'...
Ferry
|||From Ferry :
> From Paul Ibison :
> Thanks Paul. I guess I'll have a look and see if I can do either that or
> perhaps find some 'backdoor'...
> Ferry
Having said that, I started a more detailed search on Google and came
up with this one. Going to check this later:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*
'************************************************* **
' Purpose :Add Identity range to the user defined Tables under a given
publisher
' Inputs :Publisher Name and Identity Range
' Returns :None
' Author :Mahesh M Kodli
'************************************************* ***
CREATE PROCEDURE AddMergeArticle
@.pPublisherName VARCHAR(255),
@.pIdentityRange BIGINT
As
DECLARE @.tRV INT
DECLARE @.tArticle VARCHAR(255)
SET @.tRV = 0
DECLARE Merge_Article_Cursor CURSOR FOR
--Get all the user defined Tables for which to add Identity Range
SELECT TABLE_NAME TableName FROM INFORMATION_SCHEMA.TABLES WHERE
rtrim(ltrim(table_type))='BASE TABLE'
AND TABLE_NAME Not like 'Conflict%' AND TABLE_NAME Not like
'dtproperties'
AND TABLE_NAME Not like 'sys%' AND TABLE_NAME Not like 'MS%'
OPEN Merge_Article_Cursor
FETCH NEXT FROM Merge_Article_Cursor INTO @.tArticle
WHILE @.@.FETCH_STATUS = 0
BEGIN
--Check if the Table has identity column and accordingly set the Auto
--Identity Range to TRUE or FALSE Before adding Identity Range to the
--article
IF OBJECTPROPERTY ( OBJECT_ID(@.tArticle), 'TableHasIdentity') = 1
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name
= @.tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE
name like @.pPublisherName AND UPPER(publisher)=UPPER(@.@.servername) and
publisher_db=db_name()))
BEGIN
--Use the System stored procedure add merge article to add Identity
range for -- each article
EXECUTE sp_addmergearticle @.publication = @.pPublisherName, @.article =
@.tArticle, @.source_owner = N'dbo', @.source_object = @.tArticle, @.type =
N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause =
null, @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = @.pIdentityRange,
@.identity_range = @.pIdentityRange, @.threshold = 80,
@.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions =
0,@.force_invalidate_snapshot=1
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT * FROM sysmergeextendedarticlesview WHERE name
= @.tArticle AND pubid IN (select pubid FROM sysmergepublications WHERE
name like @.pPublisherName AND UPPER(publisher)=UPPER(@.@.servername) and
publisher_db=db_name()))
BEGIN
EXECUTE sp_addmergearticle @.publication = @.pPublisherName, @.article
= @.tArticle, @.source_owner = N'dbo', @.source_object = @.tArticle, @.type
= N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1, @.article_resolver = null, @.subset_filterclause =
null, @.vertical_partition = N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'False', @.pub_identity_range = NULL,
@.identity_range = NULL, @.threshold = NULL, @.verify_resolver_signature =
0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc =
N'true', @.check_permissions = 0,@.force_invalidate_snapshot=1
END
END
FETCH NEXT FROM Merge_Article_Cursor INTO @.tArticle
END
CLOSE Merge_Article_Cursor
DEALLOCATE Merge_Article_Cursor
IF (@.@.ERROR <> 0)
BEGIN
SELECT @.tRV = -95 --UnSuccessful
GOTO XIT
END
XIT:
RETURN @.tRV
|||Ferry,
thanks for the heads up. I've found the link
http://www.devarticles.com/c/a/SQL-S...2000-Part-2/3/
and will add it onto my site. The only problem with the proc above is that
it assumes you want to replicate all tables, so there's room for another
(simpler) version which takes a tablename as a third parameter.
To have this occur without any intervention such as using Mahesh's script
you would have to edit the stored procedure sp_addmergearticle itself to
hardcode the automatic range management. This is a posibility but which
obviously invalidates support agreements - depends on how confident you are
of getting it spot on
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication trigger count

Hi,
I have Merge Replication ( Articles x,y) with Publications at 4 Sites and a
Central Subscriber. All the Merge Agents are running with property
-exchangetype 2 parameter
Article x have 12 triggers (4 for insert, 4 delete and 4 updates) which
looks OK ( 1 set for each publication) but article y has only 3 triggers(1
insert, i update and 1 delete). I am getting invalid following erros in the
conflict viewer:
1. The row was updated at 'SINDEV21.NewCase' but could not be updated at
'SINDEV20.NewCase'. Invalid object name
'ctsv_CAB6F215FE394ECAB4CE7DD56BD4B1B8'.
2. The row was updated at 'SINDEV21.NewCase' but could not be updated at
'SINDEV20.NewCase'. Unable to synchronize the row because the row was updated
by a different process outside of replication.
You need to reinitialize this subscription and resend your data. It looks
like your replication metadata is out of sync.
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
"Vikas Kohli" <VikasKohli@.discussions.microsoft.com> wrote in message
news:7DC1A948-B930-45A1-ACFF-BB88246FBC16@.microsoft.com...
> Hi,
> I have Merge Replication ( Articles x,y) with Publications at 4 Sites and
a
> Central Subscriber. All the Merge Agents are running with property
> -exchangetype 2 parameter
> Article x have 12 triggers (4 for insert, 4 delete and 4 updates) which
> looks OK ( 1 set for each publication) but article y has only 3 triggers(1
> insert, i update and 1 delete). I am getting invalid following erros in
the
> conflict viewer:
> 1. The row was updated at 'SINDEV21.NewCase' but could not be updated at
> 'SINDEV20.NewCase'. Invalid object name
> 'ctsv_CAB6F215FE394ECAB4CE7DD56BD4B1B8'.
> 2. The row was updated at 'SINDEV21.NewCase' but could not be updated at
> 'SINDEV20.NewCase'. Unable to synchronize the row because the row was
updated
> by a different process outside of replication.
|||When I had first configured this, everything was OK. However to carry a
schema change activity, I had dropped all the subscriptions and Publications,
dropped all the replication procedures, triggers manually wherever required
and reconfigure the Replication again after the schema change. It has started
giving error after then.I have tried removing replication a number of times
but each time same problem.
Also please let me know what should be the normal count of Triggers at the
Central Subscriber, is it 12 or 4?
"Hilary Cotter" wrote:

> You need to reinitialize this subscription and resend your data. It looks
> like your replication metadata is out of sync.
> --
> 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
> "Vikas Kohli" <VikasKohli@.discussions.microsoft.com> wrote in message
> news:7DC1A948-B930-45A1-ACFF-BB88246FBC16@.microsoft.com...
> a
> the
> updated
>
>
|||Just to add more...
I cant reinitialize the Replication since the Data volume is huge and I
can't send that accross WAN. I have to use the property 'do not initialize
Schema or data' when I add subscription.
"Vikas Kohli" wrote:
[vbcol=seagreen]
> When I had first configured this, everything was OK. However to carry a
> schema change activity, I had dropped all the subscriptions and Publications,
> dropped all the replication procedures, triggers manually wherever required
> and reconfigure the Replication again after the schema change. It has started
> giving error after then.I have tried removing replication a number of times
> but each time same problem.
> Also please let me know what should be the normal count of Triggers at the
> Central Subscriber, is it 12 or 4?
> "Hilary Cotter" wrote:
|||Hi,
Any help in this matter will be appreciated as we are encountering lot of
issues due to this
Vikas Kohli
"Vikas Kohli" wrote:
[vbcol=seagreen]
> Just to add more...
> I cant reinitialize the Replication since the Data volume is huge and I
> can't send that accross WAN. I have to use the property 'do not initialize
> Schema or data' when I add subscription.
> "Vikas Kohli" wrote:

Merge Replication to multiple servers losing data

Hi,
We are using merge replication between four servers (1 publisher & 3
subscribers). The same articles (tables) are in each publication. I run the
application which changes data in some tables and adds records in another
table. The inserted data is immediately updated via trigger. After running
the application all expected data is present. If I manually force
replication to each subscriber sequentially, all expected data is present.
If I run replication between the servers at the same time, the table to
which data was added will lose some data. The data lost was not the data
that was just added. We are running SQL Server 2000 SP3a on all servers. Any
ideas?
tia,
Paul
Look at the 'view conflict' at replication monitor...
"PaulW" <MSNewsGroup@.Digi-Sol.com>
news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
> Hi,
> We are using merge replication between four servers (1 publisher & 3
> subscribers). The same articles (tables) are in each publication. I run
> the application which changes data in some tables and adds records in
> another table. The inserted data is immediately updated via trigger.
> After running the application all expected data is present. If I manually
> force replication to each subscriber sequentially, all expected data is
> present. If I run replication between the servers at the same time, the
> table to which data was added will lose some data. The data lost was not
> the data that was just added. We are running SQL Server 2000 SP3a on all
> servers. Any ideas?
> tia,
> Paul
>
|||There are no recorded conflicts. This table only has data inserted, then
updated through a trigger. We did view the transaction log. The only entries
with the table were the inserts we initiated followed by a delete/insert for
the trigger update.
Paul
"Grigoris Tsolakidis" <gcholakidis@.spam_remove.hotmail.com> wrote in message
news:uhRQsHGmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> Look at the 'view conflict' at replication monitor...
> "PaulW" <MSNewsGroup@.Digi-Sol.com>
> news:urv9zNxlHHA.1216@.TK2MSFTNGP03.phx.gbl...
>
sql

Merge Replication Questions [SQL2k5 non express]

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

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

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

Thanks for the answers in advance

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

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

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

|||

Sorry, my initial question was not clear.

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

Let's see the following example:

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

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

|||

Now it is much clearer to me.

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

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

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

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

|||

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

|||

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

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

It was because i forgot to include the:

set QUOTED_IDENTIFIER ON

maybe this is a bug of sql2k5 SP1

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

Thanks again for the solution.

Merge Replication Problem (814916)

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

merge replication picking up tables not in the articles

I am having a strange issue with my merge repl. I have set my verbose level
to 2 which is the highest, and I see an error on a table that is not in my
articles being published but the repl is failing for it. the table exists in
my centralized database but not in my branch's. I can't find any references
to it in my replication.
can anyone tell me where I can look to see why repl is trying to delete from
this table?
TIA.
Can you script out your publication to see if it occurs there?
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
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:1AC0EA95-F1EE-4630-9B99-1E66A7BAF026@.microsoft.com...
>I am having a strange issue with my merge repl. I have set my verbose
>level
> to 2 which is the highest, and I see an error on a table that is not in my
> articles being published but the repl is failing for it. the table exists
> in
> my centralized database but not in my branch's. I can't find any
> references
> to it in my replication.
> can anyone tell me where I can look to see why repl is trying to delete
> from
> this table?
> TIA.
>
|||I did try that and there were no references there either. Thats when I
decided to ask the experts
Anyway, I added the tables being referenced and all is well. You also
answer my other question about an incorrect Parameter, I did as you suggested
but it was still wrong, I removed it since the repl was working fine and my
repl was successful last night.
Thanks again for your help.
"Hilary Cotter" wrote:

> Can you script out your publication to see if it occurs there?
> --
> 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
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:1AC0EA95-F1EE-4630-9B99-1E66A7BAF026@.microsoft.com...
>
>

Merge replication Over Internet

Have a look in the articles section of
www.replicationanswers.com - I have a posted up an
article there on just this topic.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul
Thanks for such a wonderful article, Actually I am facing a problem, you could see it as I have posted it on the group.
Actulally some days before I was implementing merge replication over internet, during that time I didn't even provide the netbios name of the remote MSDE server on the subscriber MSDE server and it worked fine.
but now on a new set of MSDE servers even if I enter all the information as you said I could not even connect to the remote server using osql facility.
Pls Pls Pls help me out I have already spent 3 days on this problem and getting frustated with each passing moment.

Quote:

Originally posted by Paul Ibison
Have a look in the articles section of
www.replicationanswers.com - I have a posted up an
article there on just this topic.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

|||Hi Paul
One more query if you could solve my doubt.
Actually while impletmenting replication over internet, I am planning to use dynamic filtering, Since we don't want to provide FTP information to over subscribers (which are obviously at distributed locations).
So I am asked to make sure that Snapshot is applied manually at the sbuscribers side, Since I am not having very much idea about how to Create Dynamic snapshot and apply it manually, Plz guide me?
Is it that If I apply snapshot manually I'll not need to specify all FTP related stuff??

Quote:

Originally posted by rdhar
Hi Paul
Thanks for such a wonderful article, Actually I am facing a problem, you could see it as I have posted it on the group.
Actulally some days before I was implementing merge replication over internet, during that time I didn't even provide the netbios name of the remote MSDE server on the subscriber MSDE server and it worked fine.
but now on a new set of MSDE servers even if I enter all the information as you said I could not even connect to the remote server using osql facility.
Pls Pls Pls help me out I have already spent 3 days on this problem and getting frustated with each passing moment.

|||For a manual snapshot, you don't need the FTP settings to initialize,
however, any additional articles added to the publication will cause
problems.
I can't setthis up today, but if you're having problems with manual
application of dynamic snapshots, then yo could disable the dynamic snapshot
bit and have the filters applied to the whole snapshot. It ends up the same,
but will take longer.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Can you ping the remote server or probably ping is disabled so can you
telnet it using port 1433? IE is it 'visible' at all through firewalls.
Can you connect using the IP address in OSQL? If so, have you created the
client network alias?
Are you using a VPN? Is it FTP or fileshare initialization? Question for
initialization configuration.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication Initialization-Too many generation batches

I have a SQL 2005-2005 Express Merge replication. Articles with FKs.
Added a new subscriber, when I initialize it, some tables are updated more
190 times. For example, customer table has 100 records, however the
initialization inserts 100 and update 19000 rows. It makes the initialization
too long.
Is this has someting to do the some old snapshot not cleanup or Child and
Parent Generations in Separate Generation Batches?
Any help would be greatly appreciated.
John
Here are some logs:
Enumerating inserts and updates in article 'LogCustomerHeader' (generation
batch 1891)
Downloaded 100 change(s) in 'CustomerList' (100 updates): 253849 total
Enumerating inserts and updates in article 'LogCustomerDetail' (generation
batch 1891)
Downloaded 100 change(s) in 'CustomerList' (100 updates): 253949 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254049 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254149 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254249 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254349 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254449 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254549 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254649 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254749 total
Enumerating deletes in all articles (generation batch 1901)
Enumerating inserts and updates in article 'LogPosition' (generation batch
1901)
Enumerating inserts and updates in article 'LogPositionOff' (generation
batch 1901)
Enumerating inserts and updates in article 'Email' (generation batch 1901)
Enumerating inserts and updates in article 'User' (generation batch 1901)
Enumerating inserts and updates in article 'CustomerGroup' (generation batch
1901)
Enumerating inserts and updates in article 'CustomerList' (generation batch
1901)
Downloaded 15 change(s) in 'User' (15 updates): 2865 total
Downloaded 46 change(s) in 'CustomerGroup' (46 updates): 8786 total
Downloaded 41 change(s) in 'CustomerList' (41 updates): 254790 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254890 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 254990 total
Enumerating inserts and updates in article 'LogCustomerHeader' (generation
batch 1901)
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255090 total
Enumerating inserts and updates in article 'LogCustomerDetail' (generation
batch 1901)
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255190 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255290 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255390 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255490 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255590 total
Downloaded 100 change(s) in 'CustomerList' (100 updates): 255690 total
Hi John,
I understand that when you added a new subscriber to your current SQL
Server 2005-2005 Express merge replication, you found that the
initialization process was too long.
If I have misunderstood, please let me know.
To let me better understand your issue, I would like to know the following
qeustions:
1. How many articles published in your publication database?
2. How much space that the replicated tables have?
3. How many subscribers in your merge replication?
4. How long did the initialize process finish?
5. Could you please mail me (changliw_at_microsoft_dot_com) the replication
logs for further research?
Look forward to your response.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Thanks Charles,
> 1. How many articles published in your publication database?
8
> 2. How much space that the replicated tables have?
~<5 M , totally about 5000 rows

> 3. How many subscribers in your merge replication?
8
> 4. How long did the initialize process finish?
~25 minutees. Sync in LAN
> 5. Could you please mail me (changliw_at_microsoft_dot_com) the replication
> logs for further research?
Will do.
Thanks,
John
"Charles Wang[MSFT]" wrote:

> Hi John,
> I understand that when you added a new subscriber to your current SQL
> Server 2005-2005 Express merge replication, you found that the
> initialization process was too long.
> If I have misunderstood, please let me know.
> To let me better understand your issue, I would like to know the following
> qeustions:
> 1. How many articles published in your publication database?
> 2. How much space that the replicated tables have?
> 3. How many subscribers in your merge replication?
> 4. How long did the initialize process finish?
> 5. Could you please mail me (changliw_at_microsoft_dot_com) the replication
> logs for further research?
> Look forward to your response.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
|||Hi John,
Thanks for your response.
I have checked the logs. The initialization seemed no problem. For why the
initialization causes so many updates, I need to consult the product team
on this issue since the initialization process is undocumented and I could
not assume anything. I will let you know the response as soon as possible
when I get their responses. However the process may need a long time and
sometimes may not get responses.
I appreciate your patience, but if I could not get their response within 2
days. Effectively and immediately I recommend that you contact Microsoft
Customer Support Services (CSS) via telephone so that a dedicated Support
Professional can assist you in a more efficient manner. Please be advised
that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Charles,
I was wondering if this has something to do with the fact that I added some
columns to 2 published tables. This made the replication articles merge by
columns ( sp_mergearticlecolumn)
Thanks,
John
"Charles Wang[MSFT]" wrote:

> Hi John,
> Thanks for your response.
> I have checked the logs. The initialization seemed no problem. For why the
> initialization causes so many updates, I need to consult the product team
> on this issue since the initialization process is undocumented and I could
> not assume anything. I will let you know the response as soon as possible
> when I get their responses. However the process may need a long time and
> sometimes may not get responses.
> I appreciate your patience, but if I could not get their response within 2
> days. Effectively and immediately I recommend that you contact Microsoft
> Customer Support Services (CSS) via telephone so that a dedicated Support
> Professional can assist you in a more efficient manner. Please be advised
> that contacting phone support will be a charged call.
> To obtain the phone numbers for specific technology request please take a
> look at the web site listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> If you are outside the US please see http://support.microsoft.com for
> regional support phone numbers.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== ===
> Get notification to my posts through email? Please refer to:
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ================================================== ====
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ================================================== ====
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
>
>
>
>
>
>
|||Hi John,
Adding columns may cause SQL Server Merge replication reinitialization
which may need a long time. I recommend that you refer to "Adding Columns"
section in this article to see if your steps would cause the
reinitialization:
Schema Changes on Publication Databases
http://technet.microsoft.com/en-us/library/aa237127(sql.80).aspx
Please feel free to let me know if you have any questions or concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Merge Replication changes sequence

I have a question for merge replication.
Scenario: Publication with 3 articles (tables A, B and C) and the sequence
of changes at one subscriber are C (delete row), B (insert row), A (delete
row) and B (insert row).
Questions:
1) Does the merge replication agent keep this sequence when updating the
tables at the Publisher and at the other Subscribers?
2) If not, what's the order the merge agent follows for updating the data if
any?
3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
Thanks,
Ivar
Answers inline.
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
"Ivar" <Ivar@.discussions.microsoft.com> wrote in message
news:A16A74B3-43AA-4F45-9620-E84EC6B350BA@.microsoft.com...
>I have a question for merge replication.
> Scenario: Publication with 3 articles (tables A, B and C) and the sequence
> of changes at one subscriber are C (delete row), B (insert row), A (delete
> row) and B (insert row).
> Questions:
> 1) Does the merge replication agent keep this sequence when updating the
> tables at the Publisher and at the other Subscribers?
It is impossible to predict what sequence they will be applied in.
> 2) If not, what's the order the merge agent follows for updating the data
> if
> any?
Basically deletes are processed first, then it is done according to the
article id.
> 3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
No, they all do apply the DML in a random manner, except deletes are
processed first. In SQL 2005 you can do logic records which means that
parents will be modified before the children.

> Thanks,
> Ivar
|||As well as Hilary's answer, this might help you to understand the merge
article processing order: http://support.microsoft.com/kb/307356
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Hilary and Paul,
Your comments have been a great help for me. On my application I'm using
triggers and due to the order the Merge Agent was sending changes, the
trigger was rolling back them and changes did not propagate.
Now I modified the article order on my publication and everything is working
fine.
Thank you,
Ivar
"Ivar" wrote:

> I have a question for merge replication.
> Scenario: Publication with 3 articles (tables A, B and C) and the sequence
> of changes at one subscriber are C (delete row), B (insert row), A (delete
> row) and B (insert row).
> Questions:
> 1) Does the merge replication agent keep this sequence when updating the
> tables at the Publisher and at the other Subscribers?
> 2) If not, what's the order the merge agent follows for updating the data if
> any?
> 3) Is there a difference if the SQL server version is 2005, 2000 or 7.0?
> Thanks,
> Ivar

Merge replication and Publisher Identity range

Hi,
I have configured a merge replicaiton on sql 2000.
I have also set up auto identity range management on one of my articles.
The publisher identity range is set to 600, and the subscriber identity range is set to 100 and the threshold is %80.My subscribers are mobile users connecting with pocket pc.
I have no problem with the pocket pcs, when they sync sql server automatically adjusts the identity range for them if they have used more than %80. However I have problems with managing identity range on the publisher. When I directly insert into the table on the publisher, if I use more than 80% of my keys, it won't adjust my identity range and it will give an error. I have to manually run the system stored procedure :"sp_adjustpublisheridentityrange" to adjust the range.

Question:
How can I automate this process? I read something about LogReader agent but I don't know how to start it?

Is there any way to handle this problem on the server side? Or do I have to run the stored procedure in my application?BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs Continuously

http://support.microsoft.com/default.aspx?scid=kb;en-us;304706&Product=sql2k