locked
Using 'Set Nocount On' in triggers interferes with Sync RRS feed

  • Question

  • I am currently using MSF v2.1.  My server is using SQL Server 2008 R2 and my client is using SQL Server 2008 R2 Express.  We use SqlSyncProvider for the provider.
    After introducing SyncFX to sync our server database to client databases we had a problem with our business application that doesn't like the count values returned from update statements being higher than 1 due to the insert triggers that are fired.
    We found this article 
    and this
    To solve this problem with counts we now edit the triggers and add 'Set NoCount On' to all triggers. 
    This solved our problem however we now get an error on the client during sync:
    Exception  :Cannot insert the value NULL into column 'Deleted', 
    table 'dbo.Forecast'; column does not allow nulls. UPDATE fails.
    This occurs when the client database is downloading rows that are inserted on the server and no other changes have occurred on either db.
    The error is reasonable since dbo.Forecast.Deleted column does not allow Null. 
    However Nulls are not allowed on both the Server and Client databases so no Null values are ever permitted in either database. Queries confirm there are no Nulls in Forecast.Deleted in either db.
    If we remove the 'Set NoCount ON' statements from the triggers the problem does not occur and a sync completes successfully.
    Is it possible the Set NoCount On is adversely affecting the Sync Framework?
    Any other suggestions to look for the cause of this issue?
    I understand from reading http://msdn.microsoft.com/en-us/library/cc305322.aspx we should be using sql server change tracking. Would this avoid needing triggers at all?
    Thanks in advance for any suggestions.
    Monday, November 28, 2011 11:51 PM

All replies

  • do you have other triggers on your tables other than the one created by Sync Framework?

    The Sync Framework triggers only touches the _tracking tables and never changes the actual rows inserted in the base table.

    the error you're getting above is on the base table itself. what set's the value for that column? is it a filter column in Sync Framework?

    Tuesday, November 29, 2011 9:25 AM
  • Hi June, thanks for your queries. We do not have any other triggers on our tables, only SyncFx created ones. You are correct the Sync Framework triggers only touch the _tracking tables. We are importing a number of rows into the base table at the server, this is when the value is set for the Deleted column and is always set to false.  On insert our application is expecting a rowcount of 1 row affected after an insert. However after the triggers fire we get a rowcount of 2 returned for each insert into the base table.  Set NoCount avoids this and our application is happy.

    Here's a psuedo code of our sync project

    Provisioning:
    provLocal = new SqlSyncScopeProvisioning(sqlConnLocal);
    provHO = new SqlSyncScopeProvisioning(sqlConnHO);
    for provLocal & provHO do
    if not prov.ScopeExists(ScopeName)
    {
      scope = DbSyncScopeDescription(ScopeName);
      scope.Tables.Add(SqlSyncDescriptionBuilder.GetDescriptonForTable(strTableName, sqlConnLocal));
      prov.PopulateFromScopeDescription(scope);
      prov.SetCreateTableDefault(Skip);
      prov.Apply();
    }
    
    Insert into the HO dbo.Forecast table is performed by our business application.
    Syncing:
    function ConfigureProvider(sqlConn)
    {
      provider = new SqlSyncProvider();
      provider.ScopeName = ScopeName;
      provider.Connection = sqlConn;
      provider.DbConnectionFailure, BatchApplied, BatchSpooled & ApplyChangeFailed set up
      provider.MemoryDataCacheSize = 1024;
      return provider;
    }
    providerLocal = ConfigureProvider(sqlLocal);
    providerHO = ConfigureProvider(sqlHO);
    orchestrator = new SyncOrchestrator();
    orchestrator.LocalProvider = providerLocal;
    orchestrator.RemoteProvider = providerHO;
    orchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;
    orchestrator.Synchronize();

    The error occurs in the last step when we sync the server db to a client db after calling orchestrator.Synchronize();  The error occurs on the client db (sqlLocal).

    The 'Deleted' column is not a filter column.

    Thanks

    Jeremy



    Jeremy
    Tuesday, November 29, 2011 10:40 AM