locked
Anchor with type DateTime RRS feed

  • Question

  • Hi

    I want to sync a table with appox. 2000 rows to a mobile device. From the mobile device's point of view, this wil be DownloadOnly because I'm not going to make any changes on the device itself. I tried to do this with snapshot but it took several minutes to send all the 2000 rows over the network, and I'm planning on doing this every day.

    I took a look at this: http://msdn.microsoft.com/en-us/library/cc305973%28v=sql.100%29.aspx and concluded I needed everything from row 2 in this table. Since I can't change the actual table on the server, I'm going to have to use the 'DatumCreatie' (CreationDate) and the 'DatumWijziging' (LastChangeDate) columns to look up the incremental changes (type DateTime).

    This is what I do on the webservice (server side):


            m_serverProviderPlu = New DbServerSyncProvider()
            Dim builder As New SqlConnectionStringBuilder()

            builder.DataSource = "dbdev"
            builder.IntegratedSecurity = False
            builder.InitialCatalog = "dbMEATvvpstock"

            builder.UserID = "testjoachim"
            builder.Password = "BLANK"
            Dim serverConnection As New SqlConnection(builder.ConnectionString)
            m_serverProviderPlu.Connection = serverConnection

            Dim pluSyncAdapterBuilder As New SqlSyncAdapterBuilder
            pluSyncAdapterBuilder.Connection = serverConnection

            'tabel zelf
            pluSyncAdapterBuilder.TableName = "tblPlu"
            pluSyncAdapterBuilder.DataColumns.Add("PluID")
            pluSyncAdapterBuilder.DataColumns.Add("PluNaam")
            pluSyncAdapterBuilder.DataColumns.Add("Prijs")
            pluSyncAdapterBuilder.DataColumns.Add("Actief")
            pluSyncAdapterBuilder.DataColumns.Add("DatumCreatie")
            pluSyncAdapterBuilder.DataColumns.Add("DatumWijziging")
            'rest voorlopig niet

            'afwerking
            pluSyncAdapterBuilder.CreationTrackingColumn = "DatumCreatie"
            pluSyncAdapterBuilder.UpdateTrackingColumn = "DatumWijziging"


            ' select new anchor command
            Dim anchorCmd As New SqlCommand()
            anchorCmd.CommandType = CommandType.Text
            anchorCmd.CommandText = "SELECT @sync_new_received_anchor = GETUTCDATE()"
            anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.DateTime).Direction = ParameterDirection.Output

            m_serverProviderPlu.SelectNewAnchorCommand = anchorCmd

            Dim ad = pluSyncAdapterBuilder.ToSyncAdapter
            DirectCast(ad.SelectIncrementalInsertsCommand.Parameters("@sync_last_received_anchor"), SqlParameter).DbType = DbType.DateTime


            m_serverProviderPlu.SyncAdapters.Add(ad)

    On the device (client):

                m_syncAgentPLU = New SyncAgent()
                m_servicePLU = New PluSyncWebService.Service
                m_syncAgentPLU.RemoteProvider = New ServerSyncProviderProxy(m_servicePLU)


                Dim fileLoc = "\Application\db\dbPlu.sdf"                        
                Dim connString As String = "Data Source=" + fileLoc + "; Default Lock Timeout = 10000"

                If Not File.Exists(fileLoc) Then
                    Dim clientEngine As New SqlCeEngine(connString)
                    clientEngine.CreateDatabase()
                    clientEngine.Dispose()
                End If

                Dim clientSyncProvider As New SqlCeClientSyncProvider(connString)
                m_syncAgentPLU.LocalProvider = clientSyncProvider

                Dim tblPlu As New SyncTable("tblPlu")
                tblPlu.CreationOption = TableCreationOption.DropExistingOrCreateNewTable 
                tblPlu.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.DownloadOnly


                Dim scanningGroup As New SyncGroup("Scannings")
                tblPlu.SyncGroup = scanningGroup
                'tblBarCodeScanningOpmerking.SyncGroup = scanningGroup

                m_syncAgentPLU.Configuration.SyncTables.Add(tblPlu)

     

    This is the code generated for 'select incremental inserts' command:

    SELECT [PluID], [PluNaam], [Prijs], [Actief], [DatumCreatie], [DatumWijziging], [GebruikerCreatie], [GebruikerWijziging] FROM [tblPlu] WHERE (DatumCreatie > @sync_last_received_anchor AND DatumCreatie <= @sync_new_received_anchor)

     

    Now I'm having the following problem:

    When I run the application and sync for the first time, nothing happens. I decided to run SQL Profiler and have a look at the query's being executed. Then I ran the sync for a second time. The @sync_last_received_anchor property's value was some 10 minutes ago, and the @sync_new_received_anchor was the current datetime.

    Am I correct that the @sync_last_received_anchor property's value should be the last DateTime I downloaded data (in that case, the anchors in the above example were correct)? If so, could anyone explain me why this doesn't work yet (the properties seem OK)? I've looked hours for examples on this but didn't find anything similar.

    Thanks in advance

    Joachim







    • Edited by joowa Thursday, June 16, 2011 1:12 PM
    Thursday, June 16, 2011 12:23 PM

