locked
Sqlcesyncprovider change tracking RRS feed

  • Question

  • Hello,
    I'm trying to understand how sql compact update tracking data for every insert,update,delete , I've used the manual change tracking in old ctp version so I know what data to update and making additional queries to update tracking tables for the moment.
    Is this the right way to do that ? I red the documentation and says "....sqlcesyncprovider is aware of stored procedures..." but sql compact doesn't support stored procs or triggers that are created when provisioning for what I know.
    Any help appreciated
    • Moved by Hengzhe Li Friday, April 22, 2011 2:25 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Saturday, July 18, 2009 12:09 AM

Answers

  • SQL CE has it's own way of tracking changes and the tracking table is not always populated for a given item.  If you look on your base table after calling Apply, you will see columns added called "__sysChangeTxBsn" and "__sysInsertTxBsn" that are used to track changes.  The only time a record in present in the tracking table is when a change is made through sync.  The reason for this is that more tracking metadata is needed for rows received through P2P synchronization.  Are you seeing issues with synchronization?

    Could you point me to the documentation that you are quoting? 

    Thanks-
    Wednesday, July 22, 2009 5:42 PM
  • About the "numeric" problem, this is a known issue and will be fixed in RTM.  Also, I noticed that your products_id column is an identity column which are currently not supported in CTP2 but will be in RTM.  Sorry for the inconvenience.
    Tuesday, July 28, 2009 5:02 PM

All replies

  • In general the SQL CE engine has its "own" way to track changes.  May you explain your business scenario why you need to browse or update the internal tracking table or tracking data on SQL CE?

    Thanks.
    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 21, 2009 5:19 AM
    Answerer
  • I need to synchronize sqlce peers over network. The problem is the tracking tables are not modified, I've tried with these simple lines of code
    Dim scopeDesc As New DbSyncScopeDescription("products")
    scopeDesc.Tables.Add(SqlCeSyncDescriptionBuilder.GetDescriptionForTable("products", m_oCn))
    
    Dim serverConfig As New SqlCeSyncScopeProvisioning()
    
    serverConfig.PopulateFromScopeDescription(scopeDesc)
    serverConfig.Apply(m_oCn)
                    
    After that If I made a change like adding a row in the table I should be able to see the tracking tables modified but this doesn't happen. How can the sqlceprovider create a syncknowledge ???
    What do you mean by ".........has its "own" way to track changes..." ???

    Thanks
    Tuesday, July 21, 2009 11:09 AM
  • So you would like to do a PEER-to-PEER sync between 2 SQL CE providers/repicas, right?  If so can reveal what version of Sync Framework you have installed?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 21, 2009 2:13 PM
    Answerer
  • 2.0 CTP2 what else.....
    Tuesday, July 21, 2009 4:09 PM
  • SQL CE has it's own way of tracking changes and the tracking table is not always populated for a given item.  If you look on your base table after calling Apply, you will see columns added called "__sysChangeTxBsn" and "__sysInsertTxBsn" that are used to track changes.  The only time a record in present in the tracking table is when a change is made through sync.  The reason for this is that more tracking metadata is needed for rows received through P2P synchronization.  Are you seeing issues with synchronization?

    Could you point me to the documentation that you are quoting? 

    Thanks-
    Wednesday, July 22, 2009 5:42 PM
  • Hi,

    Even the local insert didn't show on your tracking table, did the sync work for you? The tracking table is used for recording the sync changes from other stores. SQL CE has other ways to track the local changes. The SyncKnowledge is stored in the scope_info table. If you want to know more, you may want to enable System.Diagnostics tracking logs for your app.

    Thanks,
    Dong
    This posting is provided AS IS with no warranties, and confers no rights.
    Wednesday, July 22, 2009 5:57 PM
    Moderator
  • Thanks for the answer
    I've seen the columns added through code, they are not visible in server explorer dataconnection.
    So sqlce uses coupled tracking.....that is reasonable without triggers

    The documentation quoted is:

    http://msdn.microsoft.com/en-us/library/dd918848%28SQL.105%29.aspx

    "......Both SqlSyncProvider and SqlCeSyncProvider are aware of the stored procedures and change-tracking tables created during provisioning...."

    Actually I'm getting some errors in the synchronization........scope not found, but I've to made more tests maybe I made some mistake, I'll let you know If I'm able to sync two sql ce over network

    Thanks everyone
    Wednesday, July 22, 2009 7:16 PM
  • Hi,
    I'm getting errors during synchronization and I'm doing nothing, I'm just provisioning the db with one scope and one table as I wrote on a previous post and then call synchronize without making any changes and working local no wcf. The message is "....while initializing adapters for scope....". This is the SqlCeSyncProviderScopeConfiguration object in the db, is this causing problems ?

    <SqlCeSyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Table Name="products" GlobalName="products" TrackingTable="products_tracking">
    <Col name="products_id" type="int" pk="true" />
    <Col name="products_model" type="nvarchar" size="22" />
    <Col name="products_ean" type="nvarchar" size="32" />
    <Col name="products_price" type="money" />
    </Table>
    </SqlCeSyncProviderScopeConfiguration>


    Wednesday, July 22, 2009 11:13 PM
  • Could you include the full exception with the inner exception as well?

    Thanks-
    Thursday, July 23, 2009 3:59 PM
  • I thought it was some problem in my app that is quite big, so I made a simple app for testing, sorry it's not very good written.
    http://rapidshare.com/files/259231622/SqlCePeer.rar.html

    The first problem was with a numeric type column, once I removed it I'm getting error with transactions.
    Thanks
    Thursday, July 23, 2009 7:55 PM
  • I took a look and found the problem, it looks like your column names are not the same between sides, as one side has "products_d" and one has "products_id".  This will cause problems unless you use column mapping to map these columns to a common name.

    The error you are seeing is a bug due to not closing DataReaders in this error path.  I will file a bug.

    Thanks,
    Phil
    Monday, July 27, 2009 8:47 PM
  • sorry that was a type error, but the changes are not applied however, the syncstatistics are correct shows changes downloaded and uploaded but the datasource is not updated. Did it work for you ??
    Also I'm getting an error adding a numeric(6,2) column this is the stack trace, you can also recreate the problem just by adding a column of that type.

    ��
    at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
       at Microsoft.Synchronization.Data.SyncUtil.GetSqlDbTypeFromString(String typeString)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncAdapter.GetParameterForColumn(DbSyncColumnDescription col)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncAdapter.PrepareInsertCommand()
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncAdapter.BuildCommands(SqlCeSyncProviderAdapterConfiguration tableInfo)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncAdapter..ctor(SqlCeSyncProviderAdapterConfiguration tableConfig)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncUtil.InitializeCeAdapters(SqlCeConnection connection, String objectPrefix, String scopeName)
    
    Messagge:
    "Requested value 'numeric' was not found."

    The sqlce numeric should cast to sqldbtype.decimal, is there a way to get around the problem ?
    In SqlCeSyncDescriptionBuilder.GetDescriptionForTable the datatype is numeric, while on the schema of the ado datatable is decimal.....

    For the moment I've changed manually the column type in the sqlcesyncproviderscopeconfiguration object stored in the db, as provisioning is done only one time.

    Thanks

    Monday, July 27, 2009 10:17 PM
  • About the "numeric" problem, this is a known issue and will be fixed in RTM.  Also, I noticed that your products_id column is an identity column which are currently not supported in CTP2 but will be in RTM.  Sorry for the inconvenience.
    Tuesday, July 28, 2009 5:02 PM
  • thanks for the answer I will wait for the final release instead of getting mad
    Tuesday, July 28, 2009 5:53 PM