none
Sync not detecting all changes on rows RRS feed

  • General discussion

  • Hi,

    I am new to using the Sync Framework, I have a big database that we wrote a sync program for. The initial
    sync seemed to go down very well indeed. We were able to verify everything was correct, as it is a financial application and once done
    with the sync all our reports balanced.

    We then today for the first time performed a end of day routine. Which his a stored procedure that that Z's every item not Z'ed yet.
    It then reports what it has done and life goes on. The problem is that our synch after this mornings Z did not detect the changes that were made to the rows.
    The changes are caused by performing updates on 3 views which represent items in various stages. There are at least 3 changes that happen on every row
    that is processed by the Z. It gets the current ZID which is a field that links back to another table. Its ZedProcessed field also gets flagged from false to true.
    And then a Trigger on the Carrying table updates the ModifiedTime.

    The onsite database is SQL SERVER 2012
    The offsite is SQL Azure
    Using the latest version of the Sync Framework

    Where do I start looking for a solution? Any help on this topic will be greatly appreciated.

    Thanks

    Louis

    Monday, September 17, 2012 2:01 PM

All replies

  • how are the updates being done?

    have you checked for conflicts?

    when it does the update, can you confirm its updating the corresponding tracking table entry?

    Tuesday, September 18, 2012 1:29 AM
    Moderator
  • Hi,

    Thanks for your reply. I just wanted to test again this morning when we run the Z.
    So yesterday I wacked the cloud database, re-synced all the data and then everything was equall.
    This morning we ran the Z and then again ran the sync, the problem is still there and was not a once off issue.

    I dont believe their can be conflicts, the cloud database at this moment in time is not being updated.
    Sync is a one way from on site database to the cloud database.

    The biggest problem is that the main transactions table, the one with items that get flagged during the Z proccess does not get synced and the changes are not seen or tracked.
    They are updated from a stored procedure which runs on the onsite database. As I mentioned before it changes the ZedProcessed bit column from 0 to 1 and then provides the
    ZedID column with a number of the current Z. The stored procedure is wrapped in a transaction so that if anything goes wrong, it can roll back. Which has not happened yet.
    We can however see that during the Z process our trigger on the table is being called and it does apdate the modified datetime stamp on the row. Our trigger is set to after update,
    where the sync trigger is set to for update. Could this be part of the problem?

    Thanks

    Louis

    Tuesday, September 18, 2012 9:30 AM
  • is the sync framework trigger updating the tracking table as well?

    you can enable sync framework tracing or subscribe to ApplyChangesFailed event to see if its raising an error.

    Tuesday, September 18, 2012 10:02 AM
    Moderator
  • Well I believe it is meant to, but it seems that somehow the trigger is not executing.
    With it being SQL server 2012, is it possible to use the built in change tracking like the examples show with SQL 2008?

    In the mean time I will try out what you suggested and get back to you.

    Thanks for the quick reply.

    Louis

    Tuesday, September 18, 2012 10:42 AM
  • Hi again,

    Another quick question. How hardwould it be that for this one table to implement a custom change detector?
    For example all I would want to do is find all the rows where the ModifiedDate column is greater than the last
    sync. This might in theory make life simpler, though there could be some problems with this that I am not aware of.

    Thanks again

    Louis

    Tuesday, September 18, 2012 10:58 AM
  • if your intent is to detect what has changed, the framework does this for you already.

    otherwise, you can always add a filter.

    if you're using the built in database providers (sqlsyncprovider), it uses timestamps.

    Tuesday, September 18, 2012 11:05 AM
    Moderator
  • Hi,

    My intention is simply to get the system up and runnning.
    It is a system that is already in production and scaling to the cloud is our next step.
    A lot of people around the world are waiting for me to get the sync up. It is such a pity that
    the standard way of implementing with the sync framework is falling over in such a way.

    First prize would be just to hit sync and everything happens correctly. I am in the mean time trying to setup a second
    database locally and in the cloud so that we can try see what is happening. As with the Live system, we can only see once a day.

    All changes that happen during the day, and also new items are 100% being sent to the cloud via the sync. Though it did not detect the changes
    from this monrings Z run. So currently our financial reports on the cloud are showing what happened yesterday and what has been added today.
    The items that were processed this morning did not update, so they would still show up on our Pre-List report.

    The rest of our system uses mainly the entity framework to do its work. It is only the Z process that we chose to use native sql.

    Thanks

    Louis

    Tuesday, September 18, 2012 11:21 AM
  • can you not simulate simply updating a row and see if it goes thru? even simply doing a dummy update like "UPDATE Table X SET Col1=Col1 WHERE Id=1".

    The built-in SQL Change Tracking only tells you what has changed. There are other aspects to synchronization like identifying which replica a change has come from, what has been synched already, conflict handling, etc...

    built-in out of the box provider should work with SQL Server 2005 SP2 and above, SQL Azure even SQL 2012 LocalDb.

    I would focus on looking at what your stored proc does. Change tracking  in Sync Framework is a very simple trigger that simply updates the corresponding entry in the tracking table when an update occurs.

    since its based on triggers, any actions (bulk updates, bcp, etc...) that by default don't fire triggers will no update the tracking table, thus no change will be detected.

    updating PK values is not supported as well.

    Tuesday, September 18, 2012 11:51 AM
    Moderator
  • Do you perhaps have an email address? I can then send you the SP and you can see what it does.
    Else I will try do a simple explanation here:

    There are 3 Primary Views. They look at the same data, but in different ways and represent items in different stages in the process. They all have the following columns

    Description varchar (160) -- (On OpportunityDetail Table)
    OpportunityDetailID int -- (Key to OpportunityDetail Table)
    AdvancedDescription varchar (50)  -- (On OpportunityDetail Table)
    Notes varchar (160) -- (On OpportunityDetail Table)
    AccommodationSetID int  --(Key to AccommodationSet Table)
    CompanyID int -- (On OpportunityDetail Table)
    ZedProcessed bit -- (On OpportunityDetail Table)
    ZedID int -- (On OpportunityDetail Table)

    The code in the SP basically does the following

    BEGIN TRANSACTION;
    
    BEGIN TRY
    
    -- Ommited some stuff up here a insert to zeds table 
    -- and then declaring some variables
    
    SELECT @NewZID =  SCOPE_IDENTITY()
    
    UPDATE [dbo].[ItemsInDeposits]
    SET ZedProcessed = 1, ZedID = @NewZID
    WHERE [dbo].[ItemsInDeposits].[CompanyID] = @CompanyID AND [dbo].[ItemsInDeposits].[ZedProcessed] = 0
    
    UPDATE [dbo].[ItemsInHistory]
    SET ZedProcessed = 1, ZedID = @NewZID
    WHERE [dbo].[ItemsInHistory].[CompanyID] = @CompanyID AND [dbo].[ItemsInDeposits].[ZedProcessed] = 0
    
    UPDATE [dbo].[ItemsInInHouse]
    SET ZedProcessed = 1, ZedID = @NewZID
    WHERE [dbo].[ItemsInInHouse].[CompanyID] = @CompanyID AND [dbo].[ItemsInDeposits].[ZedProcessed] = 0
    
    	
    END TRY
    
    BEGIN CATCH
    	IF @@TRANCOUNT > 0
    	ROLLBACK TRANSACTION;
    END CATCH;
    
    	IF @@TRANCOUNT > 0
    	COMMIT TRANSACTION;
    	
    SELECT * FROM @ZTable

    Hope this helps us find the path. Sorru I forgot to add the table that is not syncing correctly is the OppertunityDetail table that the view is connected to.

    Thanks fro the help so far.

    Louis



    • Edited by Louis Lewis Tuesday, September 18, 2012 12:35 PM
    Tuesday, September 18, 2012 12:23 PM
  • am assuming ItemsInDeposits, ItemsInHistory, ItemsInInHouse are Views right?

    is ZedID  the primary key in OpportunityDetail?

    Wednesday, September 19, 2012 2:29 AM
    Moderator
  • Good day,

    You are correct, they are views.

    OpportunityDetailID is the primary key in OpportunityDetail.

    Thanks again for all the help.

    Louis

    Wednesday, September 19, 2012 6:47 AM
  • have you tested updating the OpportunityDetail table to see if the sync framework trigger fires and updates the tracking table?
    Wednesday, September 19, 2012 6:50 AM
    Moderator
  • The Zed is running at the moment. I will log on shortly and check the tables.
    I will let you know as soon as that has been done.
    Wednesday, September 19, 2012 7:02 AM
  • Ok it is done,

    It did see that there were changes.

    ID update_scope_local_id scope_update_peer_key scope_update_peer_timestamp local_update_peer_key local_update_peer_timestamp create_scope_local_id scope_create_peer_key scope_create_peer_timestamp local_create_peer_key local_create_peer_timestamp sync_row_is_tombstone restore_timestamp last_change_datetime
    6196541 NULL NULL NULL 0 0x000000000067180E NULL NULL NULL 0 6722809 0 NULL 2012-09-19 08:36:07.883

    I have looked at the row in the database and it has changed like expected when the Z ran.

    It looks like this:

    ID ModifiedDate CreatedDate TransactionID CompanyID Description ZedProcessed ZedID OppertunityDetailTypeID AdvancedDescription Season Notes AccommodationSetID
    6196541 2012-09-19 08:36:07.820 2012-09-18 10:56:32.970 104029 3 CDREC0 1 1052 4 Cash Deposit Fee Recovery 0 NULL 14463

    I then ran the sync program. After the sync the version on the cloud looks like this

    6196541 2012/09/18 09:44:45 AM 2012/09/18 10:56:32 AM 104029 3 CDREC0 False  4 Cash Deposit Fee Recovery 0  14463

    It has not changed at all.

    Thanks

    Louis

    Wednesday, September 19, 2012 7:27 AM
  • i suggest you enable sync framework tracing or subscribe to the ApplyChangeFailed event to see if there's something wrong going on when it applies the changes.

    i notice the column count  in the cloud is less than the column count of the source table. did you explicitly exclude some columns for synching?

    Wednesday, September 19, 2012 7:41 AM
    Moderator
  • Hi,

    No I just changed the select statement. They are actually the same in everyway.
    Though I have explicitly defined the table in the sync program, as it has a calculated column.

    I have added a handler for the ApplyChangeFailed event. Sending it to the server now.

    Wednesday, September 19, 2012 8:17 AM