locked
Manually handling deletes with Sync __sysOCSDeletedRows and __sysRK RRS feed

  • Question

  • First post, be nice :)

    I'd like to be able to identify the records that have been deleted from my SDF file. I've found a few references to the __sysOCSDeletedRows table and had a look into it. Looks great as a tombstone table, so I thought, let's check out the __sysRK. A varbinary(8000) that holds a byte[24] array that I assumed was the primary key of the record to be deleted.

    I use Guid's as my ID's so I also assumed that this was a byte[] representation of my id, but when I tried to create a new Guid() from it, told me that it only needed 16 bytes not 24.

    So I searched for any references to __sysRK and basically came up with a very minor explanation in a blog which didn't help me that much.

    I started playing around and found out that the first 8 bytes are some type of identifier (I'm still not sure what it is which is why I'm posting this), and the additional 16 turned out to be my Guid.

    I simply retrieved the last 16 bytes and created a new Guid() and was able to get my ID.

    Can someone shine some light on the __sysRK column, plus am I totally fluking it and retrieving my ID in a manner that is haphazard?

    Thanks,
    Bradley :)
    • Moved by Mahesh DudgikarMicrosoft employee Tuesday, November 24, 2009 7:13 AM (From:SyncFx - Technical Discussion)
    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:33 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, October 28, 2009 7:19 AM

Answers

  • Bradley-

    The __sysRK is the packed Primary Key column for the table.  Since all tables use the same __sysOCSDeletedRows table, SQL Compact uses a packed representation of the PK to allow PKs of all shapes and sizes to fit in one table.  The packed PK byte array has the following format:

    Number of PK Columns (as Int32)
    The size of the first PK Column in bytes (as Int32)
    The first PK Column
    The size of the second PK Column
    The second PK Column
    ...

    So for your case, you have one PK column of type GUID.  So:

    Number of PK Columns = 1
    The size of the first PK Column = 16
    The first PK column = GUID of 16 bytes

    4 + 4 + 16 = 24 which is why the entry is a 24 byte array.

    Note that this representation is internal to SQL Compact and using it is unsupported and it is subject to change.

    Thanks,
    Phil
    Tuesday, November 24, 2009 6:40 PM

All replies

  • Bradley-

    The __sysRK is the packed Primary Key column for the table.  Since all tables use the same __sysOCSDeletedRows table, SQL Compact uses a packed representation of the PK to allow PKs of all shapes and sizes to fit in one table.  The packed PK byte array has the following format:

    Number of PK Columns (as Int32)
    The size of the first PK Column in bytes (as Int32)
    The first PK Column
    The size of the second PK Column
    The second PK Column
    ...

    So for your case, you have one PK column of type GUID.  So:

    Number of PK Columns = 1
    The size of the first PK Column = 16
    The first PK column = GUID of 16 bytes

    4 + 4 + 16 = 24 which is why the entry is a 24 byte array.

    Note that this representation is internal to SQL Compact and using it is unsupported and it is subject to change.

    Thanks,
    Phil
    Tuesday, November 24, 2009 6:40 PM
  • BTW, what is the reason you need to identify the deleted rows and maybe if we can understand your need, we could find a better way to achieve what you are trying to do.
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, November 24, 2009 8:43 PM