none
Issue SyncFramework 2.1 - Auto Increment (Identity) Columns that are outside the Primary Key RRS feed

  • Question

  • If you use an Identity Column that is outside the primary key.  The Sync Framework is creating an update stored procedure that is attempting to update the identity column.  While for inserts TSQL can leverage:  SET IDENTITY_INSERT [ColumnName] ON, there is no such animal for updates.

    If the column was part of the PrimaryKey, it would not be part of the Update Stored Procedure.

    Here is my Lead Table, notice LeadKey is an IDENTITY column, but it is outside the PrimaryKey, which is LeadID a GUID.

    CREATE TABLE [dbo].[Leads](
    	[LeadID] [uniqueidentifier] NOT NULL,
    	[LeadKey] [int] IDENTITY(0,1) NOT NULL,
    	[Authenticated] [bit] NULL,
    	[Name] [nvarchar](100) NULL,
    	[Email] [nvarchar](50) NULL,
    	[Phone] [nvarchar](30) NULL,
    	[UserAgentID] [int] NULL,
    
     CONSTRAINT [PK_Leads] PRIMARY KEY CLUSTERED 
    (
    	[LeadID] ASC
    )WITH (PAD_INDEX = OFF, 
    	 STATISTICS_NORECOMPUTE = OFF, 
    	 IGNORE_DUP_KEY = OFF, 
    	 ALLOW_ROW_LOCKS = ON, 
    	 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    

    Here is the Update Stored Proc created from my Lead Table

    CREATE PROCEDURE [Leads_update]
    	@P_1 UniqueIdentifier,
    	@P_2 Int,
    	@P_3 Bit,
    	@P_4 NVarChar(100),
    	@P_5 NVarChar(50),
    	@P_6 NVarChar(30),
    	@P_7 Int,
    	@sync_force_write Int,
    	@sync_min_timestamp BigInt,
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; 
    UPDATE [Leads] SET [LeadKey] = @P_2, 
    		 [Authenticated] = @P_3, 
    		 [Name] = @P_4, 
    		 [Email] = @P_5, 
    		 [Phone] = @P_6, 
    		 [UserAgentID] = @P_7 
    FROM [Leads] [base] JOIN [Leads_tracking] [side] 
    
    ON [base].[LeadID] = [side].[LeadID] 
    
    WHERE ([side].[local_update_peer_timestamp] <= @sync_min_timestamp OR @sync_force_write = 1) 
     AND ([base].[LeadID] = @P_1); 
    
    SET @sync_row_count = @@ROWCOUNT;
    
    END
    

    In order to view this yourself:

    1. Open SQL Profiler
    2. Start a New Trace
    3. Select the Use Template TSQL
    4. Execute your action...

    You'll find it dies on the creation of the update Stored Proc.  I'll send an 'Update' ;) after I figure out how to fix this.  But if anyone on the Sync Team could offer suggestions that would be awesome!

    Thanks:
    Dylan

     


    Dylan Phillips -- Update Getting closer to the root cause

     

    Microsoft.Synchronization.Data.SqlServer.SqlSyncProcedureHelper.BuildUpdateCommand()

    Notice we are using this._tableDesc.NonPkMutableColumns().  My AutoIncrement (immutable) is coming up in this enumeration.

    private SqlCommand BuildUpdateCommand()
    {
     if (!this._tableDesc.HasNonPkMutableColumns)
     {
     return this.BuildInsertCommand();
     }
    
     SqlCommand sqlCommand = new SqlCommand();
     sqlCommand.CommandTimeout = this._commandTimeout;
     StringBuilder builder = new StringBuilder();
     this.AddColumnParametersToCommand(sqlCommand);
     SqlParameter parameter = new SqlParameter("@sync_force_write", SqlDbType.Int);
     sqlCommand.Parameters.Add(parameter);
     SqlParameter parameter2 = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
     sqlCommand.Parameters.Add(parameter2);
     SqlParameter parameter3 = new SqlParameter("@sync_row_count", SqlDbType.Int);
     parameter3.Direction = ParameterDirection.Output;
     sqlCommand.Parameters.Add(parameter3);
     builder.Append(ClearSyncRowCountParameter(parameter3)).Append("; ");
     builder.Append("UPDATE " + this._tableDesc.LocalName + " SET " + QueryStringUtils.CommaSeparatedUpdateFromParameters(this._tableDesc.NonPkMutableColumns));
     builder.Append(" FROM " + this._tableDesc.LocalName + " " + BaseTableAlias + " JOIN " + this._trackingTableName.QuotedString + " " + TrackingTableAlias + " ON ");
     builder.Append(QueryStringUtils.JoinTwoTablesOnClause(this._tableDesc.PkColumns, BaseTableAlias, TrackingTableAlias));
     builder.Append(" WHERE (" + TrackingTableAlias + "." + this._trackingColNames.LocalUpdatePeerTimestamp + " <= @sync_min_timestamp OR @sync_force_write = 1) AND ");
     builder.Append("(" + QueryStringUtils.WhereColumnAndParameters(this._tableDesc.PkColumns, BaseTableAlias) + ");");
     builder.Append(" SET " + parameter3.ParameterName + " = @@ROWCOUNT;");
     sqlCommand.CommandText = builder.ToString();
     return sqlCommand;
    }
    
    So, I'm digging into DbSyncTableDescription, and now things get a bit uglier do to the impact of reflect, but I think I'm getting close.
     
    DbSyncTableDescription.NonPkMutableColumns returns and internal class <get_NonPkMutableColumns>
     
    internal virtual IEnumerable<DbSyncColumnDescription> NonPkMutableColumns
    {
     get
     {
      <get_NonPkMutableColumns>d__f _f = new <get_NonPkMutableColumns>d__f(-2);
      _f.<>4__this = this;
      return _f;
     }
    }
    
    
    And  get_NonPkMutableColumns.MoveNext() looks like where we are doing the checking for (Not PK) and (Not Mutable).  Notice in Label_0044, we are only checking '.IsPrimaryKey', which works fine for the (NonPk) part of MutableColumns.  But, it doesn't check for mutability...i.e. (.AutoIncrementStepSpecified).
     
    private bool MoveNext()
    {
     bool flag;
     try
     {
      switch (this.<>1__state)
      {
       case 0:
        this.<>1__state = -1;
        this.<>7__wrap11 = this.<>4__this.MutableColumns.GetEnumerator();
        this.<>1__state = 1;
        goto Label_0080;
       case 2:
        this.<>1__state = 1;
        goto Label_0080;
       default:
        goto Label_00A7;
      }
    
     Label_0044:
      this.<colDesc>5__10 = this.<>7__wrap11.Current;
      if (!this.<colDesc>5__10.IsPrimaryKey)
      {
       this.<>2__current = this.<colDesc>5__10;
       this.<>1__state = 2;
       return true;
      }
    
     Label_0080:
    
      if (this.<>7__wrap11.MoveNext())
      {
       goto Label_0044;
      }
      this.<>1__state = -1;
      if (this.<>7__wrap11 != null)
      {
       this.<>7__wrap11.Dispose();
      }
    
     Label_00A7:
      flag = false;
     }
    
     fault
     {
      ((IDisposable) this).Dispose();
     }
    
     return flag;
    }
    
     
    Friday, January 28, 2011 2:00 PM

Answers

  • Hi Dylan

    The problem you are facing is due to a limitation in our current version of Microsoft Synchronization Framework 2.1, where the Database provider does not synchronize correctly identity columns that are primary keys.  There is however a work around which is to add the identity column to a cluster index.

    You would have to modify your table and add the LeadKey to the cluster index as follows:

    CREATE TABLE [dbo].[Leads](
    	[LeadID] [uniqueidentifier] NOT NULL,
    	[LeadKey] [int] IDENTITY(0,1) NOT NULL,
    	[Authenticated] [bit] NULL,
    	[Name] [nvarchar](100) NULL,
    	[Email] [nvarchar](50) NULL,
    	[Phone] [nvarchar](30) NULL,
    	[UserAgentID] [int] NULL,
    
     CONSTRAINT [PK_Leads] PRIMARY KEY CLUSTERED 
    (
    	[LeadID] ASC,
    	[LeadKey] ASC	
    )WITH (PAD_INDEX = OFF, 
    	 STATISTICS_NORECOMPUTE = OFF, 
    	 IGNORE_DUP_KEY = OFF, 
    	 ALLOW_ROW_LOCKS = ON, 
    	 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    

    Maria del Mar Alvarez Rohena Microsoft Sync Framework
    Wednesday, February 2, 2011 6:30 PM

All replies

  • Hi Dylan

    One of the limitations of your DB Sync Provider is that it does not handle identity columns that are non-primary keys. There is a work around to this issue which is to add LeadKey to the your primary key clustered index as follows:

    CREATE TABLE [dbo].[Leads](
    	[LeadID] [uniqueidentifier] NOT NULL,
    	[LeadKey] [int] IDENTITY(0,1) NOT NULL,
    	[Authenticated] [bit] NULL,
    	[Name] [nvarchar](100) NULL,
    	[Email] [nvarchar](50) NULL,
    	[Phone] [nvarchar](30) NULL,
    	[UserAgentID] [int] NULL,
    
     CONSTRAINT [PK_Leads] PRIMARY KEY CLUSTERED 
    (
    	[LeadID] ASC,
    [LeadKey] ASC
    )WITH (PAD_INDEX = OFF, 
    	 STATISTICS_NORECOMPUTE = OFF, 
    	 IGNORE_DUP_KEY = OFF, 
    	 ALLOW_ROW_LOCKS = ON, 
    	 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    

     with this modification, things should work.


    Maria del Mar Alvarez Rohena Microsoft Sync Framework
    Tuesday, February 1, 2011 8:44 PM
  • Hi Dylan

    The problem you are facing is due to a limitation in our current version of Microsoft Synchronization Framework 2.1, where the Database provider does not synchronize correctly identity columns that are primary keys.  There is however a work around which is to add the identity column to a cluster index.

    You would have to modify your table and add the LeadKey to the cluster index as follows:

    CREATE TABLE [dbo].[Leads](
    	[LeadID] [uniqueidentifier] NOT NULL,
    	[LeadKey] [int] IDENTITY(0,1) NOT NULL,
    	[Authenticated] [bit] NULL,
    	[Name] [nvarchar](100) NULL,
    	[Email] [nvarchar](50) NULL,
    	[Phone] [nvarchar](30) NULL,
    	[UserAgentID] [int] NULL,
    
     CONSTRAINT [PK_Leads] PRIMARY KEY CLUSTERED 
    (
    	[LeadID] ASC,
    	[LeadKey] ASC	
    )WITH (PAD_INDEX = OFF, 
    	 STATISTICS_NORECOMPUTE = OFF, 
    	 IGNORE_DUP_KEY = OFF, 
    	 ALLOW_ROW_LOCKS = ON, 
    	 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    

    Maria del Mar Alvarez Rohena Microsoft Sync Framework
    Wednesday, February 2, 2011 6:30 PM
  • This can also be done within the SyncFramework, it's just ugly.  Any word on when the AutoNumber outside the primary key issue will be fixed?

    DbSyncTableDescription leadsTable = SqlSyncDescriptionBuilder.GetDescriptionForTable("Leads", azureConnection);
    //Forcing the LeadKey into the Primary Key
    leadsTable.Columns["[LeadKey]"].IsPrimaryKey = true;
    

    Dylan Phillips
    Friday, February 11, 2011 12:54 PM
  • While there is a workaround like you noticed, I would like to ask why there is a need for this column and if so, why should it be an identity column in your schema?

    Currently there is no plan of supporting auto-identity range management in the SyncFx for the near future.


    This posting is provided AS IS with no warranties, and confers no rights
    Wednesday, February 16, 2011 8:44 AM