FOREIGN KEY problem in merge replication

Hi Paul
Yes of course. My first try was adding articles without @processing_order.
A1 and B1 are related through DRI on the publisher but...
Any idea?

"Paul Ibison" wrote:

> If A1 and B1 are related through DRI on the publisher then you shouldn't need
> to get involved with processing order - it'll determine the order directly
> from the DRI. Have you tries without setting the @processing_order?
> HTH,
> Paul Ibison
>
>
 >> Stay informed about: FOREIGN KEY problem in merge replication 

Note: I have Push Merge Snapshot.
 >> Stay informed about: FOREIGN KEY problem in merge replication 

There's something very wrong here because the DRI scripts are applied after
the schema scripts when initializing. Perhaps it is something to do with the
schema of the table? Is the table created, but under a different schema to
the one used in the FK script. Please can you post up scripts for the 2
tables so I can reproduce. Also I didn't verify - are you adding this article
separately and initializing it on its own, or is this part of a
reinitialization?
HTH,
Paul Ibison
 >> Stay informed about: FOREIGN KEY problem in merge replication 

Follow this steps.
1) Make merge replication with push subscription
2) Make a snapshot
3) Create subscriber database
4) Start synchronization
5) Create table "A" in a publisher. (scripts)

a) "CREATE TABLE [A] ( [AID] int NOT NULL IDENTITY (1, 1))"
b) "ALTER TABLE [A] ADD CONSTRAINT [Pk_AID] PRIMARY KEY ([AID])"

6) Run sp_addmergearticle (scripts)

EXEC sp_addmergearticle
@publication = 'Publ2008',
@article = 'A',
@source_object = 'A',
@identityrangemanagementoption = N'auto',
@pub_identity_range = 100000,
@identity_range = 100000,
@threshold = 80,
@schema_option = 0x0004FF1,
@force_invalidate_snapshot = 1,
@verify_resolver_signature = 0

7) Generate snapshot again
Start synchronization
9) Check if "A" table uploaded to subscriber. if succeed
10) Create table "B" in a publisher. (scripts)
a) "CREATE TABLE [B] ( [BID] int NOT NULL IDENTITY (1, 1))"
b) "ALTER TABLE [B] ADD CONSTRAINT [Pk_BID] PRIMARY KEY ([BID])"

11) Run sp_addmergearticle (scripts)

EXEC sp_addmergearticle
@publication = 'Publ2008',
@article = 'B',
@source_object = 'B',
@identityrangemanagementoption = N'auto',
@pub_identity_range = 100000,
@identity_range = 100000,
@threshold = 80,
@schema_option = 0x0004FF1,
@force_invalidate_snapshot = 1,
@verify_resolver_signature = 0

12) Add column to "A" (scripts)
"sp_repladdcolumn @source_object = '[A]', @column = 'BID' , @typetext =
'INT NULL'"

13) Add FK to "A" table (scripts)
"ALTER TABLE [A] ADD CONSTRAINT [FK_A_B_BID] FOREIGN KEY ([BID]) REFERENCES
[B] ([BID])"

14) Generate snapshot again
15) Start synchronization

Hopefully You must get errors.


"Paul Ibison" wrote:

> There's something very wrong here because the DRI scripts are applied after
> the schema scripts when initializing. Perhaps it is something to do with the
> schema of the table? Is the table created, but under a different schema to
> the one used in the FK script. Please can you post up scripts for the 2
> tables so I can reproduce. Also I didn't verify - are you adding this article
> separately and initializing it on its own, or is this part of a
> reinitialization?
> HTH,
> Paul Ibison
 >> Stay informed about: FOREIGN KEY problem in merge replication 

OK - there's a few extra steps here you didn't mention .
Please have a look at Mike Hotek's article :
http://www.mssqlserver.com/replication/alert_merge_ddl.asp
HTH,
Paul Ibison
 >> Stay informed about: FOREIGN KEY problem in merge replication 

Hi Paul,
Sorry I was on leave and could not reply you.
When I read Mike Hotek's article, I decided to solve my problem by simple
way and need your suggestion. May be it's not correct, but my head already
overheated and can't find other right solution
As I guess the problem is that the Foreigh key creation DDL changes record
inserts in "sysmergeschemachange" table first. I decide to collect
"sysmergeschemachange" table record ID's with schematype=300 after Foreign
key creation, and after snapshot regeneration update schemaversion field
(increase by max value) by my previously collected record ID's.
What about my ugly solution?
I'd like to know your opinion about this solution.

Zaza Turmanidze
 >> Stay informed about: FOREIGN KEY problem in merge replication 

This might work - can't check out the ramifications right now - but the
problem with solutions like this is that they can lead you to an unsupported
setup. Better to sync up after adding the column and then make the DRI
change. Of course if you're already in this predicament this advice doesn't
really help. In that case I'd probably reinitialize, after using DataCompare
to get any changed made at the subscriber (hopefully there's only one).
HTH,
Paul Ibison, www.replicationanswers.com
 >> Stay informed about: FOREIGN KEY problem in merge replication