Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

There is already an object named 'FK_OSS_KBN_PC' in the database.(Error: 14151, Severity: 18, State: 1) Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 3:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:41 AM
Points: 83, Visits: 454
Hi All,

I am getting this below error and i can't figure out why this is happening.if anyone has ever encountered this please suggest me on the same.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Distribution Subsystem: agent ******** failed. There is already an object named 'Foriegn key name' in the database.

We are using Transactional up datable replication.This error occurring continuously.

I have checked the synchronization status.There i am getting message like "There is already an object named 'Foreign key name' in the database"

At Replication monitoring i am getting errors like

There is already an object named 'Foreign key name' in the database. (Source: MSSQLServer, Error number: 2714)
Get help: http://help/2714

Please advice on the same.




Post #1337743
Posted Tuesday, July 31, 2012 3:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
At the subscriber, that foreign key already exists somehow so the replication can't create it. Drop or rename the one in the subscriber database.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1337748
Posted Tuesday, July 31, 2012 4:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:41 AM
Points: 83, Visits: 454
Hi Sir

Thanks for the quick reply.

I have checked the details of that Foreign Key.I checked the created _ datetime in both publisher and subscriber.

In subscriber :1.07AM
In Publisher :1.21AM

I think this is causing the issue.If we drop r rename will there be any impact .Any prerequisites we have to take for this??

Kindly suggest me on the same.
Post #1337765
Posted Tuesday, July 31, 2012 4:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
It's not a duplicate key. It's a foreign key with the same name.

Check the subscriber database for objects with that name. If you find one, either drop it (if it's the same foreign key that the replication is trying to create) or rename it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1337769
Posted Tuesday, July 31, 2012 4:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:41 AM
Points: 83, Visits: 454
Sorry for the inconvenience.i provided wrong timings.Please find the below details

At publisher it was created :1.20Am
At Subscriber it was created :2.12Am

Post #1337772
Posted Tuesday, July 31, 2012 4:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
Was it created manually or was it created by the replication agents?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1337774
Posted Tuesday, July 31, 2012 6:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:41 AM
Points: 83, Visits: 454
If we do rename will there be any impact
Post #1337823
Posted Tuesday, July 31, 2012 6:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
As long as it's a different object (not exactly the same foreign key that the replication is trying to create), should be OK as long as no code references it.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1337828
Posted Tuesday, July 31, 2012 10:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 7:41 AM
Points: 83, Visits: 454
Hi,

We didn't create that Key.And today we are getting again the below error.

Error:

The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074)

Note:We have kept subscription never expires.Still why we are getting this error.

I am not getting clear idea about rename and dropping key.Is it ok to rename or drop the foreign key with same name from subscriber??
Post #1338293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse