Cannot drop table because it is being used for replication
Posted on 2011-09-27
WINDOWS SERVER 2008
I have a database that gets updated every night with new data that I import from an external server. This database is also replicated each hour to another database which is used by a website.
More detailed ... at 3am each morning I import the new data, drop the existing tables and INSERT INTO a new table, then create new indexes and constraints. From 3:30am - 6:30 pm, I publish these new tables each hour ... my website database is the subscriber.
Since I set up the replication, I get the error that I cannot drop the table because it is being used with replication. I am aware that I can rather delete the table contents (rather than drop the table) and insert the new data, but I prefer to drop the tables because it uses much less resources.
MY QUESTION: Is there a stored procedure or another method that will allow me to simply disable the replication, run my TSQL code that drops and recreates the tables, and then re-enable the replication ... or do I have to actually drop the replication, run my TSQL, then recreate the replication (using TSQL).
Any advice would be very appreciated!