Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cannot drop view as it is being used by replication


Cannot drop view as it is being used by replication

Author
Message
Iain Wilson
Iain Wilson
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
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


Carolyn Richardson
Carolyn Richardson
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1553 Visits: 3445

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
Iain Wilson
Iain Wilson
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 11
Thanks. I'll give that a try!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search