Results 1 to 14 of 14
  1. #1
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    Unanswered: sql server 2000 replication error

    The process could not drop one or more tables because the tables are being used by other publications.

    this is after the snap shots and publications have been created

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I need a few more breadcrumbs for this one...

    Are you trying to "republish" tables that are part of an existing subscription? If so, SQL 2000 does not support doing that, you'll have to either create a new subscription to the original publication or you'll have to go to the publishing database and create a new publication and subscription for your new subscriber database.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    the process could not drop one or more tables because the tables are being used

    Thanks Pat. What are the advantages of each option listed. I think I would want to "create a new subscription to the original publication"

    Would this delete the existing records on the database that I am trying to replicate?


    Here is the full scenario
    The master server with distribution, publishing ,etc got hosed up. I recreated a new server enable distribution, publishing, ett. Now I am trying to synch the data with an existing "slave server\subscriber" in hopes of preserving the data\getting it the master server.

    Everything gets created: snapshots, merge agents etc but then the error messages shows up: he process could not drop one or more tables because the tables are being used by other publications.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Uff-da! I would definitely NOT attempt a re-sync using the SQL 2000 replication tools. The tools are fine for doing tasks that they were designed to do, but re-sync is definitely NOT one of the things that those tools were designed to do!

    I would very strongly suggest that you do this re-sync/recovery job manually, and if you have any qualms about it then I would hire someone with more experience to do it for you. At the absolute minimum, before you start anything you need to make full backups... Failing to do that could cost you your data, which might cost you your job!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    Thanks for the response

    So how can you do the re-sync manually. Data is secured. Just trying to learn by trying this on my own.

    FJ

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is almost impossible to diagnose and/or repair this kind of problem remotely. Something like getting a phone call from someone above the arctic circle saying that "my computer died, can you help me fix it?". There are so many factors that I can't possibly know about your setup and problem that I really don't think that I can help you plan out the "grand scheme" of things, although I can help with specific problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    thanks

    Fair enough.

    one more question....do you think if you enable replication on a remote\slave\ server in, essence using it as the main\master server you can them add subscribers to it

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using SQL 2000 it isn't a "one step" process, but you can get there. If you peel off the existing replication, you can convert a slave into a new master.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    peel off the existing replication

    thanks.

    could you elaborate on peeling off the existing replication?

  10. #10
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    Thumbs up

    so basically this is what i did to solve the problem:
    1.bcp out all the tables from a good slave server
    2. copy all the Views from the good slave server
    3. on the old master server (the one where replication fail to work)
    a-unmark all tables published for replication
    b-drop and recreate the tables published for replication
    c-BCP in all the tables (with data) that was obtained from the most up to date slave server
    d-remark all tables published for replication
    e-put on the master server all the Views that was obtain from the up-to-date slave server
    f- tweek the system








    ps
    all the problem stemmed from dropping a table that was published for replication. even though you recreated it and bcp in fresh data, replication did not like this process and hosed up with that error message

  11. #11
    Join Date
    Aug 2010
    Posts
    1
    Here is a Microsoft support article on this known issue. Hope this helps.

    FIX: Merge Agent Fails During Synchronization of Rollup Subscriber

  12. #12
    Join Date
    May 2010
    Location
    nyc
    Posts
    8
    thanks. my problem is solved and that article did not help.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you give us some insight into your problem / solution? That might be very helpful for someone else with the same problem.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    May 2010
    Location
    nyc
    Posts
    8

    Exclamation

    Problem: replication stopped working; on our system we had 5 publications and the main one that synchronized the tables that were published for replication, failed.

    Error Message: The process could not drop one or more tables because the tables are being used by other publications. see post on 05-27-10 14:02


    This problem was evident soon after a table that was published for replication was unmarked, dropped and recreated, new table with new data was BCDed in, and table was remarked for replication


    The Challenge:
    Since the master server (where distribution was enabled) was replicating\ synchronizing with two other servers that were often offline and data was manipulated (edit\inserted\deleted) on a daily basis, all work on the servers had to cease so i can figure out which server had the most up-to-date data. Once identified, that server's tables would them be used for data manipulation.

    If the most up-to-date data was on the master, then it would not have been a problem to re-setup the master serve as a distributor, kill and reset up the DBs on the other two servers and push the data down to the two "slave"servers. However, since the most up-to-date data was on one of the "slave" server, then all the data had to come from that slave server.

    At first I tried just dropping the DB on the master server then attaching the db from the up-to-date server but that gave me the same error when i tried to push the data down to the "slave" server. I opted to copy only the tables since I knew the problem was not with the table data but replication itself.

    So this is what worked for me: see post on 07-30-10 11:35

    btw......."tweeking" the system in this case (step 5) meant dropping all subscriptions, disabling replication and re-enabling replication and all the other steps one will take to set up replication.

    I our case we would start with a blank database on the slave servers and when replication gets going it would initially create Merge Agents for each subscriber added, and data from the master server will get push down to the subscriber on it's maiden synchronization with the master server.

    Keep in mine we do disconnect\asynchronous replication at my org. Also i am not an expert in this field so i basically hacked my way to this solution and then had the application and data tested afterwards.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •