Merge replication foreign key problem

Hi there. I'm somewhat new to merge replication, and I've been having an issue with one of the scenarios that I've been trying to get working. I am using SQL 2005 on the server, with 2005 express on the client. I have 2 tables:

Photo - which has a PhotoID primary key

PhotoData - which has a PhotoDataID primary key, and a PhotoID foreign key

both primary keys are int, and set to identity. I only want the Photo table to replicate for the merge, because I want the data in the PhotoData table to only be called by demand through a web service (since the images in that table are too large to be included in the normal replication). However, when a client adds a photo to his local database (which adds a record in the photo table, and then it's actual image data in the photodata table), I can't sync the photo table any longer. The error I get is:

Could not drop object 'dbo.Photo' because it is referenced by a FOREIGN KEY constraint

I have the foreign key relationship marked as "not for replication", but that doesn't seem to help. Is there another way I should be doing this? Thanks for any help!

-PHil

Does the client have the Photo and the PhotoData tables with some data in them before it is initilaized?

If so and you dont want to lose them, then you can select the article's property of @.pre_creation_cmd='none'. By default this is set to 'drop ' and at first synchronization, the tables are tried to drop. Now when there is data and the Photo table is tried to drop, it will conflict with the FK.

|||

Hi, Philip!

Maybe, a good solution would be using data filters in merge replication? You can define data filters to filter rows of published data according on condition, dependent on current SQL server. In this case you must include both tables into publication, but data from 2nd table will not be exchanged.

Another solution - to use vertical filtering, by columns. You can exclude column, containing actual image data, from 2nd table in publication.

Both methods are well described in SQL Server Books Online (Replication -> Replication Options -> Filtering Published Data)

David.

|||

p.s. Note, that horizontal filtering (row-based) is more complex technology, and requires careful testing before using in real working system!

Good luck!

|||

Wouldn't vertical filtering not work out when I need to add the photo data into the client database after the replication (via a web service, not replication)? I need that column to exist on the client's PhotoData table, but I just don't want it's data to be replicated. My understanding of vertical filtering is that it completely omits the column entirely, but if there is a way around that, then that woudl be useful.

Otherwise, I'm not sure I follow what you mean with the horizontal filtering. Do a filter where I sync all PhotoData rows with a PhotoDataID < 0 (i.e., no rows at all), and then just add them via the web service afterwards?

Thanks for your help!

|||

Yes, the client will have data inside it before initialization, and it'll be a pull subscription. Sorry for a basic question, but where do I change this setting you're talking about?

|||The property is for sp_addmergearticle or you can set it in UI too.|||

You are right, vertical filtering will remove the column from table at subscriber.

Try to use horizontal filtering as you specified (with simple condition to synchronize no rows).

I think, this will work.

|||

I just tried your suggestions, and got the following error when I tried to synchronize:

Message
2006-12-26 11:47:07.177 {call sp_MSsetconflicttable (N'Photo', N'MSmerge_conflict_EvidenceToolPublication_Photo', N'DEVSERVER', N'EvidenceToolDatabase', N'EvidenceToolPublication')}
2006-12-26 11:47:07.364 Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSsetconflicttable (N'Photo', N'MSmerge_conflict_EvidenceToolPublication_Photo', N'DEVSERVER', N'EvidenceToolDatabase', N'EvidenceToolPublication')}
2006-12-26 11:47:07.442 Category:SQLSERVER
Source: JOHNSON-9400
Number: 102
Message: Incorrect syntax near 'PhotoID'.

Where Devserver is the server's name, EvidenceToolPublication is the name of the publication, and EvidenceToolDatabase is the name of the database.

|||I've just tried it this way, and it doesn't work. It still gives me a foreign key issue, since the data isn't being replicated.|||This seems like some issue when creating the conflict procs/tables. Would you be able to get a trimmed down version of the repro script and post it here?