Borrado de suscripciones de inserción

a ver si te sirve este articulo:

Manual Replication Cleanup
By Michael R. Hotek, mssqlserver.com
One of the more common problems you will have to encounter at some point is
when you disable replication, not all of the settings are cleaned up. This
can cause problems if you want to drop a database or alter table structures.

As you might have figured out, the reason this tip even exists is because
the wizards to disable replication are not perfect. The good news is that
there isn't anything magical about replication. Replication is run entirely
via a set of applications that are external to SQL Server. These
applications are executed by the SQL Server Agent, Windows Synchronization
Manager, or directly via some other means. The same functionality is
performed when using the replication ActiveX controls. When executed, the
replication agents or the ActiveX controls connect to the appropriate SQL
Server and utilize data stored there to determine what to do. All you have
to do is eliminate all of that data and turn off any associated settings.

If you don't take anything else away from this article, remember the
following fact. If you can perform an operation in a pretty, little GUI,
then you can perform that same operation by typing in and executing the
commands. Too many people have grown accustomed to the "point and click"
mentality of the increasingly powerful GUIs. What they fail to remember is
that these GUIs do nothing more than execute a set of commands that you
could type yourself if you knew what they were.

Technically speaking, when you configure publishing, you do absolutely
nothing to the SQL Server. Bound up within that GUI is where you create
settings for publishers, distributors, and subscribers. It is these portions
of the GUI that actually have commands that get executed behind them.

When you configure a database as a publisher, what are you doing? You are
simply setting a database option. This option is either "published", "merge
publish", or both depending upon the replication method you specified. If
you issue either of the following commands, you accomplish exactly what the
GUI is doing in that portion.

exec sp_dboption 'pubs','published',true

exec sp_dboption 'pubs','merge publish',true

It is this setting that has a decision point within the core SQL Server
code. If either of the options are turned on for publication, then you will
not be allowed to drop that database.

When you specify a distribution database in the GUI, it simply executes the
appropriate create database commands. The final step of the configure
publishing GUI adds a set of system tables to track the replication setup.

When you create a publication, entries are written to these tables
describing the objects you have published, any partitions, and any custom
settings for each article. It also modifies a flag in sysobjects indicating
that an object is participating in replication. This column is called
replinfo and is set to a value of 128. Any objects that have a value of 128
are participating in replication and those with a 0 are not. This flag is
the other decision point in the core SQL Server code that will prevent an
alter/drop statement from executing against a published object.

When the snapshot agent runs for the first time, it will add a whole slew of
objects to your database. These are any combination of one or more of the
following:

a.. triggers
b.. views
c.. conflict tables
d.. system stored procedures
e.. user stored procedures
f.. columns to your published tables
When you subscribe to a publication, you are also simply turning on a
database setting. In this case, it is subscribed. The snapshot is then
applied and you get all of the schema elements necessary to make replication
functions such as metadata tables, triggers, views, procedures, etc.

So you now have a replication environment setup and running. You then come
back and decide to remove it, but something goes wrong and you leave all
kinds of stuff behind. Note: Even if the wizard runs perfectly, it will
still leave elements behind.

The most common items that are left behind are:

database options

replication objects

sysobject settings

rowguid column

conflict tables

distribution database

To remove the database options, you simply execute sp_dboption for
published, merge publish, or subscribed, and set it to false. Removing the
sysobject settings is more involved in that you have to turn on allow
updates, update the replinfo column and set it to 0 where it is 128, and
then turn off allow updates. At this point, you should have a fully
functional database that allows you to alter/drop objects and drop
databases. You could stop here without having any side effects. But, we
might as well take everything out. You get rid of the rowguid column, if it
exists, by altering the table and dropping the column. Conflict tables are
simply user tables and can be removed with a simple drop command. The same
goes for any triggers, views, or procedures that replication created.

At this point, you should be able to drop the distribution database.

I STRONGLY recommend stopping there and not touching any of the replication
system tables or system stored procedures that were created when you setup
replication. They do not have any effect on your environment, but you never
know if something got left behind in the bowels of the SQL Server code that
will cause something to blow up if you remove these. Leave the system
objects intact on the machines and just ignore them.

You are now back to a clean system that functions just like it did before
you setup and ran replication across it.