Replication related issue,

Question

Replication related issue,

diva.mayas

Mr or Mrs. 500

Points: 569
More actions
June 28, 2010 at 12:50 am

#226085

Replication related issue,
I am explaining my architecture .
I have created
1.2 Publisher on table vendors script I have given below,
2. A Distributor
3.2 Subscribers
Data replication set up is like this as :
Table VENDORS gets replicated from 2-publishers to 2-subcribers via-Distributor.
While replication, ERROR issued in Distributor database as :
Here,
What must happen is
Pub1 (creates pubs table vendors) –> inserts (vendors) data to Distributor. -> pull by subscribers
What is happening now for me is ,
Pub1 (creates pubs table vendors-done) -> Throws error at distributor database as
Replication-Replication Distribution Subsystem: agent abc-serv1\PRD01-star-star Billing-PROD-VREPL1\REPL01-25 failed.
Violation of PRIMARY KEY constraint 'PK_vendors'. Cannot insert duplicate key in object 'dbo.vendors'.
Error is issued while operation is done between Publishers to Distributor.
Script for table
--------------------
CREATE TABLE [dbo].[vendors](
[RECNO] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[VENDORID] [int] NOT NULL,
[COMPANY] [varchar](40) NULL,
[CONTACT] [varchar](40) NULL,
[STREET1] [varchar](40) NULL,
[STREET2] [varchar](40) NULL,
[CITY] [varchar](30) NULL,
[STATE] [char](2) NULL,
[ZIP] [char](10) NULL,
[PHONE] [char](14) NULL,
[FAX] [char](14) NULL,
[STATESERV] [char](2) NULL,
[ACTIVE] [bit] NOT NULL,
[MAILGROUP] [char](12) NULL,
[ONSYSTEM] [bit] NOT NULL,
[REFERENCE] [char](10) NULL,
[RESUME] [text] NULL,
[FIRMNO] [char](4) NULL,
[FREDDIEFC] [bit] NOT NULL,
[FREDDIEBK] [bit] NOT NULL,
[FREDDIEEV] [bit] NOT NULL,
[FREDDIECL] [bit] NOT NULL,
[Billable] [bit] NOT NULL,
[BillingContact] [varchar](40) NULL,
[USFN] [bit] NOT NULL,
[LOGS] [bit] NOT NULL,
CONSTRAINT [PK_vendors] PRIMARY KEY CLUSTERED (
[VENDORID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_FREDDIEFC] DEFAULT (0) FOR [FREDDIEFC] GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_FREDDIEBK] DEFAULT (0) FOR [FREDDIEBK] GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_FREDDIEEV] DEFAULT (0) FOR [FREDDIEEV] GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_FREDDIECL] DEFAULT (0) FOR [FREDDIECL] GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_Billable] DEFAULT (0) FOR [Billable] GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_USFN] DEFAULT ((0)) FOR [USFN] GO
ALTER TABLE [dbo].[vendors] ADD CONSTRAINT [DF_vendors_LOGS] DEFAULT ((0)) FOR [LOGS] GO

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

diva.mayas (6/28/2010)
Replication related issue,
IReplication-Replication Distribution Subsystem: agent abc-serv1\PRD01-star-star Billing-PROD-VREPL1\REPL01-25 failed.
Violation of PRIMARY KEY constraint 'PK_vendors'. Cannot insert duplicate key in object 'dbo.vendors'.
Error is issued while operation is done between Publishers to Distributor.

I think you are trying to insert duplicate record with the same primary key value. Please check that.

"More Green More Oxygen !! Plant a tree today"

raghumvj Old Hand Points: 315 More actions · Answer 2

As a workaround first disable clustered index and constraint on your table,

then add one new column and enable identity insert with primary key on it, then try to re-publish table (vendor). This over comes the primary key violation.