Anyone who has ever dealt with Replication on MS SQL knows how much of a headache it can be. One area that is definitealy a pain in the rear is trying to remove replication. Using Enterprise Manager to drop replication is a 50/50 gamble. It may work, it may not. And if it works it may not have removed all signs of the replication job.
Over the course of about two years I have fought with our databases. Most problems that I have run into have been a result of replication. I am no longer using the replication features but I still feel the pains from it. Every now and then when I try to edit a table I am met with a message that I cannot Drop or Add a column because this table is marked for replication. There have been many hacks that I have had to perform manually in order to be able fix the problem.
My most recent problem occured when I tried to add a new field to a table and was met with the message that I could not altar the table because it is marked for replication. I had already in the past manually removed replication related triggers from my tables that caused problems and now I am besides myself as to what is the cause of this most recent problem.
After playing around in the sysobjects table I came acrossed the field replinfo. I noticed that all tables that had never been replicated had this value set to 0. But for tables that once were replicated the value was set to positive value. "This has got to be it" I thought to myself. I will simply edit the field and change it to 0. Easy right? Wrong! The SysObjects table is locked and can not be edited. Here we go again, another headache.
Well, actually, after a quick search on google I found my answer. You can change your settings to allow this table to be edited. Run the following to allow edits to system objects:
EXEC sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
After successfully running that you can now edit those fields. And I was able to do so. I modified all my tables that had a REPLINFO value greater than zero. And as luck would finally have it, I was finally able to add that field to my table. Unfortunately this is about the 5th different probably I have encountered with replication locking my database after I have removed replication. And I am sure it's not my last.
So if you run into a similar problem I hope the above helps you solve the problem.
Oh, by the way, don't forget to lockdown your system objects. You don't want to accidently modify/delete something important:
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE
GO
