Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Sync framework - Identity crisis RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello, 

    We implementing offline feature to an existing application. We have implemented the syn with  SQL Server internal change tracking  and over WCF. 

    All of our tables have primary key as integer, we cannot move to GUID. So as you are thinking we will have identity crises between applications. So we decided to go with the way Merge replication does, partition the primary key range. Below is the example scenario -

    Server 

    Table A - ID Range - 0 to 100

    Client 1

    Table A - ID Range - 101 to 200

    Client 2

    Table A - ID Range - 201 to 300

    how to implement this ? i know we can use 

    BCC CHECKIDENT (yourtable, reseed, value)

    CHECK (([ID]<=(100)))

    but this does not solve the issue.... Merge replication provides an option of "Not for replication" to achieve insert form clients and still maintain the set range.. can i use that somehow here?

    please help...

     

    Saturday, May 8, 2010 11:31 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I understand your problem now, and we don't have a better solution than what you have found. Even for the identity solution in SqlSyncProvider, same problem exists today.

    "Not for replica" cannot be used to solve this issue because it is for replication process only.

    Currently, I only see "dbcc checkident" as the possible solution although you must have the table owner permission. May I know why it doesn't solve your issue?

    For the TSQL command to apply remote insert changes , I will write it as:

    declare @currentIdentity int

    set @currentIdentity=@@IDENTITY

    SET IDENTITTY_INSERT [mytable] ON

    insert into [mytable]...

    SET IDENTITTY_INSERT [mytable] OFF

    dbcc checkident('[mytable]', reseed, @currentIdentity)

     

    Thanks,
    Dong

     

     

    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, May 13, 2010 7:13 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I assume that you have already set the identity seed to different values for server/client replicas, and you want to allow sync to insert values into identity column. If it is what you need, you can enable setting -- "SET IDENTITY_INSERT IdentityTable ON" to achieve it.

    Thanks,
    Dong

    This posting is provided AS IS with no warranties, and confers no rights.
    Monday, May 10, 2010 6:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Dong, 

    Thats what we are already doing now...

    say for a server table i have set the seed value as 200 and the client(ID range 1000 to 2000) inserts with identity_insert on value of ID 1000, so any value inserted in the server table will be 1001, so i have to make sure i reseed back to 1000 to avoid primary key collision.

    Is there any solution for above problem ??

    Thursday, May 13, 2010 5:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I understand your problem now, and we don't have a better solution than what you have found. Even for the identity solution in SqlSyncProvider, same problem exists today.

    "Not for replica" cannot be used to solve this issue because it is for replication process only.

    Currently, I only see "dbcc checkident" as the possible solution although you must have the table owner permission. May I know why it doesn't solve your issue?

    For the TSQL command to apply remote insert changes , I will write it as:

    declare @currentIdentity int

    set @currentIdentity=@@IDENTITY

    SET IDENTITTY_INSERT [mytable] ON

    insert into [mytable]...

    SET IDENTITTY_INSERT [mytable] OFF

    dbcc checkident('[mytable]', reseed, @currentIdentity)

     

    Thanks,
    Dong

     

     

    This posting is provided AS IS with no warranties, and confers no rights.
    Thursday, May 13, 2010 7:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Dong,

    It does the solve the problem, i was just hoping that is a better solution.

    Thanks for  your help.


    Regards,

    Ravindra

    Sunday, May 16, 2010 7:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Ravindra/Dong,

    It's been a while since this thread was created but I'm having the same problem.

    I wanted to add the DBCC CHECKINT call after sync framework stored procedure inserts.

    However, it isn't being applied when called by the Sync Framework. But if I execute the sproc from Sql Studio, the reseeding is applied.

    How did you get around this problem?

    Here is the modified Sync Framework sproc:

    ALTER PROCEDURE [dbo].[TerminalHeartBeat_insert]	
    @P_1 Int,	
    @P_2 Int,	
    @P_3 DateTime,	
    @P_4 Real,	
    @P_5 Real,	
    @sync_row_count Int OUTPUT
    AS
    BEGIN
    declare @currentIdentity int; 
    set @currentIdentity=@@IDENTITY;
    SET @sync_row_count = 0; 
    IF NOT EXISTS (SELECT * FROM [TerminalHeartBeat_tracking] WHERE [OID] = @P_1) BEGIN SET IDENTITY_INSERT [TerminalHeartBeat] ON; INSERT INTO [TerminalHeartBeat]([OID], [TerminalId], [TimeStampUtc], [CpuUsage], [MemoryAvailable]) VALUES (@P_1, @P_2, @P_3, @P_4, @P_5);  SET @sync_row_count = @@rowcount; SET IDENTITY_INSERT [TerminalHeartBeat] OFF; END 
    dbcc checkident('TerminalHeartBeat', reseed, @currentIdentity) WITH NO_INFOMSGS;
    END


    Many thanks,

    Tim

     

    Thursday, August 20, 2015 11:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    if you're using Sync Fx, try disabling the bulk inserts procedures, so you revert to a per row insert operation.
    Thursday, August 20, 2015 12:23 PM