Activate change tracking on Sql CE

All Replies

• Monday, September 15, 2008 2:50 AM

  Answerer

   

   
  

  0

   

  From my knowledge, Sql CE database tracking is set when at least synchronization happens.

  Do you have a very strong business reason to set Sql CE database tracking yourself?

   

  Thanks.

   
• Tuesday, September 16, 2008 5:55 PM

  Moderator

   

   
  

  0

  Some more detail. Designer does not have access to configure SqlCE change tracking. It sets up synchronization using the  SqlCeClientSyncProvider.  SqlCeClientSyncProvider class has access to configure change tracking.

   

  thanks

  Sudarshan

   

   
• Wednesday, January 14, 2009 8:08 PM

   

   
  

  0

   

  Right SqlCeClientSyncProvider can access Change Tracking and hopefully not the designer

   

  Will we ever do this by ourselves?

   

  The Business reason is just to redevelop our custom SqlCeClientProvider
   
• Thursday, January 15, 2009 7:43 AM

  Moderator

   

   
  

  0

   

  As Leo and Sudarshan said, the sqlce side is meant to be "sealed" so that the change tracking, the sqlcommands for read/write are built into the provider for sqlce.

   

  if there are some need to enable/disable the change tracking at the sqlce db, implementing the customer provider probably the only way i can think of to achieve this.

   

  I am currious, any reason the change tracking need to be  accessed/controled by your app ?

   

  thanks

  Yunwen

   
• Thursday, January 15, 2009 11:11 PM

   

   
  

  0

  I would like to jump on board here.  My reason for wanting to alter 'Change Tracking' is to execute a SQL statement (In VS2008sp1 Database Viewer) to turn off a identity property on a column.  The error message tells me to disable tracking, so here I am asking how to turn it off.

   

  I just need to temporarily disable it just like the error message states, do my change, then enable it.

   

  For a full run down on my experience (which ended in ideas for enhancements) :

  http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3935536&SiteID=1

   
• Friday, January 16, 2009 11:27 AM

   

   
  

  0

   

  I think the reason is the same as all the possible reasons with Change Tracking on Sql Server 2008
   
• Monday, January 19, 2009 9:41 PM

  Moderator

   

   
  

  0

  I guess we start mixing things up now :-)

   

  want to make it clear: with current design in this release, the change tracking on client side is not accessible from user's applications.

   

  I am trying to get a good reason to make this accessbile. the Identity issues cannot convence me at this moment. ID columns should be suported by using range  (please refer BOL for details ) and should not be dropped at the client side -- this will be introduce some data convergent issues and will be error prone if you got more than one clients.

   

  Agree ?

   

  thanks

  Yunwen

   

   
• Tuesday, January 20, 2009 1:57 AM

   

   
  

  0

  I didn't want to distract from the original post :-)  But will happily respond.

   

  I understand the reasoning behind not having the user access Change Tracking Enabled/Disabled.  I was more picking on the fact the error message I get in Visual Studio's Server Explorer gives me the message to 'Disable Change Tracking' even though I cannot ;-)

   

  Your question was "Agree?".

  My answer is "No, I have a better solution... Please read."

   

  
  My crazy work around works perfectly in my situation, and I would love other people to use it but its complicated to perform.  I managed to keep the identity column turned on for the server, and modify the SQL in Sync to not insert/update the identity column, resulting in all Inserted rows getting a new identity number (oeverweiting the one generated on SQLCE) based on the server, then syncing them back down to the PocketPC so they match...  Bravo.  (Keep in mind the identity column is not my primary key).

   

  Now for the fun part, I enhanced this method further by removing the identity column all together on the SQLCE database so all rows show ZERO when they are inserted on the PocketPC.  After sync this row now contains a valid identity number based on the server.  Now that the row has a number, people using the mobile app can see that this row has been synced!  Bravo x 2.

   

  The very big procedure (and headache) to achieve this is what my thread was all about, and simplifying this process is very important to me as the steps are long and tedious for code monkey's (my colleagues) to follow.  This is why I raised the problem.

   

  Enjoy (If it is not to complicated)

   

   
