Replication with lot's of conflict errors

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Replication with lot's of conflict errors

Author

Topic

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2010-01-07 : 10:47:53

I have setup the replication all over again (already 10 times ...)
but no matter what I change on the tables, or how I set it up, if I look at the details of the replication (SQL2005) I always get replication errors on some tables;I've picked one out, see details .

Can anyone give me some advise on what is wrong here...
It used work good (on SQL2000 and since two years on SQL2005) until a few weeks ago (see other cry for help in this forum :-))

the tables that get replicated are the first one and the last one.

this the error I get on the replication

A row update at 'SQL07.APPL' could not be propagated to 'SQL08.APPL'.
This failure can be caused by a constraint violation.
Explicit value must be specified for identity column in table 'TRANSFER_TABLE' either when IDENTITY_INSERT is set to ON
or when a replication user is inserting into a NOT FOR REPLICATION identity column.

First table is the table that's get's the original insert
/****** Object: Table [dbo].[INFO_SSCC] Script Date: 01/07/2010 15:32:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[INFO_SSCC](
[SSCC] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[ITEM_CODE] [varchar](8) COLLATE Latin1_General_CI_AS NULL,
[SHIPMENTNR] [int] NULL,
[CASECOUNT] [int] NULL,
[TOT_WEIGHT_PALLET] [decimal](10, 4) NULL,
[BEST_BEFORE_DATE] [datetime] NULL,
[PRODUCTION_DATE] [datetime] NULL,
[LINE_ID] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
[LOG_DATE] [datetime] NULL,
[READ_STATUS] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[VERIFIED] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
[LANDCODE] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
[BESTEMMINGSCODE] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
[SSCC_STATUS] [tinyint] NULL,
[PROD_DATE_LAST] [datetime] NULL,
[LOCKED_NR] [int] NULL,
[SHIPPED] [tinyint] NULL,
[RECORD_ID] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SEQNR] [int] NOT NULL CONSTRAINT [DF__INFO_SSCC__SEQNR__295A5481] DEFAULT ((0)),
[BATCH_CODE] [varchar](10) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_INFO_SSCC_BATCH_CODE] DEFAULT ((0)),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_D2BE7FC815284DFF9F3BDE3B973D8D6A] DEFAULT (newsequentialid())
) ON [PRIMARY]

******** This trigger does a insert into a Interface table **************

ALTER TRIGGER [InitiateSSCC] ON [dbo].[INFO_SSCC]
FOR INSERT
AS
BEGIN

SET NOCOUNT ON

INSERT INTO IF_INITIATESSCC
(
LNE,
TRC_LAND,
TRC_ITEM,
BST,
CASECOUNT,
BESTBEFOREDATE,
SSCC_CODE,
PRODUCTIONDATE,
PRINTDATE,
ARTICLEPLANID,
BATCHNUMBER,
PROD_DATE_LAST
)
SELECT LINE_ID,
LANDCODE,
ITEM_CODE,
BESTEMMINGSCODE,
CASECOUNT,
BEST_BEFORE_DATE,
SSCC,
PRODUCTION_DATE,
LOG_DATE,
SEQNR,
BATCH_CODE,
PROD_DATE_LAST
FROM INSERTED
WHERE VERIFIED = 1

END

******** This is the table into which the trigger here above does the insert **************

/****** Object: Table [dbo].[IF_INITIATESSCC] Script Date: 01/07/2010 15:51:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IF_INITIATESSCC](
[ROW_NBR] [int] IDENTITY(1,1) NOT NULL,
[LNE] [varchar](2) COLLATE Latin1_General_CI_AS NULL,
[TRC_LAND] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
[TRC_ITEM] [varchar](6) COLLATE Latin1_General_CI_AS NULL,
[BST] [varchar](4) COLLATE Latin1_General_CI_AS NULL,
[CASECOUNT] [float] NULL,
[BESTBEFOREDATE] [datetime] NULL,
[SSCC_CODE] [varchar](20) COLLATE Latin1_General_CI_AS NULL,
[PRODUCTIONDATE] [datetime] NULL,
[PRINTDATE] [datetime] NULL,
[ARTICLEPLANID] [int] NULL CONSTRAINT [DF_IF_INITIATESSCC_ARTICLEPLANID] DEFAULT ((0)),
[BATCHNUMBER] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
[PROD_DATE_LAST] [datetime] NULL,
CONSTRAINT [PK_IF_INITIATESSCC] PRIMARY KEY CLUSTERED
(
[ROW_NBR] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

******** On this table is a trigger that insert in the transfer_table,
which is used by a datatransfer program to insert records into Oracle database **************

ALTER TRIGGER [FILL_TRANSFER_FROM_INITIATESSCC] ON [dbo].[IF_INITIATESSCC]
FOR INSERT
AS
SET NOCOUNT ON
INSERT INTO TRANSFER_TABLE ( TABLENAME, ROW_NBR)
SELECT 'IF_INITIATESSCC', ROW_NBR FROM INSERTED

******** On this table is the final table in the chain, and the error on top comes from the insert into this table **************

/****** Object: Table [dbo].[TRANSFER_TABLE] Script Date: 01/07/2010 15:29:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TRANSFER_TABLE](
[VOLGNUMMER] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[TABLENAME] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[ROW_NBR] [int] NULL,
[TRANSFERED] [int] NULL CONSTRAINT [DF_TRANSFER_TABLE_TRANSFERED] DEFAULT ((0)),
[CREATE_DATE] [datetime] NULL CONSTRAINT [DF_TRANSFER_TABLE_CREATE_DATE] DEFAULT (getdate()),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_53771A7AD71A47538121517CDC92D5E2] DEFAULT (newsequentialid()),
CONSTRAINT [PK_TRANSFER_TABLE] PRIMARY KEY NONCLUSTERED
(
[VOLGNUMMER] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 10:54:27

did you try setting identity_insert on and then inserting? did error still occur?

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2010-01-07 : 10:59:14

did you try setting identity_insert on

Where do I need to set this on ?
On the table, on a column ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 11:01:57

[code]
set identity_insert <table name> on
...
your insert code
[/code]

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2010-01-08 : 03:05:23

Hey Visakh16,

Thanks for your help.
I've modified on the database of the subscriber the trigger to following code.

ALTER TRIGGER [FILL_TRANSFER_FROM_INITIATESSCC] ON [dbo].[IF_INITIATESSCC]
FOR INSERT
AS
SET NOCOUNT ON
SET IDENTITY_INSERT dbo.TRANSFER_TABLE ON
INSERT INTO TRANSFER_TABLE ( TABLENAME, ROW_NBR)
SELECT 'IF_INITIATESSCC', ROW_NBR FROM INSERTED

Do I then need to restart the replication agent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 03:14:56

quote:
Originally posted by dyckwal

Hey Visakh16,

Thanks for your help.
I've modified on the database of the subscriber the trigger to following code.

ALTER TRIGGER [FILL_TRANSFER_FROM_INITIATESSCC] ON [dbo].[IF_INITIATESSCC]
FOR INSERT
AS
SET NOCOUNT ON
SET IDENTITY_INSERT dbo.TRANSFER_TABLE ON
INSERT INTO TRANSFER_TABLE ( TABLENAME, ROW_NBR)
SELECT 'IF_INITIATESSCC', ROW_NBR FROM INSERTED

Do I then need to restart the replication agent

try restarting
also remember to set it back to off after insert

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-08 : 10:14:55

the problem isn't identity insert.

the problem is that you are writing to your subscriber. as soon as you stop doing that the problem will go away

subscriber needs to be treated as read only

dyckwal
Yak Posting Veteran

58 Posts

Posted - 2010-01-11 : 04:52:49

the problem isn't identity insert.

the problem is that you are writing to your subscriber. as soon as you stop doing that the problem will go away

subscriber needs to be treated as read only

Hello Russell, what do you mean with this. If it is a subscriber, you always write to the subscriber to update it and keep the two databses in sync...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-11 : 19:32:29

The only write operations that should occur on your subscriber(s) is the replication itself.

If your applications write to your subscribers you're begging for this kind of trouble.

I see you've triggered a table on the subscriber. Better to drop the trigger on the subscriber and replicate the table that the trigger is writing to as well.

Replication will manage your identity fields just fine until you let writes occur on the subscribers that insert into identity fields.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-11 : 19:48:16

quote:
Originally posted by visakh16

quote:
Originally posted by dyckwal

Hey Visakh16,

Thanks for your help.
I've modified on the database of the subscriber the trigger to following code.

ALTER TRIGGER [FILL_TRANSFER_FROM_INITIATESSCC] ON [dbo].[IF_INITIATESSCC]
FOR INSERT
AS
SET NOCOUNT ON
SET IDENTITY_INSERT dbo.TRANSFER_TABLE ON
INSERT INTO TRANSFER_TABLE ( TABLENAME, ROW_NBR)
SELECT 'IF_INITIATESSCC', ROW_NBR FROM INSERTED

Do I then need to restart the replication agent

try restarting
also remember to set it back to off after insert

You do not restart after modifying a trigger.

dyckwal, check if you are using the "NOT FOR REPLICATION" option on your problematic table.

Could you show us the DDL for the problematic table in both the publisher and subscriber databases?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources