none
There is already an object named 'Constraint or Foriegn Key.' in the database.(Error: 14151, Severity: 18, State: 1)

    Question

  • 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.

    • Edited by mito access Wednesday, August 01, 2012 7:00 AM
    Tuesday, July 31, 2012 9:16 AM

All replies

  • Hi,

    It looks like you are trying to apply either a snapshot or a DDL change to subscriber and It is failing at subscriber end.

    Can you run following query on subscriber? If foreign key with same name is already existing on subscriber on the same table, than you have to manually drop the foreign key from subscriber. However SQL server tries to drop the foreign key if existing but somehow in your case it is not.

    SELECT * FROM sys.foreign_keys
    WHERE object_id = OBJECT_ID(N'[dbo].[Foreign Key Name]')
    AND parent_object_id = OBJECT_ID(N'[dbo].[Parent Table Name] ')


    Kindly mark the reply as answer if they help

    • Proposed as answer by vr.babu Tuesday, July 31, 2012 10:20 AM
    Tuesday, July 31, 2012 10:14 AM
  • Hi Sunil 

    Thanks for the reply.

    I have the query at subscriber then it showing the foreign key.

    and at publisher also it is showing same .

    Tuesday, July 31, 2012 11:00 AM
  • Hi Mito,

    1) To resolve this issue you need to do the following:

    2) Launch Replication Monitor and drill-down to the publisher under "My Publishers", select the appropriate Publication, right-click and select "Agent Profiles".

    3) When the Agent Profiles window is displayed, make sure the "Distribution Agents" page is selected. Then in the Agent profiles section, click and select the "Default for New" checkbox for "Continue on data consistency errors".

    4) Finally, click OK, and then to set the new default profile for the Distribution Agent.

    5) Once all data has been replicated repeat the process to uncheck the checkbox for "Continue on data consistency errors".


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    • Proposed as answer by Basit Farooq Tuesday, July 31, 2012 11:36 AM
    Tuesday, July 31, 2012 11:30 AM
  • Did you tried dropping the foreign from subscriber? Distribution agent will automatically create it once you start the sync after dropping.

    Kindly mark the reply as answer if they help

    Tuesday, July 31, 2012 11:35 AM
  • Hi Basit,
    This not seems to data inconsistency issue. It is issue while replicating the DDL changes to subscriber.


    Kindly mark the reply as answer if they help

    Tuesday, July 31, 2012 11:36 AM
  • I won't suggest to drop foriegn key from subscriber.

    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Tuesday, July 31, 2012 11:37 AM
  • Why? Can you confirm any alternate solution if you know.

    Kindly mark the reply as answer if they help

    Tuesday, July 31, 2012 11:44 AM
  • Hi sunil,

    Today i got one more error continuously.I.e

    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.
    • Edited by mito access Wednesday, August 01, 2012 5:44 AM
    Wednesday, August 01, 2012 4:17 AM
  •  This was expected. SQL server marks the subscription as deactivated if it is error outing for more than Subscription expire period. You can see this: Right Click on Publication on publisher server and click on Properties. A new window will open, in the bottom pain of window you could see number of hour the subscription will expire.

    You have to now reinitialize the subscription. Let me know if you need help to initialization.

    Thanks,

    Sunil


    Kindly mark the reply as answer if they help

    Wednesday, August 01, 2012 6:04 AM
  • Hi sunil,

    Thank for the reply.All this was happened in 12 hours only.The retention period we kept 0-72 hrs.

    So now the solution is reinitialize the subscription.Is it right??

    And please refer the  error "There is already an object named 'Foreign key name' in the database"
    (which is starting error  causes the issue).If we done reinitialize the first error would go or  reoccur again.

    Will it solve total problem.i.e will it come back to normal position.

    Wednesday, August 01, 2012 6:18 AM
  • How big the database is? How much it took when you had initialized last time? Your issue will be resolved after reinitialization from a new snapshot.

    Kindly mark the reply as answer if they help

    Wednesday, August 01, 2012 6:48 AM
  • Its around 35 GB.It took one r two  mins.

    A small request :why this issue happened suddenly we didn't do any changes..Any ideas???

    Wednesday, August 01, 2012 6:52 AM
  • It happened because of foreign key error. Since the transaction into replication queue were not getting replicated due to this error into top of queue. SQL server has deactivated the subscription and marked it for re-initilization. One of the possible cause of foreign key error may be which I have explained in my first reply to this post. A DDL change has been performed on publisher which hasn't replicated to subscriber. Anyway you have to go for reinitilization now as the subscription is marked for reinitilization and deactivated.

    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Wednesday, August 01, 2012 8:20 AM
    Wednesday, August 01, 2012 7:46 AM
  • Thank you for your great support.I would the same and let you know the result.
    Wednesday, August 01, 2012 8:11 AM
  • Hi ,

    we got one more error again

    Replication Transaction Queue Reader Subsystem agent  failed. Failed while applying queued message to publisher

    I am trying to start the agent but error is occurring.

    Saturday, August 04, 2012 3:43 AM
  • Can you find out more details? Please look at SQL agent jobs also and see what is the error job has failed.

    Kindly mark the reply as answer if they help

    Saturday, August 04, 2012 3:45 AM
  • We have done reinitialization.For 2 days it is working fine.

    But again we are getting these errors.Today we have deleted some rows from a table which is not in replication from both tables.

    Error1

    Replication-Replication Transaction Queue Reader Subsystem: agent  failed. The Queue Reader Agent has encountered the error ''Row handle is invalid.'' when connecting to ''xxx'' on ''xxxxxxx''. Ensure that the publication and subscription are defined properly and that both servers are running.

    Error2
    Replication-Replication Transaction Queue Reader Subsystem: agent  failed. Queue Reader aborting

    When i Checked queue reader job is getting fail.



    • Edited by mito access Tuesday, August 07, 2012 9:25 AM
    Tuesday, August 07, 2012 8:11 AM