Create the second index is "air_tix_flight4", only on "tix_id".
It will not affect performance.

Hopefully your problem will solve.

Thanks & Regards,
Subhash

-----Original Message-----
From: Gaurav Kohli
[mailto:mssqldba-ezmlmshield-x88233121.[Email address protected]
Sent: Friday, June 29, 2007 6:14 PM
To: LazyDBA Discussion
Subject: RE: Reindexing Error

Thanks for your response Stephen,

The table is called "air_tix_flight". This has got two columns "tix_id"
and "flight_id", both integers. And this has got two indexes.

The first index is "air_tix_flight8", which is a clustered index on
"flight_id".
The second index is "air_tix_flight4", which is a non clustered index on
"flight_id" and "tix_id".

Both the indexes were created using the recommendations from the Index
Tuning Wizard.

The question here is that should I go ahead and drop the clustered index as
"flight_id" is already there in the non clustered one.

Thanks
Gaurav


-----Original Message-----
From: Stephen Davey
[mailto:mssqldba-ezmlmshield-x41005222.[Email address protected]
Sent: Friday, June 29, 2007 5:48 PM
To: LazyDBA Discussion
Subject: Re: Reindexing Error

Gaurav,

Sounds to me like the table you're trying to reindex has two indexes that
use the same column or columns in its definition. Check the index
definitions to verify that they all use unique columns. If there is a
duplicate index definition, and both are needed, you will probably have to
rebuild each index individually.

S. Davey (6/29)


MI DIT\Agency Services
7:30 A.M. - 4:00 P.M. ET
517.335.4237 (M- F CCC )


>>> "Gaurav Kohli "
<mssqldba-ezmlmshield-x3756793.[Email address protected] 06/29/07 3:18 AM
>>> Hi Guys,

I get the error:

"A column has been specified more than once in the order by list.
Columns in the order by list must be unique."

When I run the query:

DBCC DBREINDEX(Table_Name,' ',90)

We are on SQL Server 2000

Any Ideas?

Thanks
Gaurav Kohli



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html








---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page