SQL Server Performance

Identity value error

Discussion in 'SQL Server 2005 General DBA Questions' started by viksar, Feb 28, 2008.

  1. viksar New Member

    Hi group we are having issues inserting rows in published table. This table is set to replicate (transactional replication with update, 2 way replication).
    We have a job which insert rows within this table and its coming up with error message which is attached below
    Executed as user: sa. The insert failed. It conflicted with an identity range check constraint in database 'fr_engine_db', replicated table 'dbo.fr_extract', column 'fr_extract_id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent. [SQLSTATE 42000] (Error 548) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
    So far I have done following steps
    • exec sp_adjustpublisheridentityrange @table_name = 'contact_comms',
      @table_owner = 'dbo'
    This executed successfully, However above step didn't reset the identity column values
    • exec sp_MScheckidentityrange @pubid=pubid, @checkonly=1
    but it came up with error message "
    Msg 206, Level 16, State 2, Procedure sp_MScheckidentityrange, Line 0
    Operand type clash: int is incompatible with uniqueidentifier
    "
    Reset the identity range, but this didn't help and I still come up with same problem, Can you please update me what else need to be done, I need to be done . how can I resolve this??
    Attached is the table defination
    CREATE TABLE [dbo].[fr_extract](
    [fr_config_id] [int] NOT NULL,
    [fr_extract_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [element_description] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [stylecoloursizeid] [int] NOT NULL,
    [value] [money] NULL,
    [currency] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [lastdate_populated] [smalldatetime] NULL,
    [season] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT [MSrepl_tran_version_default_44BC5E3D_2A74_42AE_9F64_143C210478A1_878626173] DEFAULT (newid()),
    CONSTRAINT [PK_fr_extract] PRIMARY KEY CLUSTERED
    (
    [fr_extract_id] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    ALTER TABLE [dbo].[fr_extract] WITH NOCHECK ADD CONSTRAINT [FK_fr_extract_fr_configuration] FOREIGN KEY([fr_config_id])
    REFERENCES [dbo].[fr_configuration] ([fr_configid])
    GO
    ALTER TABLE [dbo].[fr_extract] CHECK CONSTRAINT [FK_fr_extract_fr_configuration]
    GO
    ALTER TABLE [dbo].[fr_extract] WITH NOCHECK ADD CONSTRAINT [repl_identity_range_tran_878626173] CHECK NOT FOR REPLICATION (([fr_extract_id]>(11513000) AND [fr_extract_id]<(11523000)))
    GO
    ALTER TABLE [dbo].[fr_extract] CHECK CONSTRAINT [repl_identity_range_tran_878626173]

  2. viksar New Member

    Hi group ANY IDEAS ON ABOVE ISSUES, WHAT NEED TO BE DONE, COUDN'T FIND MUCH HELP ON INTERNET RESOURCES OR ON BOL AS WELL.
  3. viksar New Member

    Hey group
    Its been a week that I have posted this post and haven't received any reply or suggestion or any comment so far. I would appreciate if anybody / moderator can please suggest something on this issue. I am sure you must have come across this issue before.
    Thanks
  4. Adriaan New Member

    The error is occurring for this call:

    exec sp_MScheckidentityrange @pubid=pubid, @checkonly=1
    If you check the sp definition in master (the sp_MS... sprocs are undocumented in BOL) you should see that @pubid is of the uniqueidentifier type.
    I'm not sure what to make of the "pubid" value you're feeding like this @pubid=pubid (one expects a local variable or a constant expression) but it would appear that "pubid" is of the integer type, where it needs to be of the uniqueidentifier type.
  5. viksar New Member

    Yes I know about @pubid, this pubid refers to publication ID, you can check for this value from syspublications. I am using integer value for this variable but I am getting result/ errors as updated before.

Share This Page