• Tuesday, January 27, 2009 6:23 AM

  Moderator

   

   
  

  0

  interesting, well it might be working in such way, I am a bit concerned about perf based on your description ("then syncing them back down to the PocketPC so they match... " ).

   

  I have another questions:

   do you need this ID column on your sqlce client db ? if not, you can simply filter the column out ( column filtering )

   

  thanks

  Yunwen

   
• Tuesday, February 03, 2009 1:09 AM

   

   
  

  0
  G'Day,
  
  interesting, well it might be working in such way, I am a bit concerned about perf based on your description ("then syncing them back down to the PocketPC so they match... " ).
  Performace wise it is a small price to pay for this feature!  I have developed a background application for the sync as it takes 15 minutes against our database (6 seconds a table if no changes).  The re-sending of the data back to the mobile only adds a few seconds but well worth it.  As it all works in the background while the main app the user see's is working as normal I am happy.  The battery drain as acceptable for these customers, they know they have something special.
  
   do you need this ID column on your sqlce client db ? if not, you can simply filter the column out ( column filtering )
  Regretfully yes.  The system was originally designed to give the user a Tracking ID number of a task (The identity column).  When we migrated to PocketPC we added a GUID column and made it the primary key, but the system (and users) still wanted the tracking number.  My crazy method was the only was I could keep the Server software working exactly the same, and introduce this functionality to the PocketPC :
   - No sync, no tracking number.
   - Has synced, here is your tracking number.
  
  I hope this helps, sorry for the delay (customer went live last week)
   
• Thursday, August 13, 2009 12:31 PM

   

   
  

  0
  Hi Yunwen,
  
  Can I bump this old thread?  You wrote:
  
  "with current design in this release, the change tracking on client side is not accessible from user's applications...I am trying to get a good reason to make this accessbile."
  
  I have an issue at the moment whereby I need to preload a SQL CE database with the initial data that a new user will need. I don't want to do this with SF because it will be too slow and since this will be a server-side operation it makes sense to just query the data from the server database and insert it into the client SQL CE database.
  
  Problem is, I need to switch on the Change tracking before loading the data, or alternatively, switch it on afterwards and update the anchor values to effectively recognise the existing data is valid. Then, once the database is created, I can download this to the client, after which the initial sync should find no changes necessary.
  
  I hope I've explained this well enough. There must be some kind of DML commands that are being sent to the SQL CE databse from the SF (when calling CreateSchema) that are achieving this.
  
  Anyhow, as developers, surely we should be given the option of controlling the Change Tracking rather than having it hidden from us - I think we can be trusted with the power :) (meant in a light-spirited way)
  
  Kind Regards,
  Paul
  
   
• Friday, August 14, 2009 5:33 AM

  Moderator

   

   
  

  0

  Paul,
  
  A version of SQL Compact with the ability to control change tracking is in the works.  This will be released in beta form with the next beta of Visual Studio 2010.
  
  Regards,
  
  Sean Kelley

  • Marked As Answer by Sean_KelleyModerator Friday, August 14, 2009 5:33 AM
  •  
   
• Friday, August 14, 2009 10:03 AM

   

   
  

  0
  Thanks for the update Sean - it's good to know that this is on the way.
   
• Saturday, August 15, 2009 4:13 PM

   

   
  

  0
  THIS is a great news !

  ---

  Sébastien Pertus. Bewise
   
• Wednesday, December 09, 2009 9:13 PM

   

   
  

  0
  any update on this ?

  ---

  Sébastien Pertus. Bewise
   
• Saturday, December 12, 2009 9:23 AM

   

   
  

  0
  Sebastien - See this: http://erikej.blogspot.com/2009/10/whats-new-in-sql-server-compact-35-sp2.html

  ---

  http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.

  • Marked As Answer by Sebastien PertusMicrosoft Employee Monday, December 14, 2009 6:32 PM
  •  
   
• Monday, December 14, 2009 6:32 PM

   

   
  

  0
  GREAT!

  ---

  Sébastien Pertus. Bewise