none
Cannot drop the table ... because it is being used for replication. (Microsoft SQL Server, Error: 3724) RRS feed

  • Question

  •  

    Hi, it is little different to other problemas with the same title.

     

    (SQL2005)

    My problem is that i romoved Replication in the server, but the tables who used to be part of one article, cant be droped or changed. It server was used like source (distribuitor not suscriber and there is other like coordinator)... i search (SSMS) "visually" and I found nothing about replication... my questions is about, what system tables should i look, maybe it wasn't clean with the remove replication and i have to do by my self.

     

    I found this in web from Microsoft http://support.microsoft.com/kb/326352 but it doesn't work... any other advise?

     

    Thanks

    Friday, September 19, 2008 2:49 PM

Answers

All replies

  • How did you originally remove replication?

    sp_removedbreplication doesn't work when executed in the user database?

    Friday, September 19, 2008 4:59 PM
    Moderator
  • It was made from the coordinator server SQL2005, with SSMS, i did not use a stored procedure directly.

     

    All the examples in web, talk about modify system tables, but in SQL 2005 it can't be done...

     

    what can i do?

    Friday, September 19, 2008 5:12 PM
  • i was asking about sp_removedbreplication, did that not work?

    Friday, September 19, 2008 5:16 PM
    Moderator
  • Ok.

     

    I Executed that stored procedure but i don't have changes... i can't change or drop the table. 

    Note: until now, i'm not restarting the SQL service... i will do that and comments if there is any change.

     

    thanks

     

    Friday, September 19, 2008 5:30 PM
  • When you originally removed replication, did it complete successfuly?  or did it time out, get cancelled, disrupted, etc.?  Are there other replication objects or tables left behind?  like tables syspublications or sysmergepublications?

     

    For the object that you cannot drop, can you return the results for the below query:

     

    select is_published from sys.objects where name = '<object name>'

    select category from sysobjects where name = '<object name>'

     

    Friday, September 19, 2008 7:17 PM
    Moderator
  • yes. it finish complete without any error, it was made from coordinator server.

     

    the result from your querys are:

    is_published

    ------------

    0

     

    category

    -----------

    0

     

    Friday, September 19, 2008 7:58 PM
  • can you post the exact error you're getting?

     

     

     

    Friday, September 19, 2008 8:15 PM
    Moderator
  • what a need to do is modify the type of a column in a table from char(16) to varchar(50), and when i go to right click on the object and select design option here are the results:

     

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Drop failed for Table 'dbo.TiCatUbicacion'.  (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Table&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot drop the table 'dbo.TiCatUbicacion' because it is being used for replication. (Microsoft SQL Server, Error: 3724)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3033&EvtSrc=MSSQLServer&EvtID=3724&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Friday, September 19, 2008 9:00 PM
  • can you run profiler to trace this action to see what SQL statement is failing?  It usually has to do with is_published or category column, but you said it returned 0, so i'm not exactly sure where it's failing.
    Friday, September 19, 2008 9:38 PM
    Moderator
  •  

    Hi Greg, I follow your indications and found something:

     

    This command:

     

    select @published = is_merge_published | is_published from sys.tables where object_id = @objid_tmp

     

     

    If I execute in SSMS with my object table to drop:

     

    select is_merge_published, is_published, *

    from sys.tables

    where object_id = object_id('TiCatUbicacion')

     

    is_merge_published is_published

    ------------------               ------------

    1                               0

    (1 row(s) affected)

     

    Now, how can I change this values to 0? (both)

     

    Thanks

    Monday, September 22, 2008 3:03 PM
  • Try creating a test publication on the database and then drop it (use sp_removedbreplication proc).
    What might have happened is replication is not completed dropped from the database.
    Monday, September 22, 2008 5:30 PM
  • Thanks qaz_wsxd, but i found better solution... for all the people with the same problem...

     

    sp_MSunmarkreplinfo which takes a tablename as a parameter

     

    Here the source solution:

    http://www.dbtalk.net/microsoft-public-sqlserver-replication/orphaned-entries-sys-objects-after-286901.html

     

    And it cleans the flag, now i can change or drop it.

     

    Thanks

    Monday, September 22, 2008 11:25 PM
  • Even this did not work for me.

    Finally I found the solution on

    http://bloggingabout.net/blogs/dennis/archive/2007/08/03/cannot-drop-database-because-of-replication.aspx 

    Simple, create another DB with the same name on another machine and force restore it on the DB having the problem

    Thursday, October 16, 2008 1:29 AM
  • This actually WORKED for me.   sp_removedbreplication 'dbname'  That is mentioned in that support article.  http://support.microsoft.com/kb/326352
    • Proposed as answer by Parker Smart Thursday, June 21, 2012 9:43 PM
    Thursday, June 21, 2012 9:43 PM
  • sp_removedbreplication 'dbname' works for me.

    Thank You,

    Monday, January 4, 2016 12:09 AM
  • Worth noting that sp_Msunmarkreplinfo also has a @owner (schema) parameter - so if you have the object name twice but under different schemas you will need to specify the owner aswell.  and there is a third parameter @type smallint
    Tuesday, February 23, 2016 1:45 PM