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.