locked
SqlSyncProvider - Cannot insert explicit value for identity column in table 'Address' when IDENTITY_INSERT is set to OFF.

    General discussion

  • If I have a table which contains an auto incremented identity key (for example, an int value), then SqlSyncScopeProvisioning generates invalid SQL script. I know that I must synchronize with specified keys but it is a bug in "the script engine". For instance, the ID is an "int identity(0,1) not null" key in the following example:


    IF NOT EXISTS (SELECT * FROM [Address_tracking] WHERE [ID] = @ID)
    INSERT INTO [Address]
    (
    [ID], [OrganizationUID], [SettlementUID], [SettlementPart], [PostalCode],
    [POBoxNumber], [POBoxPostalCode], [PostalAddressLine1], [PostalAddressLine2]
    )
    VALUES
    (
    @ID, @OrganizationUID, @SettlementUID, @SettlementPart, @PostalCode,
    @POBoxNumber, @POBoxPostalCode, @PostalAddressLine1, @PostalAddressLine2
    )
    SET
    @sync_row_count = @@rowcount

    In addition, the ID is auto incremented in the Address_Tracking table too.
    • Edited by János Janka Sunday, June 14, 2009 9:12 AM
    • Changed type János Janka Sunday, June 14, 2009 9:12 AM
    • Moved by Max Wang_Chinasoft Thursday, April 21, 2011 1:25 AM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Friday, June 12, 2009 5:54 PM

All replies

  • Fortunately, there is a workaround. I've made a SetPrimaryKeys function:

    private DbSyncTableDescription SetPrimaryKeys(DbSyncTableDescription tableSchema,
        params string[] keys)
    {
        Contract.Requires(tableSchema != null);
        Contract.Requires(keys != null);
        Contract.Requires(Contract.ForAll(keys, key => key != null));
    
        for (var i = tableSchema.Columns.Count - 1; i >= 0; i--)
        {
            var column = tableSchema.Columns[i];
    
            // Removes the auto incremented identity keys.
            if (column.IsPrimaryKey && column.AutoIncrementStepSpecified)
            {
                tableSchema.Columns.Remove(column);
            }
            else // Sets the column's IsPrimaryKey property.
            {
                column.IsPrimaryKey = keys.Contains(column.UnquotedName);
            }
        }
        
        return tableSchema;
    }

    Yeah. Now I can define own identity keys instead of auto incremented keys and the SyncFx uses these correctly to write scripts.
    Monday, June 15, 2009 4:21 PM
  • Good day Janka!
    In the SetPrimaryKeys() keys parameter, where do you get that? Does this require changing the schema of an existing database?

    Thanks,
    Joey
    Monday, June 22, 2009 10:57 PM
  • Hi!

    My issue is that SyncFX generates invalid tables and stored procedures for change tracking, when these contain auto incremented int keys. It is no issue, when an auto incremented key is a GUID (NewsequentialId()). But it is an issue, when an auto incremented key is an int. Fortunately, SyncFx gets the schema meta data and I can modify it, then SyncFx generates correct scripts for creating change tracking infrastructure on the database. So, I can synchronize with other columns instead of auto incremented int columns.

    For instance, I have an Address table:

    CREATE TABLE [dbo].[Address](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[OrganizationUID] [uniqueidentifier] NOT NULL,
    	[SettlementUID] [uniqueidentifier] NOT NULL,
    	[SettlementPart] [nvarchar](256) NULL,
    	[PostalCode] [int] NULL,
    	[POBoxNumber] [int] NULL,
    	[POBoxPostalCode] [int] NULL,
    	[PostalAddressLine1] [nvarchar](400) NULL,
    	[PostalAddressLine2] [nvarchar](400) NULL,
     CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    I must synchronize data using the OrganizationUID and the SettlementUID. The ID is no good because SQL Server can't insert/update specified auto incremented int values. Eventually, it should be a correct way to synchronize data.
    Saturday, June 27, 2009 7:20 PM