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.
