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 '10 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 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x79

asked: Jun 18 '10 at 01:38 PM

Seen: 1913 times

Last Updated: Jun 18 '10 at 01:38 PM