none
Sync Framework does not generate 'Set Identity_Insert ON' for non Int columns RRS feed

  • Question

  • I have several tables that have identity columns. Most of them are identity int PK columns, and they work fine when syncing.

    But there are a few where they have identity PK columns but their data-type is not Int. They are either smallInt or TinyInt. (We know for sure that these values remain within the data limits) In this case SPs generated by the Sync FW does not contain the 'Set Identity ON' statement. 

    (Actually this is the only explanation I could come up by comparing the table data insert SPs that has 'set identity' statement with the missing ones).

    Am I doing anything wrong here? And if not what would be the solution to this problem.

    Thanks !

    Tuesday, December 28, 2010 9:41 AM

Answers

  • Yes, I can reproduce this problem. I also tried to change the above table to use smallint pk type and then it worked. so the problem seems to be with tinyint. I will log a bug for this problem. In the mean time, please use int or smallint(if it works in your case) as the pk identity column.

    Thanks!

    Wednesday, December 29, 2010 7:06 PM
    Answerer

All replies

  • Hi, which version of sync framework and which database provider type are you using?

    I tried V2.1 SqlSyncProvider, the generated insert sp has such content for a smallint identity pk column.

    CREATE PROCEDURE [dbo].[t2_insert]
     @P_1 SmallInt,
     @P_2 NVarChar(200),
     @sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; IF NOT EXISTS (SELECT * FROM [dbo].[t2_tracking] WHERE [a] = @P_1) BEGIN SET IDENTITY_INSERT [dbo].[t2] ON; INSERT INTO [dbo].[t2]([a], [b]) VALUES (@P_1, @P_2);  SET @sync_row_count = @@rowcount; SET IDENTITY_INSERT [dbo].[t2] OFF; END
    END
    GO

    Wednesday, December 29, 2010 12:08 AM
    Answerer
  • Hi Jin, Thanks for the reply.

    I am too using Sync Framework 2.1.

    For example, this is one of my tables.

     

     

    CREATE TABLE [dbo].[AbuseTypes](
    	[Id] [tinyint] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	[Modifier] [smallint] NOT NULL,
    	[Code] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     CONSTRAINT [PK_AbuseTypes] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    
    )
    

     

     

    And this is the insert SP.

     

    CREATE PROCEDURE [Sync].[Sync_AbuseTypes_insert]
    	@P_1 TinyInt,
    	@P_2 VarChar(50),
    	@P_3 SmallInt,
    	@P_4 Char(3),
    	@sync_row_count Int OUTPUT
    AS
    BEGIN
    SET @sync_row_count = 0; IF NOT EXISTS (SELECT * FROM [Sync].[Sync_AbuseTypes_tracking] WHERE [Id] = @P_1) BEGIN INSERT INTO [dbo].[AbuseTypes]([Id], [Name], [Modifier], [Code]) VALUES (@P_1, @P_2, @P_3, @P_4); SET @sync_row_count = @@rowcount; END 
    END
    GO
    

    And this is the Bulk insert SP

    CREATE PROCEDURE [Sync].[Sync_AbuseTypes_bulkinsert]
    	@sync_min_timestamp BigInt,
    	@sync_scope_local_id Int,
    	@changeTable [Sync].[Sync_AbuseTypes_BulkType] READONLY
    AS
    BEGIN
    -- use a temp table to store the list of PKs that successfully got updated/inserted
    DECLARE @changed TABLE ([Id] tinyint, PRIMARY KEY ([Id]));
    
    -- update/insert into the base table
    MERGE [dbo].[AbuseTypes] AS base USING
    -- join done here against the side table to get the local timestamp for concurrency check
    (SELECT p.*, t.local_update_peer_timestamp FROM @changeTable p LEFT JOIN [Sync].[Sync_AbuseTypes_tracking] t ON p.[Id] = t.[Id]) AS changes ON changes.[Id] = base.[Id]
    WHEN NOT MATCHED BY TARGET AND changes.local_update_peer_timestamp <= @sync_min_timestamp OR changes.local_update_peer_timestamp IS NULL THEN
    INSERT ([Id], [Name], [Modifier], [Code]) VALUES (changes.[Id], changes.[Name], changes.[Modifier], changes.[Code])
    OUTPUT INSERTED.[Id] INTO @changed; -- populates the temp table with successful PKs
    
    UPDATE side SET
    update_scope_local_id = @sync_scope_local_id, 
    scope_update_peer_key = changes.sync_update_peer_key, 
    scope_update_peer_timestamp = changes.sync_update_peer_timestamp,
    local_update_peer_key = 0,
    create_scope_local_id = @sync_scope_local_id,
    scope_create_peer_key = changes.sync_create_peer_key,
    scope_create_peer_timestamp = changes.sync_create_peer_timestamp,
    local_create_peer_key = 0
    FROM 
    [Sync].[Sync_AbuseTypes_tracking] side JOIN 
    (SELECT p.[Id], p.sync_update_peer_timestamp, p.sync_update_peer_key, p.sync_create_peer_key, p.sync_create_peer_timestamp FROM @changed t JOIN @changeTable p ON p.[Id] = t.[Id]) AS changes ON changes.[Id] = side.[Id]
    SELECT [Id] FROM @changeTable t WHERE NOT EXISTS (SELECT [Id] from @changed i WHERE t.[Id] = i.[Id])
    END
    GO
    

    As you can see it has not added the 'set identity' statement. As I said before I just guessed the reason in my first post, as it was the only different thing when compared to a working SP.

    Thanks,

    Vindana.

     

    Wednesday, December 29, 2010 6:47 AM
  • Yes, I can reproduce this problem. I also tried to change the above table to use smallint pk type and then it worked. so the problem seems to be with tinyint. I will log a bug for this problem. In the mean time, please use int or smallint(if it works in your case) as the pk identity column.

    Thanks!

    Wednesday, December 29, 2010 7:06 PM
    Answerer
  • Thanks Jin for looking into this!
    Wednesday, December 29, 2010 10:37 PM