All replies

  • is it not synching incremental updates or is it always downloading everything?

    you table creation option is set to DropExistingOrCreateNewTable, so you are dropping the table everytime you sync?

    Thursday, June 16, 2011 12:50 PM
  • The table creation option was indeed probably also wrong (and I corrected this at the moment), but I just noticed that I made a mistake in my problem description. I told that all the data is downloaded the first time, but that's not correct (the data was just still there from my 'snapshot' attempt, and apparently the table didn't got dropped).

    So the problem is there never happens anything now. I've tested again and edited my problem description.

     

    Thanks for the answer, Joachim


    Thursday, June 16, 2011 1:06 PM
  • if the client has prexisting data, the download will raise conflicts as the download would insert the same rows that are already existing in the client.
    Thursday, June 16, 2011 1:16 PM
  • That was also my thought. That's why I restarted with an empty table, but still nothing happened.

     

    I extracted the incremental inserts query from SQL Profiler and executed it myself (with the same values for the arguments passed). This returned a row I've made between my last sync attempt and now. So I don't think there's anything wrong with the query.

    Thursday, June 16, 2011 1:24 PM
  • subscribe to the applychangesfailed event on the client and check if its raising an error or conflict.
    Thursday, June 16, 2011 1:28 PM
  • I did this both serverside and clientside and the event didn't raise.

    I also noticed that now, the values in @sync_last_received_anchor and @sync_new_received_anchor are 2 hours behind the actual time. I tried to sync again at 2011-06-16 16:30 and the value of @sync_new_received_anchor was 2011-06-16 14:30. I checked my system time on the device and on my computer and they were both correct.

    Thursday, June 16, 2011 2:37 PM
  • isnt your sync_new_received_anchor date using UTCDate?

    try converting your 'DatumCreatie' (CreationDate) and the 'DatumWijziging' (LastChangeDate) columns to UTC  in your where clause.

    Thursday, June 16, 2011 2:55 PM
  • I changed   anchorCmd.CommandText = "SELECT @sync_new_received_anchor = GETUTCDATE()"  to getdate() and everything seems to work fine now! 

    edit: I read your answer later but that was the problem indeed. Maybe it's indeed better to do it the opposite way and convert the columns in the where clause.

     

    Thanks for the answers!

     

    I may have another little question on the TableCreationOption. There doesn't seem to be an option like 'UseExistingTableOrCreateTable'. If I want to achieve something like this, is the only posibillity to manually check if the table already exists? I don't want to create all the tables beforehand on all the devices.

    Thanks in advance, Joachim

    Thursday, June 16, 2011 3:03 PM
  • yes, just do manual checking if the table exists.


    Thursday, June 16, 2011 3:18 PM
  • Thanks,

    I got this to work with the 'DownloadOnly' SyncDirection, and now I need it for UploadOnly (sending data from the scanner to the server). I thought this was going to be similar, but my sync_last_received_anchor keeps being wrong (so this delete didn't work):

    exec sp_executesql N'DELETE FROM [tblBarCodeScanning] WHERE ([BarCodeScanningID] = @BarCodeScanningID) AND (@sync_force_write = 1 OR (Update_Timestamp <= @sync_last_received_anchor)) SET @sync_row_count = @@rowcount',N'@BarCodeScanningID uniqueidentifier,@sync_force_write bit,@sync_last_received_anchor datetime,@sync_row_count int output',@BarCodeScanningID='A233E7CF-2C01-4BFE-9935-53228D415695',@sync_force_write=0,@sync_last_received_anchor='1753-01-01 12:00:00',@sync_row_count=@p6 output
    select @p6

    (UpdateTimestamp is of type DateTime)


    This is my client side code (I'm feeling I need to do some more configuration here because here, I never specify to use datetime. server side. I've got the select new anchor command set like I did in the DownloadOnly example).

            Dim basicBinding As New BasicHttpBinding
            Dim endPoint As New EndpointAddress("http://192.168.8.101:1234/ScanSyncService")
            Dim proxy As New ScanSyncServiceClient(basicBinding, endPoint)

            Dim test = proxy.HelloWorld

            Dim syncProxy = New ServerSyncProviderProxy(proxy)


            m_syncAgentScannings = New SyncAgent()
            m_syncAgentScannings.RemoteProvider = syncProxy

            Dim fileLoc = "\Application\db\dbSyncTest.sdf"

            Dim connString As String = "Data Source=" + fileLoc + "; Default Lock Timeout = 10000"

            'If Not File.Exists(fileLoc) Then
            '    Dim clientEngine As New SqlCeEngine(connString)
            '    clientEngine.CreateDatabase()
            '    clientEngine.Dispose()
            'End If

            Dim clientSyncProvider As New SqlCeClientSyncProvider(connString)

            m_syncAgentScannings.LocalProvider = clientSyncProvider
            '
            Dim tblBarCodeScanning As New SyncTable("tblBarCodeScanning")
            tblBarCodeScanning.CreationOption = TableCreationOption.UseExistingTableOrFail
            tblBarCodeScanning.SyncDirection = Data.SyncDirection.UploadOnly

     

            Dim scanningGroup As New SyncGroup("Scannings")
            tblBarCodeScanning.SyncGroup = scanningGroup

            m_syncAgentScannings.Configuration.SyncTables.Add(tblBarCodeScanning)

     

     

    I wouldn't ask this if I hadn't searched on this myself for several hours. Is there something I could do to make the sync_last_received_anchor correct?

    Thanks in advance, Joachim

     

    Monday, June 27, 2011 9:38 AM
  • Apparently I don't need to specify the updatetrackingcolumn, creationtrackingcolumn, to use UploadOnly (I suddenly remembered this handy table I've come across: http://msdn.microsoft.com/en-us/library/cc305973%28v=sql.100%29.aspx).

    In that way, the generated query's don't check the sync_last_received_anchor. I'm still not sure why the anchor just wasn't correct, although I don't need it right now.

    Monday, June 27, 2011 9:59 AM