0

I have a SQL 2005 Standard*(Publisher/Distributor)* to SQL 2005 Express*(subscriber)* Replication Topology. I deleted(thru SSMS) a table and added a row to a different table. I then ReInitialized All with a Generate New Snapshot option and then ran my Subscribers Sync() Procedure.

I now get the below errors and I have no flippin idea what I should do about them...

Error messages:
The schema script 'tblCaseNotes_3.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Could not drop object 'dbo.tblCaseNotes' because it is referenced by a FOREIGN KEY constraint. (Source: MSSQLServer, Error number: 3726)
Get help: http://help/3726

tblCaseNotes is the Primary table so I don't know why it is trying to drop it...

Improve this question

1 Answer 1

Reset to default
2

When you push out a new snapshot to the subscribers, default practice is to drop all the tables in the snapshot and recreate them.

As the table that you dropped on the publisher was probably related to the tblCaseNotes table you'll need to manually drop that table on all your subscribers. Then the snapshot will be able to be loaded.

Improve this answer
4
  • Manually?!?! That will take for ever. That is amazing that there isn't a more automated and sophisticated method. How do BIG business' make schema changes?
    – Refracted Paladin
    Aug 21, 2009 at 1:07
  • 1
    Normally tables that are being replicated don't get dropped so it isn't an issue. All you need to do is delete that one table. If that may be an issue, edit the tblCaseNotes_3.sch file in notepad (it's just a SQL SCript) and put in code to remove the foreign key constraint (if it exists) and then the table (if it exists). The IF EXISTS blocks are critical as otherwise the script will fail if the objects are already missing.
    – mrdenny
    Aug 21, 2009 at 3:15
  • When you say manually drop, you mean the same one I dropped on the publisher, not tblCaseNotes which I do not want dropped, correct?
    – Refracted Paladin
    Aug 21, 2009 at 13:34
  • Correct, the table that you dropped on the Publisher would need to be dropped on the subscriber, either manually (via SSMS) or by editing the scripts in the snapshot folder.
    – mrdenny
    Aug 21, 2009 at 21:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .