1

I have a problem with my transactional replication when I use articles, filters, and delete operations. When I insert the new data into the publisher all the data that meets the specified filter is inserted to the subscriber - everything works fine. When I delete some data from the publisher, not all data that meets the specified filter is deleted from the subscriber and I don't know why.

I have three servers:

  • A – publisher (Microsoft SQL Server 2012)
  • B – distributor (Microsoft SQL Server 2012),
  • C – subscriber (Microsoft SQL Server 2012).

There are six tables in my publisher database. Each table has a primary key and some of them have foreign key as shown below:

Database diagram

I want to replicate only data for the first manufacturer (manufacturer ID=1) from the publisher to the subscriber, so I use these filters:

SELECT <published_columns> FROM [dbo].[Manufacturer]
WHERE [dbo].[Manufacturer].ID IN (Select M.ID from [dbo].[Manufacturer] AS M where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Catalog]
WHERE [dbo].[Catalog].ID IN (Select C.ID from [dbo].[Catalog] AS C
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Cars]
WHERE ID IN (Select CA.ID from [dbo].[Cars] AS CA
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[CarParts]
WHERE ID IN (Select CP.ID from [dbo].[CarParts] AS CP
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[Parts]
WHERE ID IN (Select P.ID from [dbo].[Parts] AS P
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

SELECT <published_columns> FROM [dbo].[PartDetails]
WHERE ID IN (Select PD.ID from [dbo].[PartDetails] AS PD
INNER JOIN [dbo].[Parts] AS P ON P.ID = PD.PartID
INNER JOIN [dbo].[CarParts] AS CP ON CP.PID = P.ID
INNER JOIN [dbo].[Cars] AS CA ON CA.ID = CP.CarID
INNER JOIN [dbo].[Catalog] AS C ON C.ID = CA.CID
INNER JOIN [dbo].[Manufacturer] AS M ON M.ID = C.MID where M.ID = 1)

When I insert the new data into the publisher's CarParts, Parts, or PartDetails tables, all the data that meets the specified filter (manufacturer ID=1) is inserted at the subscriber.

When I delete data from the publisher CarParts, Parts, or PartDetails tables, not all data that meets the specified filter (manufacturer ID=1) is deleted from the subscriber - it only deletes data from the CarParts table, not from Parts or PartDetails.

The same filter is used for insert and delete operations. What could be the problem? Maybe it is a Microsoft SQL Server 2012 bug?

migrated from serverfault.com Apr 25 '16 at 8:03

This question came from our site for system and network administrators.

-2

The problem was with delete operations sequence.

Problem solved.

  • If you want to accept your own answer on this migrated question, you will need to create an account on this site (click the "join this community" link at the top of the page). I'd encourage you to add a bit more detail to your answer as well. Thanks. – Paul White May 5 '16 at 23:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy