Replication

[tropicana]

Hi,

I am setting up transactional replication and have run into a problem… Can anyone help?

The table I am publishing is called Area. The table, Area, is the same on both the publisher and the subscriber.

When executing transactional replication, the error I get from the distribution agent is:-

Cannot update identity column 'AreaId'.

Cannot update identity column 'AreaId'.
(Source: WTEVAL01\REPORTING (Data source); Error number: 8102)

This is the SQL for the table ‘Area’:-

CREATE TABLE [Area] (
    [AreaId] [int] IDENTITY (1, 1)   NOT NULL ,
    [AreaCode] [varchar] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
    [Description] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
    [ParentAreaId] [int] NULL ,
    [Priority] [int] NULL ,
    [Visible] [bit] NOT NULL CONSTRAINT [DF_Area_Visible] DEFAULT (1),
    [indent] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
    [CreateDate] [datetime] NOT NULL ,
    [ModifyDate] [datetime] NOT NULL ,
    [DisplayOrder] [int] NULL ,
 
 
 
 
    CONSTRAINT [PK_Area] PRIMARY KEY  NONCLUSTERED 
    (
        [AreaId]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
    CONSTRAINT [U_Area_Code] UNIQUE  NONCLUSTERED 
    (
        [AreaCode]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
    CONSTRAINT [FK_Area_ParentArea] FOREIGN KEY 
    (
        [ParentAreaId]
    ) REFERENCES [Area] (
        [AreaId]
    )
) ON [PRIMARY]
GO

I’ve managed to work out from reading a few sites that the constraint PK_Area is being violated so I must disable this constraint for replication.

However, I have read that primary keys, unique keys or indexes cannot be disabled.

The only alternative I can think of is to drop the constraint PK_Area, but the subscriber server is a live production server and is constantly hit with queries therefore I’ve been told that re-building the index constraint PK_Area after each replication run (which is every 10 mins) is unacceptable.

How can I get round this?

Kind Regards,
Tom

[Borik]

You need to use "NOT FOR Replication" in your identity column...

Hi,

I am setting up transactional replication and have run into a problem… Can anyone help?

The table I am publishing is called Area. The table, Area, is the same on both the publisher and the subscriber.

When executing transactional replication, the error I get from the distribution agent is:-

Cannot update identity column 'AreaId'.

Cannot update identity column 'AreaId'.
(Source: WTEVAL01\REPORTING (Data source); Error number: 8102)

This is the SQL for the table ‘Area’:-

CREATE TABLE [Area] (
    [AreaId] [int] IDENTITY (1, 1)   NOT NULL ,
    [AreaCode] [varchar] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
    [Description] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
    [ParentAreaId] [int] NULL ,
    [Priority] [int] NULL ,
    [Visible] [bit] NOT NULL CONSTRAINT [DF_Area_Visible] DEFAULT (1),
    [indent] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
    [CreateDate] [datetime] NOT NULL ,
    [ModifyDate] [datetime] NOT NULL ,
    [DisplayOrder] [int] NULL ,
 
 
 
 
    CONSTRAINT [PK_Area] PRIMARY KEY  NONCLUSTERED 
    (
        [AreaId]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
    CONSTRAINT [U_Area_Code] UNIQUE  NONCLUSTERED 
    (
        [AreaCode]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
    CONSTRAINT [FK_Area_ParentArea] FOREIGN KEY 
    (
        [ParentAreaId]
    ) REFERENCES [Area] (
        [AreaId]
    )
) ON [PRIMARY]
GO

I’ve managed to work out from reading a few sites that the constraint PK_Area is being violated so I must disable this constraint for replication.

However, I have read that primary keys, unique keys or indexes cannot be disabled.

The only alternative I can think of is to drop the constraint PK_Area, but the subscriber server is a live production server and is constantly hit with queries therefore I’ve been told that re-building the index constraint PK_Area after each replication run (which is every 10 mins) is unacceptable.

How can I get round this?

Kind Regards,
Tom