Post reply

Rename table causes conflict

  • August 17, 2005 at 9:13 am

    #94595

    I set up a transactional replication recently.  Several tables were renamed before the the replication started using Enterprise Manager. I set up replication using the renamed table names.  They are replicated successfully. However, conflict tables with the old table names show up on both the publisher and subscriber in Enterprise Manager. So both the renamed table names and the conflict table names show up in Enterprise manager. As far as I know, this is not inhibiting performance.  But I'd sure like to get rid of the conflict issue. How do I accomplish this?

     

  • August 17, 2005 at 3:05 pm

    #582882

    did you use sp_rename for that?

    if yes did those tables had triggers on them ?

    if yes fix the triggers

     


    * Noel

  • August 17, 2005 at 3:10 pm

    #582883

    Q.  did you use sp_rename for that?

    A. No. We used Enterprise Manager.

    Q. ....did the tables have triggers in them?

    A. No.

  • August 18, 2005 at 5:00 am

    #582962

    The conflict tables were created by merge replication or if you are using updateable subscribers.

    If you are no longer using these replication types you can drop these conflict tables using drop table conflictXXXXXXXXXXXXXXX

    --

    Hilary Cotter

    Looking for a SQL Server replication book?

    http://www.nwsu.com/0974973602.html

    Looking for a FAQ on Indexing Services/SQL FTS

    http://www.indexserverfaq.com

  • August 18, 2005 at 5:25 pm

    #583372

    Try recreate table by delete table and create new table with same name.

    sometimes or.. Rename doesn't rename table name in sysobject

  • August 19, 2005 at 6:56 am

    #583490

    Following Hilary's advice, I was able to drop the conflict tables in query analyzer. I was not able to delete the tables in Enterprise Manager.  Thank you!

    After my original post, I think I found the source of the conflict tables. At one point, I tried to replicate some out-of-date views that might have referenced the tables with the old names. Even though I killed that replication and started a new replication  without the views, the conflict tables did not disappear.  Dropping the conflict tables as Hilary suggested cleaned up the problem.  So far.

  • August 19, 2005 at 9:30 am

    #583587

    OOPs. Another issue that I think is related to the original problem. The next two snapshots complete with the error:

    sp_MSmakeconflicttable(debug): no unique index for cft table

    Any thoughts?

  • August 20, 2005 at 6:21 am

    #583747

    I'm becoming very frustrated. I deleted the subscription, deleted the publication, started all over. I set up the intial replication and it worked fine except for the recreation of the conflict tables again with the old table names. After I dropped the conflict tables on both the publisher and the subscriber and started the snapshot again. It failed to run again with the error "sp_MSmakeconflicttable(debug): no unique index for cft table"

    The old table names must persist somewhere. How do I get rid of them?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply