October 22, 2004 at 10:15 am
"Cannot drop table because it being used for replication. "
Am replicating between SQL 2003 servers. Server A is Publisher and Distributor. Server B is Subscriber.
I've turned the replication off on Server A. I've deleted all of the jobs associated with the replication, and even disabled the Publisher from Distributing anything. Have deleted all of the Articles and Publications, and stopped Subscription, yet the table on Server B persists.
How can I delete this table (which I need to rebuild) and begin replication again?
Thanks!
October 22, 2004 at 11:46 am
I don't know if it works the same for SQL 2003? however I had this issue with merge replication in 7.0 and I found the following information which was helpful to me...
Removing Replication
always remove replication in the following order:
- Delete the subscriptions
- Delete the publications
- Disable publishing
Deleting publications without unsubscribing or disabling publishing without deleting publications results in this situation.
Anyways, if you are seeing the above mentioned errors, try any or all of the below workarounds one after another in the order described below.
1. EXEC sp_removedbreplication 'your_database_name'
2. Execute the following procedure on the problematic database and pass the table name as the parameter:
EXEC sp_msunmarkreplinfo 'your_table_name'
3. You may not need this method, but take this as a last resort.
EXEC sp_configure 'allow',1
go
reconfigure with override
go
use your_database_name
go
update sysobjects set replinfo = 0 where name = 'your_table_name'
go
EXEC sp_configure 'allow',0
go
reconfigure with override
go
NOTE: If you don't follow the proper replication removal order, you may see the same problem with replicated stored procedures!
(Same problem can occur with replicated stored procedures)
October 27, 2004 at 2:09 am
Sounds like your in trouble. I had my own experiences with removing repications and sometimes it happens that not all the information is removed from the system tables. Especially if you just delte the jobs, articles etc.
Unfortunately you don't write what type of replication you where using, so I can't tell which entries in the system tables need to be cleaned. But first look in the distribution database in MS Publications, MSSubscription, MS Subscriber Info etc. Where are actually quite a lot of tables which can contain replication related entries. Also if you where using merge replication check if the triggers are still on the table, any stored procedures and views used by replication.
It's quite hard work if you do it manually, but in case of merge replication I can send you a script which does most of the job.
M
[font="Verdana"]Markus Bohse[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply