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?

-2

The problem was with delete operations sequence.

Problem solved.

| improve this answer | |

Your Answer

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