locked
Active Change Tracking / Removing a identity column RRS feed

  • Question

  •  

    I put this in the sync forum as the problem is caused by the way the Microsoft Sync creates the schema on SQLCE.

     

    Ultimate problem I am trying to solve : Removing the identity property from a column on a SQLCE table while leaving the server schema completly unchanged.

     

    Reason : The app requires all rows to have a unique identifier (we are already using GUID, uniqueness is not the problem).  I manually remove the identity column from the SQL scripts used for syncing to ensure all uploaded rows do not include the identity column, and once the sync completes with the download the SQLCE table now contains a new identity number matching the server and conflict free from other devices adding rows on the same table.  Bravo!  Problem is I dont want the identity property on the column of the SQLCE database at all.  It is causing headaches with our app (and customers) to have this number changing whenever it syncs.

     

    Preferred solution : Remove identity column.  I have tried the following SQL scripts.  This will be a once-off before the database is deployed to the device during install.  The follow-up issue of how to use sync's 'CreateSchema' functionality to address this 'identity column' issue can be resolved in another ticket, but in all honesty I do not see this requirement happening.

     

    --Add another column with the same data type as the IDENTITY column.
    ALTER TABLE TaskManagement_TaskInfo
      ADD new_TaskNo int NULL

    --Update the new column with the values of the IDENTITY column.
    UPDATE TaskManagement_TaskInfo
      SET new_TaskNo = TaskNo

    --Drop the IDENTITY column.
    ALTER TABLE TaskManagement_TaskInfo
      DROP COLUMN TaskNo

    --Rename the new column to the dropped IDENTITY column’s name.
    EXEC sp_rename 'TaskManagement_TaskInfo.new_TaskNo', 'TaskNo', 'COLUMN'

     

    DROP COLUMN causes a nice error asking me to disable change tracking (I can imagine renaming the column will also cause a error, but babysteps please).  Other posts on msdn seem useless regarding change tracking :

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

     

    So... Am I asking the right question :

    "How to remove change tracking on a SQLCE database?"

     

    If this cannot be answered for some reason then my question is :

     

    "How do I remove an identity property from a column on SQLCE database after Microsoft Sync has created the schema?"

    • Moved by Hengzhe Li Friday, April 22, 2011 8:04 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, September 29, 2008 6:16 AM

