none
Error on initial data sync RRS feed

  • Question

  • I have used the SQL Azure Data Sync Tool to create my Azure database and to create the SQL Server Agent job to sync the data. This worked successfully.

    When I then run the created job, it fails with the error below:

     

    TITLE: Start Jobs - VM-WIN7-TEST
    ------------------------------

    Execution of job 'SyncToSQLAzure-Sync_Troy_Test' failed.  See the history log for details.

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    That's all there is. I have searched the system but can't find any error logs other than the Event Log and there is nothing relevant in there.

    I then had a flash of inspiration and pasted the job step into a DOS box (showing my age!). This then gave me something to go on:

    Sql Azure Batches Applied = 4
    Sql Azure Batches Applied = 5
    Microsoft.Synchronization.Data.DbSyncException: Changes for table 'troy.JOBLIST'
     is not enumerated in right order. Ensure you add an order by Convert(nvarchar,
    PKColumn) for all primary keys in the table.
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncProvider.WriteScopeInf
    oAndCommitTransaction(DbSyncScopeMetadata scopeMetadata, ChangeApplicationAction
     action)
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncProvider.ApplyChangesI

    There is more, but I expect that this is the relevant part.

    The script used to create that table is:

    /****** Object:  Table [troy].[JOBLIST]    Script Date: 07/21/2010 14:58:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [troy].[JOBLIST](
     [Master_Id] [varchar](15) NOT NULL,
     [Job_Id] [varchar](15) NOT NULL,
     [Type] [varchar](8) NOT NULL,
     [Status] [varchar](1) NULL,
     [History_Id] [varchar](15) NULL,
     [Advert_Id] [varchar](15) NULL,
     [Comment_Text] [varchar](250) NULL,
     [Comment_Text_2] [varchar](250) NULL,
     [Comment_Text_3] [varchar](250) NULL,
     [Comment_Text_4] [varchar](250) NULL,
     [Comment_Text_5] [varchar](250) NULL,
     [Comment_Text_6] [varchar](250) NULL,
     [Comment_Text_7] [varchar](250) NULL,
     [Comment_Text_8] [varchar](250) NULL,
     [Comment_Text_9] [varchar](250) NULL,
     [Comment_Text_10] [varchar](250) NULL,
     [Date_Updated] [datetime] NULL,
     [Updated_By] [varchar](10) NULL,
     [Proximity] [varchar](16) NULL,
     [Proximity_Id] [varchar](16) NULL,
     [Ok_To_Send_Cv] [varchar](1) NULL,
     [Date_Added] [datetime] NULL,
     [Added_By] [varchar](10) NULL,
     [Time_Added] [varchar](8) NULL
    , CONSTRAINT [PK_JOBLIST] PRIMARY KEY CLUSTERED ( [Job_Id]  ASC, [Master_Id] ASC, [Type] ASC ) ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO

    Firstly, for future reference it would be helpful if there was easy access to this error text when running the job from SSMS.

    Secondly, HELP! What does the error mean and how can I resolve it please.

    Best regards,

    Gordon

    • Moved by mjayaramModerator Wednesday, August 4, 2010 6:20 PM Wrong forum (From:SyncFx – Microsoft Sync Framework (Preview Release))
    Wednesday, August 4, 2010 10:21 AM

All replies

  • Moving it to the right forum.
    Maheshwar Jayaraman - http://blogs.msdn.com/mahjayar
    Wednesday, August 4, 2010 6:19 PM
    Moderator
  • Does anyone have any ideas please?
    Monday, August 9, 2010 10:24 AM
  • The error seems to indicate a FK violation in that SQL Azure Data Sync tried to apply changes in the incorrect order.  Is this possible?  Have you verified that you ordered the tables correctly in the tool based on PK / FK ordering?

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Monday, August 9, 2010 4:35 PM
  • Hi Liam,

    There are no foreign keys in the whole database. Yes, there are fields in some tables that are used as primary keys in others, but there are no explicit references to link the fields together.

    I understand that this might cause operational issues, but I don't see why it stops the tables loading from a valid (local) SQL database in the first place.

    I am just trying to get a test Azure database set up so that I can see how my application performs against it.

    Tuesday, August 10, 2010 1:34 PM
  • Progress!

    It turned out that there are nulls in some rows of this table, in the PK fields. The original test data is very old so they may have got in a long time ago. However, I am surprised that the DTSS didn't complain when I copied the original data into the source database used for this test (with the PK constraints on it).

    I synced just that table to SQL Azure (with the nulls) and got no errors. Once I removed the nulls from the source DB then the sync worked when I synced other tables also.

    A different table has just given the same error but there are no nulls in it. I am experimenting with the sync order...........

    Tuesday, August 10, 2010 3:20 PM
  • That is great to hear. Thanks for letting us know.

    Liam


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Tuesday, August 10, 2010 3:25 PM
  • Hi Liam,

    The other table is more confusing. There are no nulls in it. The NOTEDT table is only referenced by the NOTEHD table. I have enclosed the error and the table creation scripts - any help would be appreciated. It makes no difference which order they table are synced.


    C:\>
    C:\>"C:\Program Files\Microsoft Sync Framework\Power Pack For SQL Azure November
     CTP\SyncLocalSqlAzureDatabase.exe" -localServer localhost -localDb xxxx_Test -S
    qlAzureDb xxxx_Azure_Test -scope Sync_xxxx_Test -SqlAzureServer xxxxxxxxx.datab
    ase.windows.net -SqlAzureUser xxxxxx -SqlAzurePassword xxxxxxxxx-ConflictResolutionPolicy SqlAzureWins
    Local Table: xxxx.NOTEHD Progress: SelectingChanges
    Local Table: xxxx.NOTEDT Progress: SelectingChanges
    Session Progress: ChangeEnumeration
    Sql Azure Batches Applied = 1
    Sql Azure Batches Applied = 2
    Local Table: xxxx.NOTEDT Progress: SelectingChanges
    Sql Azure Batches Applied = 3
    Microsoft.Synchronization.Data.DbSyncException: Changes for table 'xxxx.NOTEDT'
    is not enumerated in right order. Ensure you add an order by Convert(nvarchar, P
    KColumn) for all primary keys in the table.
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncProvider.WriteScopeInf
    oAndCommitTransaction(DbSyncScopeMetadata scopeMetadata, ChangeApplicationAction
     action)
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncProvider.ApplyChangesI
    nternal(DataSet changes, DbSyncScopeMetadata scopeMetadata, Boolean isDataBatche
    d, Boolean isLastBatch)
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureBatchConsumer.ApplyChanges
    InChunks(DataSet changes, DbSyncScopeMetadata scopeMetadata, Boolean isLastBatch
    )
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureBatchConsumer.ConsumeBatch
    (DataSet changes, DbSyncScopeMetadata scopeMetadata, Boolean isLastBatch)
       at Microsoft.Synchronization.Data.SqlAzure.SqlAzureSyncProvider.ProcessChange
    Batch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, Obje
    ct changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessi
    onStatistics)
       at Microsoft.Synchronization.KnowledgeProviderProxy.ProcessChangeBatch(CONFLI
    CT_RESOLUTION_POLICY resolutionPolicy, ISyncChangeBatch pSourceChangeManager, Ob
    ject pUnkDataRetriever, ISyncCallback pCallback, _SYNC_SESSION_STATISTICS& pSync
    SessionStatistics)
       at Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTI
    ON_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)
       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(Syn
    cIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, Knowledg
    eSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCa
    llbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAd
    apter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32&
     changesApplied, Int32& changesFailed)
       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(
    SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncP
    rovider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied,
     Int32& changesFailed)
       at Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()
       at Microsoft.Synchronization.SyncOrchestrator.Synchronize()
       at SyncLocalSqlAzureDatabase.Program.Main(String[] args)

    C:\>


    /****** Object:  Table [xxxx].[NOTEHD]    Script Date: 07/21/2010 14:58:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [xxxx].[NOTEHD](
     [Notehd_Id] [varchar](15) NOT NULL,
     [Date_Entered] [datetime] NULL,
     [Date_Updated] [datetime] NULL,
     [Entered_By] [varchar](10) NULL,
     [Updated_By] [varchar](10) NULL,
     [Summary] [varchar](60) NULL,
     [Master_Id] [varchar](15) NULL,
     [Type] [varchar](10) NULL
    , CONSTRAINT [PK_NOTEHD] PRIMARY KEY CLUSTERED ( [Notehd_Id]  ASC ) ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [xxxx].[NOTEDT]    Script Date: 07/21/2010 14:58:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [xxxx].[NOTEDT](
     [Page_No] [decimal](6, 0) NOT NULL,
     [Note_Text] [varchar](250) NULL,
     [Notehd_Id] [varchar](15) NOT NULL
    , CONSTRAINT [PK_NOTEDT] PRIMARY KEY CLUSTERED ( [Notehd_Id]  ASC, [Page_No] ASC ) ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO

    Tuesday, August 10, 2010 3:35 PM
  • Hi Troy99,

    Unfortunately it sounds like you are running into a bug that we had in this version of the SQl Azure Provider.  This issie is in the ordering of changes that we did.  What is happening is that the converstion of convert(NVarchar, decimalcol) has a bug in it that was giving un an incorrect ordering. 

    The only way to resolve this would be disable our batching.  Unfortunately that is not possible using the SyncLocalDatabase.exe that SQL Agent is calling.  Luckily it is not hard to create your own SyncLocalDatabase.exe application through code in about 10 lines of code.  There is an example of how to do this on our blog here:

    http://blogs.msdn.com/b/sync/archive/2010/01/25/how-to-synchronize-from-sql-azure-to-sql-server-using-visual-basic.aspx

    The only change to this code you would need is to disable the batching.

    Liam

     


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Tuesday, August 10, 2010 8:30 PM
  • Many thanks Liam,

    I have managed to do a sync with that code. However, I couldn't see how to disable batching.

    The table I was having trouble with has synced, but there are a lot of tables so I don't yet know if everything is OK.

    How do I disable batching please, or can you point me to some documentation that describes the whole tool.

    Friday, August 13, 2010 2:16 PM
  • Hi Troy,

    If you do not set batching in the code (using the PowerPack provider) it will be disabled.  The executable actually sets batching to something like 50K which is likley the problem.  I am including a really simple (untested) snippet of code that should work for executing sync...

    namespace ConsoleApplication1
    {
      class Program
      {
    
        static void Main(string[] args)
        {
          try
          {
    
            string sqlServerConnectionString = "Data Source = (local); Initial Catalog = AdventureWorksLT2008; Trusted_Connection=True;";
            string sqlAzureConnectionString = "Server=tcp:[INSERT SQL AZURE SERVER].database.windows.net;Database=AdventureWorks;User ID=[INSERT SQL AZURE USER NAME]@[INSERT SQL AZURE SERVER];Password=[INSERT SQL AZURE PASSWORD];Trusted_Connection=False;Encrypt=True;";
    
            SyncOrchestrator orch = new SyncOrchestrator
            {
              LocalProvider = new SqlSyncProvider("Sync_AdventureWorksLT2008", new SqlConnection(sqlServerConnectionString)),
              RemoteProvider = new SqlAzureSyncProvider("Sync_AdventureWorksLT2008", new SqlConnection(sqlAzureConnectionString)),
              Direction = SyncDirectionOrder.UploadAndDownload
            };
            Console.WriteLine("Starting Sync " + DateTime.Now);
            ShowStatistics(orch.Synchronize());
          }
          catch (Exception ex)
          {
            Console.WriteLine(ex);
          }
          Console.WriteLine("Hit any key to quit");
          Console.ReadKey();
        }
    
        public static void ShowStatistics(SyncOperationStatistics syncStats)
        {
          string message;
          message = "\tSync Start Time :" + syncStats.SyncStartTime.ToString();
          Console.WriteLine(message);
          message = "\tSync End Time  :" + syncStats.SyncEndTime.ToString();
          Console.WriteLine(message);
          message = "\tUpload Changes Applied :" + syncStats.UploadChangesApplied.ToString();
          Console.WriteLine(message);
          message = "\tUpload Changes Failed :" + syncStats.UploadChangesFailed.ToString();
          Console.WriteLine(message);
          message = "\tUpload Changes Total  :" + syncStats.UploadChangesTotal.ToString();
          Console.WriteLine(message);
          message = "\tDownload Changes Applied :" + syncStats.DownloadChangesApplied.ToString();
          Console.WriteLine(message);
          message = "\tDownload Changes Failed :" + syncStats.DownloadChangesFailed.ToString();
          Console.WriteLine(message);
          message = "\tDownload Changes Total  :" + syncStats.DownloadChangesTotal.ToString();
          Console.WriteLine(message);
        }
    
      }
    }
    
    

     


    Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/
    Monday, August 16, 2010 5:35 PM