Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '09 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 '09 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 '09 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 '09 at 21:09
add comment

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.