January 16, 2006 at 2:22 am
Hi all,
At the weekend I had to stop replication on a server (it has a pull subscription to it) and then recreate it. Before deleting it I saved off a script of the old publication and used that to recreate it when I was ready to reinstate the replication.
However, when the snapshot completes the distribution agent fires up a message saying "Cannot drop the view <name> because it is being used for replication".
Puzzled by this, I altered the properties of the publication and set it NOT to drop that view if it existed. I re-ran the snap shot and it appeared to still have that error.
However, investigating the agent history it seems that the last time the distribution agent ran was on Saturday (when it threw up the original error) and despite me imploring it to run again (right clicking and telling it to start) it won't and simply sits there with the "Cannot drop the view..." message.
Has anyone seen anything similar to this as I'm tearing out what little remains of my hair!
Thanks,
Iain
January 17, 2006 at 2:46 am
Been there got the T-Shirt.
Script out the delete for the replication and extract the lines that refer to the view. Drop the view from being replicated. Then you can get the replication going, then add the view into the properties to replicate after replication is going to add it in again.
Personally I have had so much trouble with replicating stored procs and sometimes views that I now take the stand that I will not try to replicate them as they are easily recreatable and change infrequently as long as you keep a copy of the code, why copy them over every time you snapshot it just adds overhead, why not just apply the changes to views and SP's to all publishers and subscribers manually as and when needed.
I think SQL Server tries to validate the views and SP's and if it finds it cannot validate it for some reason it errors. I suppose its posible that the replication for the view is taking place before a table that has changed so it cannot validate the view and gives you this error, that's what was happening in my scenario anyway. If this is so then another solution could be that you could alter the script so that the replication of this view happens at the end, after all the table replication.
Regards Carolyn
January 17, 2006 at 2:50 am
Thanks. I'll give that a try!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply