By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,208 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,208 IT Pros & Developers. It's quick & easy.

Replication

P: 1
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’:-

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [Area] (
  2.     [AreaId] [int] IDENTITY (1, 1)   NOT NULL ,
  3.     [AreaCode] [varchar] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
  4.     [Description] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
  5.     [ParentAreaId] [int] NULL ,
  6.     [Priority] [int] NULL ,
  7.     [Visible] [bit] NOT NULL CONSTRAINT [DF_Area_Visible] DEFAULT (1),
  8.     [indent] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
  9.     [CreateDate] [datetime] NOT NULL ,
  10.     [ModifyDate] [datetime] NOT NULL ,
  11.     [DisplayOrder] [int] NULL ,
  12.  
  13.  
  14.  
  15.  
  16.     CONSTRAINT [PK_Area] PRIMARY KEY  NONCLUSTERED 
  17.     (
  18.         [AreaId]
  19.     ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
  20.     CONSTRAINT [U_Area_Code] UNIQUE  NONCLUSTERED 
  21.     (
  22.         [AreaCode]
  23.     ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
  24.     CONSTRAINT [FK_Area_ParentArea] FOREIGN KEY 
  25.     (
  26.         [ParentAreaId]
  27.     ) REFERENCES [Area] (
  28.         [AreaId]
  29.     )
  30. ) ON [PRIMARY]
  31. 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
Nov 27 '06 #1
Share this Question
Share on Google+
1 Reply


P: 1
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’:-

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [Area] (
  2.     [AreaId] [int] IDENTITY (1, 1)   NOT NULL ,
  3.     [AreaCode] [varchar] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
  4.     [Description] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
  5.     [ParentAreaId] [int] NULL ,
  6.     [Priority] [int] NULL ,
  7.     [Visible] [bit] NOT NULL CONSTRAINT [DF_Area_Visible] DEFAULT (1),
  8.     [indent] [varchar] (5) COLLATE Latin1_General_CI_AS NULL ,
  9.     [CreateDate] [datetime] NOT NULL ,
  10.     [ModifyDate] [datetime] NOT NULL ,
  11.     [DisplayOrder] [int] NULL ,
  12.  
  13.  
  14.  
  15.  
  16.     CONSTRAINT [PK_Area] PRIMARY KEY  NONCLUSTERED 
  17.     (
  18.         [AreaId]
  19.     ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
  20.     CONSTRAINT [U_Area_Code] UNIQUE  NONCLUSTERED 
  21.     (
  22.         [AreaCode]
  23.     ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
  24.     CONSTRAINT [FK_Area_ParentArea] FOREIGN KEY 
  25.     (
  26.         [ParentAreaId]
  27.     ) REFERENCES [Area] (
  28.         [AreaId]
  29.     )
  30. ) ON [PRIMARY]
  31. 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
Feb 26 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.