Answered by:
Sync Framework does not generate 'Set Identity_Insert ON' for non Int columns

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!
- Proposed as answer by jigu2014Microsoft employee, Editor Wednesday, December 29, 2010 7:43 PM
- Marked as answer by Vindana Madhuwantha MSFT Wednesday, December 29, 2010 10:36 PM
Wednesday, December 29, 2010 7:06 PMAnswerer
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
GOWednesday, December 29, 2010 12:08 AMAnswerer -
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!
- Proposed as answer by jigu2014Microsoft employee, Editor Wednesday, December 29, 2010 7:43 PM
- Marked as answer by Vindana Madhuwantha MSFT Wednesday, December 29, 2010 10:36 PM
Wednesday, December 29, 2010 7:06 PMAnswerer -
Thanks Jin for looking into this!Wednesday, December 29, 2010 10:37 PM