More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Unable to drop a table even after removing replication. Getting the following errors, when try to drop or alter the table...
Q: Unable to drop a table even after removing replication. Getting the following errors, when try to drop or alter the table:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 't1' because it is published for replication.
Server: Msg 4931, Level 16, State 1, Line 1
Cannot add columns to table 't1' because it is being published for merge replication.
Is there a way out?
A: To avoid this situation, you should 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)
(Continued on next question...)
