Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cannot drop view as it is being used by replication Expand / Collapse
Author
Message
Posted Monday, January 16, 2006 2:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 08, 2009 10:59 AM
Points: 44, Visits: 11

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

Post #250886
Posted Tuesday, January 17, 2006 2:46 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 11, 2014 9:31 AM
Points: 1,451, Visits: 2,908

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

 



Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists
Post #251171
Posted Tuesday, January 17, 2006 2:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 08, 2009 10:59 AM
Points: 44, Visits: 11
Thanks.  I'll give that a try!
Post #251174
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse