locked
Problem Syncing from Sql Ce to Sql Azure RRS feed

  • Question

  • Liam posted a video called "SQL Server to SQL Azure Synchronization using Sync Framework 2.1" and provided some C# code for the demo.  I have changed it to work from "SQL CE to SQL Azure" and also changed to VB instead of C#.  It appears to have provisioned both databases fine, but it fails each time on the sync with the message:

    A first chance exception of type 'System.InvalidOperationException' occurred in Microsoft.Synchronization.dll
    System.InvalidOperationException: Both LocalProvider and RemoteProvider must be set.
    at Microsoft.Synchronization.SyncOrchestrator.Synchronize()
    at ElderSuite.frmMain.Sync() in X:\Programs\ElderSuite\General\Forms\frmMain.vb:line 487

    My code is below, but I don't know what the problem is.  I am using Visual Studio 2008, Sync Framework 2.1, SQL CE 3.5 SP2.  I can see that the "Providers" table has been created in the SQL Azure database and all the tracking information as well.  I don't see any tracking information in my SQL CE database.  Any suggestions?

    Thanks,

    Vince

     

        Private Sub btnSynchronize_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles btnSynchronize.ItemClick
            'Run Setup
            Setup()

            'Run Sync
            Sync()
        End Sub

    Private Sub Setup()
            Try
                Dim SqlAzureConnectionString As String = "Server=tcp:MyServer.database.windows.net;Initial Catalog=MyDatabase;Integrated Security=False;Persist Security Info=True;User ID=MyID;Password=MyPassword;Encrypt=True;"
                Dim SqlCelocalConnectionString As String = "Data Source=|DataDirectory|\Sample.sdf;Password=MyPassword;"
                Dim SqlCeServerConn As SqlCeConnection = New SqlCeConnection(SqlCelocalConnectionString)
                Dim SqlAzureConn As SqlConnection = New SqlConnection(SqlazureConnectionString)
                Dim ScopeName As String = "AllTableSyncGroup"
                Dim myScope As DbSyncScopeDescription = New DbSyncScopeDescription(scopeName)

                'Declare Each Table to Setup for Sync - Remember Dependencies
                Dim Providers As DbSyncTableDescription = SqlCeSyncDescriptionBuilder.GetDescriptionForTable("Providers", SqlCeServerConn)

                'Add Each Table from Above to the Scope - Remember Dependencies
                myScope.Tables.Add(Providers)

                'Setup SQL CE Server for Sync
                Dim SqlCeServerProv As SqlCeSyncScopeProvisioning = New SqlCeSyncScopeProvisioning(SqlCeServerConn, myScope)

                If SqlCeServerProv.ScopeExists(ScopeName) = False Then
                    'Apply the scope provisioning.
                    Debug.Print("Provisioning SQL Server CE for sync " + DateTime.Now)
                    SqlCeServerProv.Apply()
                    Debug.Print("Done Provisioning SQL Server CE for sync " + DateTime.Now)
                Else
                    Debug.Print("SQL Server CE Database server already provisioned for sync " + DateTime.Now)
                End If

                'Setup SQL Azure for Sync
                Dim SqlAzureProv As SqlSyncScopeProvisioning = New SqlSyncScopeProvisioning(SqlAzureConn, myScope)
                If SqlAzureProv.ScopeExists(ScopeName) = False Then
                    'Apply the scope provisioning.
                    Debug.Print("Provisioning SQL Azure for Sync " + DateTime.Now)
                    SqlAzureProv.Apply()
                    Debug.Print("Done Provisioning SQL Azure for Sync " + DateTime.Now)
                Else
                    Debug.Print("SQL Azure Database Already Provisioned for Sync " + DateTime.Now)

                    SqlAzureConn.Close()
                    SqlCeServerConn.Close()
                End If

            Catch Ex As Exception
                XtraMessageBox.Show(Ex.ToString)
            End Try
        End Sub

        Private Sub Sync()
            Try
                Dim SqlAzureConnectionString As String = "Server=tcp:MyServer.database.windows.net;Initial Catalog=MyDatabase;Integrated Security=False;Persist Security Info=True;User ID=MyID;Password=MyPassword;Encrypt=True;"
                Dim SqlCelocalConnectionString As String = "Data Source=|DataDirectory|\Sample.sdf;Password=MyPassword;"
                Dim SqlCeServerConn As SqlCeConnection = New SqlCeConnection(SqlCelocalConnectionString)
                Dim SqlAzureConn As SqlConnection = New SqlConnection(SqlAzureConnectionString)
                Dim ScopeName As String = "AllTableSyncGroup"
                Dim Orch As SyncOrchestrator = New SyncOrchestrator
                Dim LocalProvider = New SqlSyncProvider(ScopeName, SqlAzureConn)
                Dim Remoteprovider = New SqlCeSyncProvider(ScopeName, SqlCeServerConn)

                'Set Sync Direction
                Orch.Direction = SyncDirectionOrder.UploadAndDownload

                'Print ScopeName
                Debug.Print("ScopeName={0} ", scopeName.ToUpper())

                'Print Start Sync Time
                Debug.Print("Starting Sync " + DateTime.Now)

                'Start Sync
                ShowStatistics(Orch.Synchronize())    ''''FAILS ON THIS LINE''''

                'Close Connections
                SqlAzureConn.Close()
                SqlCeServerConn.Close()

            Catch ex As Exception
                Debug.Print(ex.ToString)
                XtraMessageBox.Show(ex.ToString)
            End Try
        End Sub

        Private Sub ShowStatistics(ByVal syncstats As SyncOperationStatistics)
            Dim message As String

            'Print Statistics
            message = "\tSync Start Time :" + SyncStats.SyncStartTime.ToString()
            Debug.Print(message)
            message = "\tSync End Time   :" + SyncStats.SyncEndTime.ToString()
            Debug.Print(message)
            message = "\tUpload Changes Applied :" + SyncStats.UploadChangesApplied.ToString()
            Debug.Print(message)
            message = "\tUpload Changes Failed  :" + SyncStats.UploadChangesFailed.ToString()
            Debug.Print(message)
            message = "\tUpload Changes Total   :" + SyncStats.UploadChangesTotal.ToString()
            Debug.Print(message)
            message = "\tDownload Changes Applied :" + SyncStats.DownloadChangesApplied.ToString()
            Debug.Print(message)
            message = "\tDownload Changes Failed  :" + SyncStats.DownloadChangesFailed.ToString()
            Debug.Print(message)
            message = "\tDownload Changes Total   :" + SyncStats.DownloadChangesTotal.ToString()
            Debug.Print(message)
        End Sub

    Wednesday, September 1, 2010 7:53 AM

Answers

  • Hi Vincent,

    I believe you are really close.  The error you are referring to is because you set the LocalProvider and RemoteProvider inacurrately.  I believe this should fix that error:

                Dim Orch As SyncOrchestrator = New SyncOrchestrator
                Orch.LocalProvider = New SqlSyncProvider(ScopeName, SqlAzureConn)
                Orch.RemoteProvider = New SqlCeSyncProvider(ScopeName, SqlCeServerConn)

    Can you check to see if that fixes the problem?  As for not seeing the provisioning.  I did want to make sure that in fact the schema is coming FROM the SQL compact database and not from SQL Azure.  If it is coming from SQL Azure (which is likely the most common case) you would need to point the DbSyncTableDescription to the SQLAzurreConn and not from SQL Compact.

    Hope that helps!

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Thursday, September 2, 2010 5:28 PM

All replies

  • Hi Vincent,

    I believe you are really close.  The error you are referring to is because you set the LocalProvider and RemoteProvider inacurrately.  I believe this should fix that error:

                Dim Orch As SyncOrchestrator = New SyncOrchestrator
                Orch.LocalProvider = New SqlSyncProvider(ScopeName, SqlAzureConn)
                Orch.RemoteProvider = New SqlCeSyncProvider(ScopeName, SqlCeServerConn)

    Can you check to see if that fixes the problem?  As for not seeing the provisioning.  I did want to make sure that in fact the schema is coming FROM the SQL compact database and not from SQL Azure.  If it is coming from SQL Azure (which is likely the most common case) you would need to point the DbSyncTableDescription to the SQLAzurreConn and not from SQL Compact.

    Hope that helps!

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Thursday, September 2, 2010 5:28 PM
  • I'm officially not as smart as Liam!  :) 

    This worked perfectly.  I don't know how I messed that up, but I did. It is syncing perfectly now.  I have a couple of other questions, but I logged in remotely to try the fix and will get back to this after the holiday.

    Thanks again!

    Monday, September 6, 2010 9:38 AM
  • LOL, glad that helped.

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Monday, September 6, 2010 3:01 PM