Gaurav,

Subhash's solution will eliminate the error during reindexing.
However, dropping the flight_id column from air_tix_flight4 may
adversely affect the application(s) using that index. Without knowing
these applications, and to what use the data will be put, I cannot give
sound advise on whether or not to drop a column or index.

Since there are only two indexes on this table I would recommend
reindexing each one separately.

S. Davey (6/29)


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


>>> "Subhash Chandra "
<mssqldba-ezmlmshield-x25908480.[Email address protected] 06/29/07
9:07 AM >>>
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



---------------------------------------------------------------------
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