Asked by:
SyncFX 2.1 : problem with Metadata thinking that records are deleted !?

Question
-
We have a scope that syncronize some tables between two countries
in the 'Master' end, there is a Navision ERP system that we extract data from into the the synchronized tables.
The extraction method uses the SQL MERGE operator that automatically merge data into a table and deletes when not found.
The problem we face is that some rows gets deleted and then re-created, using the same Primary Key.
The problem is now that the Sync Metadata thinks that the data is deleted, even after the data has been re-inserted (sync_row_is_tombstone = 1)
Why is this happening ?... shouldn't the MetaData row become active again, once the row is re-inserted ?
edit 1:
I've tested a vanilla setup where i manually delete and re-create using the same key.. in this case the sync_row_is_tombstone gets updated to 0 which is correct... :-/
edit 2:
I've written a hack of a job, that joins the _tracking table where 'sync_row_is_tombstone = 1' with the tables i have troubles with:
- insert data into temptable
- delete from table
- insert into table (effectivly updating metadata)
- delete temptable
I need to do this, to be sure that metadata will push the data to the client database... i know i could simply update the metadata but i'm not 100% confident how the data should look like.
- Edited by Montago Tuesday, July 10, 2012 1:25 PM
Tuesday, July 10, 2012 11:39 AM
All replies
-
are new rows always inserted via SQL Merge? am assuming you have SQL Merge that deletes a row and another SQL Merge re-inserting the same rows, either way, theys should be be firing triggers to update the metadata.
Wednesday, July 11, 2012 1:22 AM -
The whole content of the table is more or less controlled by the merge function but some updates do occur by a program (Linq 2 SQL DataContext )
the layout of on of the tables look like this:
SET ansi_nulls ON
SET quoted_identifier ON
SET ansi_padding ON
CREATE TABLE [dbo].[resources]
(
[dataset] [NVARCHAR](3) NOT NULL,
[lxbenummer] [BIGINT] NOT NULL,
[medarbejder] [VARCHAR](20) NOT NULL,
[navn] [VARCHAR](50) NOT NULL,
[birth date] [DATETIME] NOT NULL,
CONSTRAINT [PK__Resource__EFB4713E4A03EDD9] PRIMARY KEY CLUSTERED (
[dataset] ASC, [medarbejder] ASC )WITH (pad_index = OFF,
statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on,
allow_page_locks = on) ON [PRIMARY]
)
ON [PRIMARY]and the Merge function:
MERGE [resources] TARG
using navi2009views.dbo.resources SRC
ON ( TARG.lxbenummer = src.lxbenummer )
AND TARG.medarbejder = src.medarbejder
AND TARG.navn = src.navn
AND TARG.[birth date] = src.[Birth Date]
WHEN NOT matched BY target THEN
INSERT
VALUES (dataset,
lxbenummer,
medarbejder,
navn,
[birth date])
WHEN NOT matched BY source THEN
DELETE;We did a couple of tests with the Merge function before we used it in production and we found that the metadata would update just as expected...
We've been using LINQ2SQL for years now and lately for about 2 years with the sync framework, without a problem like the one we have now.
It's a real mystery why the metadata suddently thinks that data has been deleted, without that being the case :-/
edit
would / could it be possible that a DELETE is fired and the DELETE_TRIGGER will only fire partially ?... that it succeed in updating the metadata, but fail to actually delete the data ? thereby invalidating the metadata ?
- Edited by Montago Wednesday, July 11, 2012 7:34 AM
Wednesday, July 11, 2012 7:31 AM -
must be an issue with the order in which the trigger is executed.
in your case, a same row having the same PK can actually be deleted and reinserted in the same MERGE statement since your MERGE condition is not just on the PK.
but the order of trigger execution is not guaranteed, so it could be that the last trigger to execute is the delete.
try setting the trigger order so that Delete trigger executes first (see: http://technet.microsoft.com/en-us/library/ms189568.aspx)
- Edited by JuneT Wednesday, July 11, 2012 9:00 AM
Wednesday, July 11, 2012 7:53 AM -
(note i didn't write the Merge script)
The reason why the Merge is selecting on ALL columns, is to prevent doing UPDATE's which would force a full re-sync every time the MERGE is called !!
I will look into ordering the triggers as you suggested and until i do, we'll use the unload-delete-reload-drop method i explained...
thanks
edit:
the link specify that you can order AFTER-triggers, meaning that if you have 2x AFTER-triggers you can order which will fire before the other...
SyncFX uses FOR-triggers which fire simultanious to the event - i'm not sure, but according to MSDN, AFTER-triggers fire after a successful operation but say nothing about FOR-triggers, i suspect that FOR-triggers fire even though an operation is unsuccessful ?
edit 2:
apparently FOR and AFTER might be the same .. hmm...
- Edited by Montago Wednesday, July 11, 2012 11:46 AM
Wednesday, July 11, 2012 8:50 AM -
FOR and AFTER are the same.
there are only two types of triggers, instead of and AFTER (FOR) (http://technet.microsoft.com/en-us/library/ms189799)
Thursday, July 12, 2012 6:33 AM