• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

Can't Remove Replication

I have a table in a database that I cannot modify.  I get this message:

Cannot alter the table 'CustNotes' because it is being published for replication.

It is in sql server 2005.  However the database compatibility is sql 2000 (80).

The replication was apparently set up years ago and is no longer valid.  There are no publications or subscriptions on the server.  I have researched this and found most sites recommend using sp_removedbreplication.

My question is ... will the sp_removedbreplication procedure delete my table?  ie.  can i run sp_removedbreplication without doing any harm?  I don't want to lose any of my tables or data as this is the production db.
0
hi2way
Asked:
hi2way
  • 5
  • 4
  • 2
2 Solutions
 
AnujCommented:
You have to drop all subscription and finally the publication itself. The steps vary depends on your type of replication and subscription.

Dropping the publication, subscription or replication never drops your table. sp_removedbreplication actually removes the replication related objects in the database.

Please refer the KB from Microsoft How to manually remove a replication in SQL Server 2000 or in SQL Server 2005.
0
 
TempDBACommented:
so, the replication exists in the server and there is no other servers subscribed to it. If it is the condition, you can easily drop the replication. If won't affect.
0
 
hi2wayAuthor Commented:
anujnb:  yes, I saw the documentation.  I just don't know what it means by "removes all replication related objects.  I want to make sure that does not include my tables.  Also, I have nothign to drop.  There are no visible signs of any existing subscription or publication and we have no other servers.  The old server is completely gone.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
hi2wayAuthor Commented:
TempDBA

When you say I can easily drop the replication, are you referring to sp_removedbreplication.  If not, please let me know how I can do it (without of course deleting my tables in the process).  I don't want to lose my data.
0
 
AnujCommented:
Replication related objects means, replication related system stored procedures, triggers, Constraints, and other system tables. This will not effect user related tables. So you are safe to run the command sp_removedbreplication.

0
 
TempDBACommented:
one quick question, why sp_removedbreplication? Don't you want your replication information to be removed from distribution database? I would suggest going through the general way first for every publication in the publication database:-
1. sp_dropsubscription
2. sp_droparticle
3. sp_droppublication

This will take care of removing the metadata from distribution database. You use sp_removedbreplication only when all other way of removing replication fails.
0
 
hi2wayAuthor Commented:
TempDBA,

I realize that would be the way to go.  The problem is those stored procedures require me to know more information.  For example, sp_droparticle requires me to know the publication name.  I don't know any subscriptions, publications or any other replication information.  These objects (what ever the names were) were all apparently removed years ago - before my time.  However, sql server must still thinks they exist.
0
 
TempDBACommented:
try the result from sysarticles and syspublications object in the publication database. Or you can get similar information from distribution database using

  distribution.dbo.MSArticles A WITH (NOLOCK)  
inner join  
 distribution.dbo.MSPublications P WITH (NOLOCK)  

0
 
hi2wayAuthor Commented:
I only have one database.  I ran the sql you provided - it had no records.  I then ran the following two results - both came up empty (no records):

select * from distribution.dbo.MSArticles
select * from distribution.dbo.MSPublications
0
 
TempDBACommented:
So, then you are all good. You can run the sp_removedbreplication.
0
 
hi2wayAuthor Commented:
Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now