Index creation problem.

Dear all,
When I try to drop and recreate an index in my development server. I got
this error.
'Corp_billing' table
- Unable to create index 'PK_Corp_billing'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A col
umn has been
specified more than once in the order by list. Columns in the order by list
must be unique.
Besides this what are hypothetical indexes and can I delete them at
production server.
Kind regards
Khurram ShahzadAccording to the error message you are trying to create a primary key,
but you specify column's name more then once. Make sure that each
column is specified only once.
As for you second question - hypothetical index is created by the
index tuning wizard. If I remember correctly the server deletes those
indexes after a while.
Adi|||Adi,
I used the following syntax and I am also getting same error while dropping
through Enterprise Manager.
drop index Corp_billing.Corp_billing_idx
I created multiple indexes one column but they are in combination with other
coulumns.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122975887.001679.176620@.g44g2000cwa.googlegroups.com...
> According to the error message you are trying to create a primary key,
> but you specify column's name more then once. Make sure that each
> column is specified only once.
> As for you second question - hypothetical index is created by the
> index tuning wizard. If I remember correctly the server deletes those
> indexes after a while.
> Adi
>|||Can you post the create index (or drop index) statement that you are
trying to run? In the previous message you wrote about an error that
you get when you create an index. From this message I understand that
you have an error when you are trying to drop an existing index.
I don't think that you have a limitation on how many indexes can use a
column, but you can not use a column in the same index more then once.
Adi|||Command 1:
Drop index Corp_billing.corp_billing
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
Command 2:
CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
WITH FILLFACTOR = 90
Error:
Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in
the order by list must be unique.
"Adi" <adico@.clalit.org.il> wrote in message
news:1122977315.810889.247310@.g49g2000cwa.googlegroups.com...
> Can you post the create index (or drop index) statement that you are
> trying to run? In the previous message you wrote about an error that
> you get when you create an index. From this message I understand that
> you have an error when you are trying to drop an existing index.
> I don't think that you have a limitation on how many indexes can use a
> column, but you can not use a column in the same index more then once.
>
> Adi
>|||The hypothetical indexes never get deleted when they remain... Go ahead and
delete them..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Khurram Shahzad" <Khurram.Shahzad@.360training.com> wrote in message
news:Oh36TL0lFHA.3144@.TK2MSFTNGP12.phx.gbl...
> Dear all,
>
> When I try to drop and recreate an index in my development server. I got
> this error.
>
> 'Corp_billing' table
> - Unable to create index 'PK_Corp_billing'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]A c
olumn has
> been specified more than once in the order by list. Columns in the order
> by list must be unique.
>
> Besides this what are hypothetical indexes and can I delete them at
> production server.
>
> Kind regards
> Khurram Shahzad
>|||Khurram Shahzad <Khurram.Shahzad@.360training.com> wrote:[vbcol=seagreen]
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list.
> Columns in the order by list must be unique.
>
> "Adi" <adico@.clalit.org.il> wrote in message
> news:1122977315.810889.247310@.g49g2000cwa.googlegroups.com...
Do you maybe have some kind of trigger on that table that is responsible for
the similar looking error message?
robert|||Khurram Shahzad (Khurram.Shahzad@.360training.com) writes:
> Command 1:
> Drop index Corp_billing.corp_billing
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Line 2? Was there a blank line nefore the DROP INDEX statement or
what?

> Command 2:
> CREATE CLUSTERED INDEX Corp_billing ON Corp_billing (bill_id)
> WITH FILLFACTOR = 90
> Error:
> Server: Msg 169, Level 15, State 2, Line 2
> A column has been specified more than once in the order by list. Columns
> in the order by list must be unique.
Wait a minute, if you were not able to drop the index, how could you
then be able to run this command?
Could you post the CREATE TABLE statement for the table?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp