Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been trying to get a previously working replication to start working.

I have tried many settings, but I can't seem to make it work.

Here is the scenario. I dropped all the FK constraints from the destination database (call it DestDB).

I then re-initalize my replication and when I Runs I get this (de-identified) error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_TableOnlyInDestDB_MyReplicatedTable". The conflict occurred in database "DestDB", table "SomeSchema.MyReplicatedTable", column 'MyReplicatedTableId'. (Source: MSSQLServer, Error number: 547)

It is driving me nuts because the FK indicated does not exist in that database.

Now, I did copy this database (backup and restore) from another database. So the only thing I can think of is that it is somehow crossing the streams.

But that seems unlikely.

Any ideas would be great!

NOTE: This is the query I ran to ensure that I have no FKs (It returned no rows):

use DestDB

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

I also checked to ensure that the FK in question was not on the source table (in the database I am replicating from).

I have also tried: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

Also tried Dropping MyReplicatedTable. Dropped just fine and replication re-created it. (Still gave me the error though.)

I tried creating an empty database as a subscriber. That worked fine, so it is something in the DestDB that is causing the issue, but the FK in the ERROR does not exist.

share|improve this question
    
Disabling the constraint does not have the same effect as dropping it. Also what happens with a very simple SELECT * FROM DestDB.sys.foreign_keys WHERE name = N'FK_TableOnlyInDestDB_MyReplicatedTable'; or even SELECT * FROM DestDB.sys.objects WHERE name = N'FK_TableOnlyInDestDB_MyReplicatedTable';? –  Aaron Bertrand Aug 9 '13 at 21:20
    
@AaronBertrand - Alas, both of those return 0 rows. The FK is well and truely gone... I don't get why replication is complaining. (I have also tried deleting and recreating both publisher and subscriber). –  Vaccano Aug 9 '13 at 21:38
1  
Then shrug I am out of ideas. SQL Server doesn't make these error messages up, and it gets that name from the metadata of the source database, so it is there somewhere... –  Aaron Bertrand Aug 9 '13 at 21:43
1  
@Vaccano try adding a dummy record in the Publication and reinitialize or if that does not work You can set Action if name is in use property to Drop existing object and create a new one. –  Kin Aug 10 '13 at 0:27

3 Answers 3

up vote 2 down vote accepted

Sorry for waking up this dead thread, but I hate it when questions remains inconclusive. Anyway, I had the same problem. Apparently SQL server replication saves foreign keys in "dbo.MSsavedforeignkeys", which is where I found my blocker FK. A quick delete dbo.MSsavedforeignkeys where constraint_name = N'FK_TableOnlyInDestDB_MyReplicatedTable' solved my problem. Of course, there may be more "best practice" ways of cleaning up after failed replications.

share|improve this answer
    
Thank you for the answer. I don't know that questions ever are really "dead" to new answers on Stack Exchange sites. After a year, asking for more details is bad form, but a new answer is always welcome. –  Vaccano Oct 24 at 16:02

As Aaron noted, SQL Server does not invent objects, so it has to be somewhere.

My initial thought is that you are pointing replication at a different database than you think you are. Check in the "Subscription Properties" dialog what database was used to create the subscription.

Also check what type of replication you are using. If it is any of the bidirectional forms it might try to apply that breaking change to the publisher.

If that does not help, check your default extended event session to see what was actually executing when the error was raised. See http://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/ for details of how to do that.

That should allow you to figure out what is going on.

If after all that you are sure you are in the right database and replication is still tripping over the non-existent FK, I can only recommend to drop the subscription and maybe also the publication and start from scratch.

share|improve this answer
1  
+1 "at a different database" (or the same database name but on the wrong server). –  Aaron Bertrand Aug 10 '13 at 2:28

This is probably an existing foreign key in one of your Publisher tables. The replication job is trying to alter and create this foreign key on your subscriber table but it can't do this because of the data in subscriber doesn't suit to needs of the upcoming foreign key constraint.

Your data should be replicated to subscriber in any case even though you get this error.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.