We receive the following error using SQL 7: Cannot create
more than one clustered index on table 'Profile'. Drop the
existing clustered index 'PK__Profile__7B5B524B' before
creating another.

We're updating a field to a larger size, when we commit
the changes, we receive the above error.

Running a Trace, I seen that the commit drops the table,
creates the table (with fields and a PK), then creates a
clustered index. The problem lies with the create table
creating a primary key since it automatically creates the
clustered index. Is there a way to tell the DB not to add
this clustered index when running the create table command?

It's best to script this out. First, drop all of the nonclustered indexes.
Script them out first, if you don't already have the scripts. Next, alter
the table to drop the primary key. You will have to drop any foreign key
references to this table first. Next, alter the column to the desired size.
Then, add back the primary key. (Suggestion: Give it a real name.) Next,
add back the nonclustered indexes. Finally, add back the foreign keys.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote

We receive the following error using SQL 7: Cannot create
more than one clustered index on table 'Profile'. Drop the
existing clustered index 'PK__Profile__7B5B524B' before
creating another.

We're updating a field to a larger size, when we commit
the changes, we receive the above error.

Running a Trace, I seen that the commit drops the table,
creates the table (with fields and a PK), then creates a
clustered index. The problem lies with the create table
creating a primary key since it automatically creates the
clustered index. Is there a way to tell the DB not to add
this clustered index when running the create table command?

Hi Mike

This newsgroup is actually about creating a clustered installation of SQL
Server for fault tolerance purposes, and not really about clustered indexes.
This qustion might be better in the .server or .programming newsgroups, so I
have cc'd it to .server.

I presume you are using the Enterprise Manager to make this change, although
you didn't mention the tool you are using. The EM does a lot of things
behind the scenes that you don't always have control over. Using TSQL
throught the Query Analyzer, rather than point and click through EM, you can
request that a primary key have a nonclustered index. I would take the code
that trace shows you, and rewrite the CREATE TABLE to specify that the PK
should be nonclustered, and include the CREATE INDEX statement for the
clustered index. Then execute it through Query Analzyer.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Thanks for forwarding this.... I did find it there.

I am using EM to make the changes. Unfortuanately, I have to use the 3rd party software to make changes to the backend. The only way to know exactly what was taking place during this commit was using the Trace. Is there a way to tell SQL to not create a clustered index on a primary key while creating the table?

So you should have replied in that newsgroup instead of this one. This
newsgroup is NOT about clustered indexes. PLEASE post any replies only to
..server.

You'll need to be more specific about the current structure of the table. Do
you currently have a PK that is not a clustered index? What other indexes do
you have?

If you have access to EM, you should have access to other tools. Have you
tried finding Query Analzyer? The executable name is isqlw.exe, so try
running that. Or you can try the command line tool osql. Docs are in the
Books online. You can just type in your create table there.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"Mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote

what was taking place during this commit was using the Trace. Is there a way
to tell SQL to not create a clustered index on a primary key while creating
the table?