How to control table sync order

kkchan

Hi,
I hit error when sync one of my table. It is caused by the Table PK used in another table. I am using trigger to control RI instead of foreign constraint.

Due to this, I need to control the table sync order as workaround. How could I do so?

Thank you

kkchan

Hi,
I posted the script generated as below.

Master Table : Locations (LocationID - PK)
Child Table: Terminals (LocationID = FK)

As you can see, Terminal is "sync" before Locations.

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

-- Update 1 row in [dbo].[Terminals]
UPDATE [dbo].[Terminals] SET [Descr]=N'Kuala Lumpur Culture Avenue', [LocationID]=2 WHERE [TerminalID]=1

-- Update 3 rows in [dbo].[Locations]
UPDATE [dbo].[Locations] SET [Descr]=N'BOOKSTORES QUEENSBAY ', [CompanyID]=1 WHERE [LocationID]=5
UPDATE [dbo].[Locations] SET [Descr]=N'Bookstores Midvalley', [CompanyID]=1 WHERE [LocationID]=11
UPDATE [dbo].[Locations] SET [Descr]=N'BAC', [CompanyID]=1, [BusinessHourFrom]='18000101 09:10:00.000', [BusinessHourTo]='18000101 20:00:00.000' WHERE [LocationID]=12

-- Add 5 rows to [dbo].[Locations]
SET IDENTITY_INSERT [dbo].[Locations] ON
INSERT INTO [dbo].[Locations] ([LocationID], [Descr], [LocationTypeID], [CostCentreID], [CompanyID], [BusinessHourFrom], [BusinessHourTo], [DeviationMinute]) VALUES (2, N'KUALA LUMPUR CULTURE AVENUE ', 1, 0, 1, NULL, NULL, 0)
INSERT INTO [dbo].[Locations] ([LocationID], [Descr], [LocationTypeID], [CostCentreID], [CompanyID], [BusinessHourFrom], [BusinessHourTo], [DeviationMinute]) VALUES (3, N'商品/業務銷售部 ', 1, 0, 1, NULL, NULL, 0)
INSERT INTO [dbo].[Locations] ([LocationID], [Descr], [LocationTypeID], [CostCentreID], [CompanyID], [BusinessHourFrom], [BusinessHourTo], [DeviationMinute]) VALUES (4, N'TS WHAREHOUSE ', 1, 0, 1, NULL, NULL, 0)
INSERT INTO [dbo].[Locations] ([LocationID], [Descr], [LocationTypeID], [CostCentreID], [CompanyID], [BusinessHourFrom], [BusinessHourTo], [DeviationMinute]) VALUES (6, N'BOOKSTORES KLANG ', 1, 0, 1, NULL, NULL, 0)
INSERT INTO [dbo].[Locations] ([LocationID], [Descr], [LocationTypeID], [CostCentreID], [CompanyID], [BusinessHourFrom], [BusinessHourTo], [DeviationMinute]) VALUES (7, N'BOOKSTORES THE CURVE ', 1, 0, 1, NULL, NULL, 0)
SET IDENTITY_INSERT [dbo].[Locations] OFF

COMMIT TRANSACTION
GO

-- Reseed identity on [dbo].[Locations]
DBCC CHECKIDENT('[dbo].[Locations]', RESEED, 12)
GO

Please advice. URGENT!

Sylvain Lafontaine

I don't know about the table sync order but there is an option to disable the DML triggers before making the sync.

(In the case of DRI, the Disable foreign keys should be checked. There is also an option for dropping the primary keys, indexes and unique constraints which is often useful, especially when you are not using the Reseed identity columns option.).

kkchan

Hi,
Thank you for reply.
I was thinking to use your approach. However, some of my tables required DML trigger to update another table such as update stock.

Or, create another project for them as workaround? Any other ways?

Thank you

Sylvain Lafontaine

And of course, I suppose that for one reason or another, you cannot include the other table(s) into the synchronisation process.

One possible solution would be to selectively disables the triggers yourself before making/calling the synchronisation process or you could replace these triggers with DRI.

The first solution should be easy to implement by doing a cut&paste or using the command line utility or the SQL-Toolkit. As for the second solution, it's up to you.