Results 1 to 5 of 5

Thread: Replication - Cannot Drop Table

  1. #1
    Fred Towicz Guest

    Replication - Cannot Drop Table

    I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

    What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

    'tblProducts' table
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

    So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

    Does anyone know why? We are running SQL7 SP2.
    Email me on ftowicz@icontact.com.au

  2. #2
    Phil McCormack Guest

    Replication - Cannot Drop Table (reply)

    Sorry if this doesn't help, but does the replication user have access permissions to drop & re-create the table on the subscriber ?

    Have a look in the SQL install directory for scripts named like uninstrepl.sql and run it in Query analyzer. Also check system tables to see if the subscriber is still listed and enabled as a subscriber, (even though replicaton has been uninstalled)

    Some thoughts !!!

    ------------
    Fred Towicz at 3/15/01 10:47:29 PM

    I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

    What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

    'tblProducts' table
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

    So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

    Does anyone know why? We are running SQL7 SP2.
    Email me on ftowicz@icontact.com.au

  3. #3
    Lyle Allen Guest

    Replication - Cannot Drop Table (reply)




    ------------
    Phil McCormack at 3/16/01 3:35:11 AM
    Hmmm. I have ran into this problem when I did not break the link to the subscriber first. Try dropping the replication to the subscriber before you un-install replication. It leaves an entry in a table that indicates that the table is still published for replication(both ends) There is an article in Technet about how to clean-up a replication that was not un-installed correctly. Sorry, don't know the article number.

    Sorry if this doesn't help, but does the replication user have access permissions to drop & re-create the table on the subscriber ?

    Have a look in the SQL install directory for scripts named like uninstrepl.sql and run it in Query analyzer. Also check system tables to see if the subscriber is still listed and enabled as a subscriber, (even though replicaton has been uninstalled)

    Some thoughts !!!

    ------------
    Fred Towicz at 3/15/01 10:47:29 PM

    I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

    What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

    'tblProducts' table
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

    So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

    Does anyone know why? We are running SQL7 SP2.
    Email me on ftowicz@icontact.com.au

  4. #4
    Fred Towicz Guest

    Replication - Cannot Drop Table (reply)

    Hello Lyle

    Thank you for your response. I used the Wizard to disable the publication settings and distribution database. I thought the wizard would know to disable the subscriber first. I can't find the system table that I need to alter.... I searched the microsoft technet site for the article you suggested, but I couldn't find it.

    Can you help me?

    Cheers
    Fred

    ------------
    Lyle Allen at 3/16/01 4:32:51 PM




    ------------
    Phil McCormack at 3/16/01 3:35:11 AM
    Hmmm. I have ran into this problem when I did not break the link to the subscriber first. Try dropping the replication to the subscriber before you un-install replication. It leaves an entry in a table that indicates that the table is still published for replication(both ends) There is an article in Technet about how to clean-up a replication that was not un-installed correctly. Sorry, don't know the article number.

    Sorry if this doesn't help, but does the replication user have access permissions to drop & re-create the table on the subscriber ?

    Have a look in the SQL install directory for scripts named like uninstrepl.sql and run it in Query analyzer. Also check system tables to see if the subscriber is still listed and enabled as a subscriber, (even though replicaton has been uninstalled)

    Some thoughts !!!

    ------------
    Fred Towicz at 3/15/01 10:47:29 PM

    I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

    What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

    'tblProducts' table
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

    So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

    Does anyone know why? We are running SQL7 SP2.
    Email me on ftowicz@icontact.com.au

  5. #5
    mjt Guest

    Replication - Cannot Drop Table (reply)

    You have to re-publish the publisher and do a snapshot and merge - the whole nine yards. That is the only way to do it on SQL 7.

    SQL 2000 is now dynamic in a sense that you can change the structure without deleting and recreating the replication.

    MJT


    ------------
    Fred Towicz at 3/15/01 10:47:29 PM

    I need some help with Merge Replication. After successfully defining a publisher, distributor and subscriber to perform merge replication, we decided to test Merge Replication to see what we can and can't do.

    What we found is that you can't add new fields or change the nullable/null attributes of tables whilst the replication settings are still defined to the databases. ie you get the following error

    'tblProducts' table
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.tblProducts' because it is published for replication.

    So we thought, fair enough. We decided to uninstall replication using the wizards provided to see whether we can make schema changes. What we found is that we can make schema changes to the publisher database(on Server1), BUT not to the subscriber (Server2).

    Does anyone know why? We are running SQL7 SP2.
    Email me on ftowicz@icontact.com.au

Posting Permissions

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