Member Avatar for KirstyHunter

Hi,

I have replication configured on a SQL 2008 Database. The tables involved are something like this;

tbl.Product
ProductID Int
ProductName NVarchar(max)
ImageID Int

FK_Product_Image FOREIGN KEY(ImageID) REFERENCES tbl.Image (ImageID)

tbl.Image
ImageID Int
Image Image

tbl.Product is part of a Transactional publication with updatable subscriptions.
tbl.Image is part of a Merge publication

I have no problem creating/modifying products with an image on the publisher, doing it on the subscriber will replicate the Image but won't replicate the product on Modify/create.

The stored procedure that does the create/modification of the Product will insert the Image into tbl.Image then create/update the product with the ID returned from inserting the image.

Can anyone tell me why the transactional bit doesn't work when its done on the subscriber?
Replication monitor doesnt give me any errors or warning and there are no errors in any event log's.

Any help appreciated
Thanks
Kirst

  • 1 Contributor
  • 1 Reply
  • 178 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by KirstyHunter
Member Avatar for KirstyHunter

Turns out it was the Foreign Key constraint causing the problem. Replication appears to be doing the transactional replication before the merge but because the transactional row had a FK to the merge it violated the FK.

I set my FK to NOT 'enforce for replication' and it worked fine.

Hi,

I have replication configured on a SQL 2008 Database. The tables involved are something like this;

tbl.Product
ProductID Int
ProductName NVarchar(max)
ImageID Int

FK_Product_Image FOREIGN KEY(ImageID) REFERENCES tbl.Image (ImageID)

tbl.Image
ImageID Int
Image Image

tbl.Product is part of a Transactional publication with updatable subscriptions.
tbl.Image is part of a Merge publication

I have no problem creating/modifying products with an image on the publisher, doing it on the subscriber will replicate the Image but won't replicate the product on Modify/create.

The stored procedure that does the create/modification of the Product will insert the Image into tbl.Image then create/update the product with the ID returned from inserting the image.

Can anyone tell me why the transactional bit doesn't work when its done on the subscriber?
Replication monitor doesnt give me any errors or warning and there are no errors in any event log's.

Any help appreciated
Thanks
Kirst

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.