All replies

  • Change tracking cannot be disabled from outside the runtime. Also Change tracking is required for the system to track and synchronize changes. Do you expect sync to work beyond once you disable change tracking?
    Thursday, October 2, 2008 2:35 AM
    Moderator
  • I believe the correct change should be in the sync tool to provide two tick boxes on how to handle the identity property column :

     * Strip identity property from the column on SQLCE side.

     * Remove identity column so it will not be sync'ed.

     

    This would greatly assist the programmer as the current behaviour fails business logic :

    -Two devices are deployed with a database, identity column starts at 1,000.

    -1st Device adds one row, identity number 1,000 is added to the local database and synced.

    -2nd Device adds one row.  Number 1,000 is added.  Device syncs and encounters a conflict as that identity number already exists.

     

    The steps above dont include if the application has a desktop app (which we do) that also adds rows into this table, it just creates a mess for everyone and no-one can use the identity number for what it is ment to be used, a unique identifier for the row.

     

    If it is not possible to disable/enable change tracking can you please change the SQLCE error message so people dont get led down the garden path like I did.

     

    ----------ideal sequence of events--------

    1. Build the SQLCE database using the normal Sync/Visual Studio tools.

    2. Alter the SQL scripts to not send the identity column during sync & add IDENTITY COLUMN OFF.  (preferrable done by the sync tool, next ticket)

    3. Disable tracking using some magic SQL command.

    4. Remove the identity property on a column.

    5. Enable tracking

    6. Deploy database to mobile app through a installer.

     

    ------------result------------

    Rows inserted onto this table by the mobile device will have 'zero' as the number, the user will see 'zero' as the tracking number and hence not synced yet (performed by a background app).

     

    When the device sync's it will upload the new rows on the table.  As the identity column is not uploaded and the identity property exists on the server, a unique number will be assigned to this row on the server.

     

    During the 2nd phase of the sync it identify's this row has changed and sucks the new identity number back to the device.  The user can then see on the device that the number is no longer zero (it has sync'ed) and a unique tracking number has been assigned that can be used to call head office / communicate with other people using mobile devices.

     

    This method is currently working, however I alter the table on a local version of SQL Server and make some tricky changes to Sync's SQL scripts to make it all work, and its just so complicated and error prone to do manually.  The steps required to do all of this is just painful and confusing for others to follow.

     

    --------------SQLCE error that should be changed------------------

    SQL Execution Error.

     

    Executed SQL statement: ALTER TABLE TaskManagement_TaskInfo
      DROP COLUMN TaskNo

     

    Error Source: SQL Server Compact ADO.NET Data Provider

    Error Message: Data Definition Language (DDL) operations are not allowed on this table, or on any system table or tracked user table.  Disable tracking before you run DDL operations. [Table name = TaskManagement_TaskInfo ]

    Sunday, October 5, 2008 10:59 PM
  • Hi Omad,

     

    I have the same problem, but the database setup is a little differnet. I have a Central SQL Database Held On SQL 2008 Server. This database contains Millions of Records.

     

    I also have SQL CE 3.5 database helded on HandHeld, Laptops etc of which Sync with the Central Server. But I don't want Millions of records send out to each HandHeld etc everytime the HandHelds need to Sync, as the database is over 4Gb.

     

    So I created UserName columns and set up the Sync SelectImcermenalInsert and Update commands to enclude a WHERE cause. E.g. WHERE ([UserName] = 'John') This works great as only the data for the required user is sync, i.e. John Handheld gets all John Rows (Customers), Mary Laptop get alls Mary rows etc etc. To get this to work I did the following:

     

    1: On First Install Sync DownloadOnly using the WHERE Cause and DropExistingOrCreateNew option of Sync Agent

    2: On all next Sync...

    3: UploadOnly update, deleted and insert rows. With Insert I removed the ID Column as you did

    4: Next I DROP ALL TABLES in the Client Database

    5: DownloadOnly Again the Enitre Table using TruncateOrCreateNew Option Of Sync Agent

     

    Everything works great until you go to Insert a new record, then you get the Error "Error Message: A duplicate value cannot be inserted into a unique index" The reason the Error occurs, from Best Trial And Error, is due the Index been all over the place. For Example

     

    ID    Customer   Data

    1      John          Info

    13    Mary          Info

    224   Ken           Info

     

    I hope I'm explaining this right.

     

    What I done was to create a new ID and Insert this ID depending on the Last ID Integer Value of the last row of the database table.

     

    1: Mycount = SELECT COUNT * TableName

    2: Next Dim MyIndex as interger = Me.DataSet.TableName.Rows(Mycount).item("ID")

    3: MyIndex = MyIndex + 1

    4: INSERT INTO TableName [ID], [etc...., VALUES(Myindex, @... )

     

    This works perfect.

     

    I hope the above was of some help to your Issue. If you would like a code example let me know.

     

    Kind Regards

     

    John

    Monday, October 13, 2008 2:15 PM
  • Omad,

     

    This is an issue that would be best addressed using SyncSchema and an example of leveraging SyncSchema to modify your client side- schema before it is created can be found here:

     

    http://msdn.microsoft.com/en-us/library/bb726037.aspx

     

    Why are you reluctant to leverage this technique?  Another option would be to just remove your identity column from the change enumeration commands use to get change from the server.  This approach results in the client side schema containing this additional column but because the enumeration command on the server does not retrieve this particular value, it will be null on the client. 

     

    Sean Kelley

    Program Manager

    Microsoft

     

    Monday, October 13, 2008 5:59 PM
    Moderator
  • Hi Sean,

     

    Thankyou for your reply.  I have tried using the _CreatingSchema event to remove the identity property from the column, however it does not do exactly what I want.  Keep in mind I wish to keep the column, just remove the identity property from the mobile side.

     

    When a database structure change needs to be rippled down to the mobile device (as seems to happen hours before a project is to be delivered) I need to run the sync tool (in VS2008) to re-generate the SQLCE table structure.  Removing the identity property from the column on the SQLCE side is not the complete story.  The best way to describe the problem is to first give you a list of steps I must follow every single time a database structure change occurs:

     

    • Copy database from server to local workstation SQL
    • Remove identity property from [TaskInfo].[TaskNo] from local workstation SQL
    • Delete SQLCE's .SDF database file from VS project
    • Open VS2008 and run tool to create SLQCE database, adding in any new tables if req.
    • Do a global search on the SQL scripts that insert [TaskInfo] (2 spots GBACache.Server.syn & GBACache.sync to follow the example) and add "SET IDENTITY_INSERT TaskManagement.TaskInfo OFF" to the insert string.
    • Remove the column [TaskNo] from the same insert strings, as well as the passing paramater strings.
    • Deploy .SDF database to the install.

    This is the only way I have found to remove the identity property from the SQLCE database, and during the sync this column contains the correct number as if it was added directly to the server database. The sync downloads the "change" and the SQLCE database on the PDA now contains the same number as the server, customers love this feature ;-)

     

    Altering the _CreateSchema event to modify this property does not alter the SQL scripts modified by this tool, also using this method means I have to create the database using a emulator/PDA which is just painful on my large database, especially when its crunch time.

     

    Unless my logic of updating the database structure on the SQLCE side to match the server is incorrect, then I still believe the correct place for this fix is in the sync tool that generates the SQLCE database.  Please refer to my previous post as to why I think the business logic is wrong with how the sync tool currently handles all identity columns.

    Tuesday, October 14, 2008 5:52 AM