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:
- Open SQL Profiler
- Start a New Trace
- Select the Use Template TSQL
- 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;
}
