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?
asked Jun 18 '10 at 01:38 PM in Default
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.
answered Jun 18 '10 at 02:26 PM