locked
MSSQL 2008 table with filtered index issue RRS feed

  • Question

  • Hi,

    I'm using the NTier project to sync two mssql 2008 instances over a WCF service. Everything it's fine until I'm tring to synchronize a table which have a unique, non-clustered, filtered index. The error received is:

    "Cannot insert duplicate key row in object 'dbo.FiscalModuleWorkstationBase' with unique index 'IX_FiscalModuleWorkstationBase_Workstation'. The duplicate key value is (6211f767-0603-e211-bdee-00155d1fa446, 1).

    The statement has been terminated.

    ". One solution I'm thinking is if I may change the synchronization steps like the insert step to be executed before the update one. Can you provide me a solution for this kind of situation?

    Thank you,

    Cosmin H

    Friday, March 29, 2013 10:58 AM

All replies

  • is it really a duplicate? that error is a SQL Server error, not specific to sync code. Sync Fx is simply bubbling it up.

    not sure how changing the order of the insert vs update would help in this error. besides, you can't really change it.

    Saturday, March 30, 2013 4:38 AM
  • actualy it is a duplicate unique key index. Let me give you  short example: i have a table named FiscalModule with colums PublicKey (uniqueidentity), WorkstationKey and IsActive (bit), and a unique filtered index onWorkstationKey and IsActive where IsActive = true which it means that only one fiscal module may be active on the workstation On the server table I make an insert: @guid, 1, true then I do a sync. This row it's replicated succesful on the client table. Now, on the server make an update on this row by changing the IsActive to false and make a new insert; now the server table looks like this:

    @guid, 1, false

    @quid, 1, true.

    After this moment, the synchronization on the client side it's faild, because the syncFx will try to make an insert with the new line and that s it...error

    Saturday, March 30, 2013 2:36 PM
  • what's your PK?
    Sunday, March 31, 2013 12:50 AM
  • The primary key of the table is the PublicKey column.
    Sunday, March 31, 2013 10:12 AM
  • looks like Sync Fx didnt pick up the right PK.

    if your PK is PublicKey and you're not using the other two columns WorkStationKey and IsActive as filter columns in your scope, you should only be seeing the PublicKey as the only column from your base table in the _tracking table.

    Open up the _tracking table and see if you have three columns there.

    you can workaround this issue by explicitly specifying the PK to use during provisioning.

    Monday, April 1, 2013 12:29 PM
  • the _traking table it's contain only the PublicKey column as PK. If I use the IX_ definition as "Unique Key" (now is "Index"), do you think the scope provision will mark the PKs correct? If not, can you give me a small example about how to explicitly specify this PKs during provisioning?

    Thank you

    Monday, April 1, 2013 2:25 PM
  • can you post the unique index definition (generate SQL Script) for the unique index on both client and server here
    Monday, April 1, 2013 11:57 PM
  • /****** Object:  Table [dbo].[FiscalModuleWorkstationBase]    Script Date: 4/2/2013 9:19:16 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[FiscalModuleWorkstationBase](
    [PublicKey] [uniqueidentifier] NOT NULL,
    [WorkstationBaseKey] [uniqueidentifier] NOT NULL,
    [FiscalModuleTypeID] [smallint] NOT NULL,
    [IsActive] [bit] NOT NULL,
    [CreatedOn] [datetime2](7) NOT NULL,
    [CreatedBy] [uniqueidentifier] NOT NULL,
    [LastModifiedOn] [datetime2](7) NOT NULL,
    [LastModifiedBy] [uniqueidentifier] NOT NULL,
    [ConfigDataValue] [xml] NULL,
     CONSTRAINT [PK_FiscalModuleWorkstationBase] PRIMARY KEY CLUSTERED 
    (
    [PublicKey] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    /****** Object:  Index [IX_FiscalModuleWorkstationBase_Workstation]    Script Date: 4/2/2013 9:19:16 AM ******/
    CREATE UNIQUE NONCLUSTERED INDEX [IX_FiscalModuleWorkstationBase_Workstation] ON [dbo].[FiscalModuleWorkstationBase]
    (
    [WorkstationBaseKey] ASC,
    [IsActive] ASC
    )
    WHERE ([IsActive]<>(0))
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[FiscalModuleWorkstationBase] ADD  CONSTRAINT [DF_FiscalModuleWorkstationBase_PublicKey]  DEFAULT (newsequentialid()) FOR [PublicKey]
    GO

    ALTER TABLE [dbo].[FiscalModuleWorkstationBase] ADD  CONSTRAINT [DF_FiscalModuleWorkstationBase_IsActive]  DEFAULT ((-1)) FOR [IsActive]
    GO

    ALTER TABLE [dbo].[FiscalModuleWorkstationBase] ADD  CONSTRAINT [DF_FiscalModuleWorkstationBase_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
    GO

    The table structure above is the same for server and client. Synchronization scope direction it may be download and upload as well.

    Tuesday, April 2, 2013 6:22 AM
  • Any ideas?
    Wednesday, April 3, 2013 11:03 AM
  • i think your insert is being applied before the update to the other row.

    try subscribing to ApplyChangeFailed event, you should see that error bubble up in there. 

    then set RetryNextSync as the resolution. that should get applied on the next sync.

    Wednesday, April 3, 2013 1:49 PM
  • Unfortunately, this solution doesn't work. The error is the same. Did you tried this solution on my scenario and it worked?
    Wednesday, April 3, 2013 3:42 PM
  • retry on next sync works perfectly fine with me.
    Thursday, April 4, 2013 11:04 AM
  • Can you tell me please, after the first synchronization there has some changes applied on the client table, and I mean, does the sync perform the update on the IsActive to false? If not, which is my case, I don't have any explanation of how it work while the next time the syncFx try to perform the same steps in the same order.
    Thursday, April 4, 2013 12:35 PM
  • i get the same error that you get  about duplicate key, i set the retry action to RetryOnNextSync and it gets sync on my next sync. nothing special.
    Thursday, April 4, 2013 12:57 PM