Add a clustered index in a published table

Hi,
I am using SQL 2K standard and have created a transactional replication
between two sql servers using push subscritpion. It is working fine. Now I
want to add a clustered index in one of the published tables and I get an
error message saying that "Cannot alter the table because it is being
published for replication". Based on this message, I assume I will have to
disable all publishing and distribution, add the clustered index and then
redo the publishing and subscriptions for all the tables from scratch again.
Am I right? Is there another way to do this?
Any help on this will be very much appreciated.
Wingman
Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
are you doing anything else in the same batch eg changing the PK?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick response.
Here are the steps I did.
1. Open the existing publishing database in the SQL enterprise manager.
2. Select 'Design' of the table which is a published table in the
publication.
3. Select the primiary key
4. Check the box of the clustered index.
5. Close the design window and click 'save' to save the changes.
6. Then the error occurs.
There are no other batches involved and I was just doing the above. I hope
this clarifies any questions for you.
Wing
"Paul Ibison" wrote:

> Interesting. 'Create Index' isn't really an 'Alter Table' so I'm wondering -
> are you doing anything else in the same batch eg changing the PK?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I can't test this right now, but I'd try to run this command:
CREATE CLUSTERED INDEX index_name ON table ( column_name )
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yep, I did and it ran succussfully without any problem. The clustered index
is created. Thanks for this suggestion.
Can you explain why I can't do this in EM? Is it just the limitation of
using this type of interface?
Also, do I need to do the 'create clustered index...' in the subscription
server or the push replication will take care of it? In the future, if I
need to add non-clustered index, will this be the way to do it?
Wingman
"Paul Ibison" wrote:

> I can't test this right now, but I'd try to run this command:
> CREATE CLUSTERED INDEX index_name ON table ( column_name )
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||If you run profiler when applying changes to a table using the Enterprise
Manager interface you can usually determine the cause of tis type of problem.
In this case the table was probably being dropped in the background. The same
issue applies in SQL Server 2005 where if you select to replicate DDL
changes, the Management Studio will fail whereas ALTER TABLE will succeed.
If you want this applied to the subscribers you have to apply initiate this
yourself. You could use sp_addscriptexec to use the replication setup or just
apply it manually.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry, don't mean to keep the discussion going that long but I have a few
follow-up questions regarding sp_addscriptexec.
1. Are there any benefits and differences using sp_addscriptexec compared
to manually executing the script in the subscriber computer?
2. If I use the sp_addscriptexec command to move the scritpt to the
subscriber(s), when or under what situation will the script be excecuted and
how often?
3. If I decide that I no longer need those scripts after using
sp_addscriptexec, how do I remove it from the subscriber computer?
Wingman
"Paul Ibison" wrote:

> If you run profiler when applying changes to a table using the Enterprise
> Manager interface you can usually determine the cause of tis type of problem.
> In this case the table was probably being dropped in the background. The same
> issue applies in SQL Server 2005 where if you select to replicate DDL
> changes, the Management Studio will fail whereas ALTER TABLE will succeed.
> If you want this applied to the subscribers you have to apply initiate this
> yourself. You could use sp_addscriptexec to use the replication setup or just
> apply it manually.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||There are no extra benefits of sp_addscriptexec in terms of running the
script. However, it may be the case that you have dozens of subscribers, or
perhaps some are offline. In this was it gives you convenience and assures
you of getting the script to the subscribers. They'll receive the script when
they synchronize, and the script won't be run more than once and won't be
retained on the subscriber's computer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok, thanks.
When I go to the subscription computer and check all replicated tables, I
notice that none of the primary key is defined but the index key for the
primary key is there. Why is it?
Also, when I checked the snapshot properties of the pubished article
defaults in EM, the non-clustered index check box is grayed out. Is there a
way I can activate it so I can uncheck it because I don't want the
non-clustered index to be replicated?
Wingman
"Paul Ibison" wrote:

> There are no extra benefits of sp_addscriptexec in terms of running the
> script. However, it may be the case that you have dozens of subscribers, or
> perhaps some are offline. In this was it gives you convenience and assures
> you of getting the script to the subscribers. They'll receive the script when
> they synchronize, and the script won't be run more than once and won't be
> retained on the subscriber's computer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||PKs are replicated as unique indexes by default in transactional replication.
If you enable the DRI option checkbox on the snapshot tab of the article
properties, the PK will be recreated on teh subscriber. For the chackboxes on
this tab, some are related to others, so try checking/unchecking others to
see if you can enable the nonclustered option. However not all combinations
are supported using this method and in some cases you have to resort to
scripting the replication instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)