question

Tom Menacher avatar image
Tom Menacher asked

cannot drop index on replicated table

We have a replicated table (publish/subscription). We changed a field on the publisher using to add a few bytes in length.

We used sp_repladdcolumn to add a new column with the requisite length (call it column 2). Then we copied all the data from our column 1 into the new column 2. Then we dropped the the column 1. At which point the server complained: can't drop a column which is indexed. So we dropped the index and then dropped column 1 (using sp_repldropcolumn). Then we used sp_repladd column to add a new column with the same name as the original column 1. Then copied the data back into it from column 2 and finally dropped column 2.

However, the replication failed to put this table out to the subscribing server. The error seems to indicate that there is an index on good old column 1 on that server, and we are stuck. I should have dropped the index on the remote server prior to letting the replication process proceed. Now, I cannot drop the index (it times out), and replication will not proceed.

In Oracle you can FORCE the database to drop an index. Any solution in SQL server?

indexes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Tom Menacher avatar image
Tom Menacher answered

Well, we finally decided that the remote location had not entered anything in the table lately, so we weren't concerned with synchronization.

We used sp_reinitmergesubscription on the publication, and simply forced the re-initialization of the table. The table exists on the remote server, and the indexes are correct, and we are back up and running.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.