x

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?

more ▼

asked Jun 18, 2010 at 01:38 PM in Default

Tom Menacher gravatar image

Tom Menacher
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jun 18, 2010 at 02:26 PM

Tom Menacher gravatar image

Tom Menacher
1 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x84

asked: Jun 18, 2010 at 01:38 PM

Seen: 2161 times

Last Updated: Jun 18, 2010 at 01:38 PM