reindexing error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

reindexing error

Author

Topic

aliddell
Starting Member

6 Posts

Posted - 2002-08-21 : 00:01:15

I'm was receiving error logs in a maintenance plan when an attempt was made to re-index a database.

The error is
Server: Msg 169, Level 15, state 2, line 2
A column has been specified more than once in the order by list....

On doing a manual re-index of the database i narrowed it down to a particluar table. I removed indexes and checked any depenant objects... can't find anything that has an order by. Still get the same error msg on a re-index or update stats ???

It's a very basic table 10 columns.. also happens to be a core table so having the indexes and stats correct is vital.

Any ideas ?

Andrew

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-08-21 : 02:39:25

paste your table definition.

Peace

Rick

aliddell
Starting Member

6 Posts

Posted - 2002-08-21 : 03:22:55

Table definition is below. The problem only seems to be occurring on the production database, the dev table doesn't have the same issues. Both are sql 2000 databases, previously restored from sql7.

Andrew

CREATE TABLE [dbo].[tblProducts] (
[ProductCode] [int] NOT NULL ,
[ProductName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SectorCode] [int] NULL ,
[TaxCode] [int] NULL ,
[InvestorSizeCode] [int] NULL ,
[APIRCode] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpecialistStyleCode] [int] NULL ,
[SubSectorCode] [int] NULL
) ON [PRIMARY]

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-21 : 04:46:15

What do you get from

DBCC CHECKTABLE ('tblProducts') WITH ALL_ERRORMSGS

HTH
Jasper Smith

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-08-21 : 05:01:03

Do remember that another word for 'order by'....is COLLATE....

You have 2 columns with a COLLATE constraint/attribute.....it may be the source of your problem.

aliddell
Starting Member

6 Posts

Posted - 2002-08-25 : 22:37:31

Checktable doesn't appear to return any problems, just the row & page count.

Checking the collation theory

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources