December 10, 2001 at 10:10 am
My DB Maintenance Plan has just started failing and I'm at a loss in trying to find out why. It fails on the "Rebuild Indexes" portion of it with Error Number: 169 and an Error Message of: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169:
[Microsoft][ODBC SQL Server Driver][SQL Server] A column has been specified more than once in the order by list. Columns in the order by list must be unique.
While I think I understand what the error is telling me, I'm unable (to date) to find the column(s) that are duplicated. Any suggestions?
Thanks for any help.
December 10, 2001 at 10:28 am
Not sure of the error. Can you recreate the maintenance plan. I've had issues with these things failing and not really providing good error messages.
Steve Jones
December 10, 2001 at 10:32 am
I've deleted the Maintenance Plan and created a new one and it generates the same error. So I'm pretty confident that it's truly the index that is the problem. It's just WHICH index is the problem? We've been adding indexes pretty aggressively lately and it's likely that one or more columns could have been duplicated. I'd just love to be able to find out which table carries the problem index.
Thanks for the input.
December 10, 2001 at 11:10 am
The only thing I can think of is to script all indexes and then clean the results or write a quick parser to get the table name and columns. Then load this into a temp table and query it.
Steve Jones
December 10, 2001 at 11:14 am
I thought of that, but was hoping there was a better way. But I'm with you, that might be the best option. Thanks again!
December 10, 2001 at 11:19 am
Good luck. If you build the script or process, fell free to post it. I might work on it if I have a chance.
Steve Jones
December 10, 2001 at 12:16 pm
I scripted out the indexes and did a Search and replace in QA to get down to
"INDEX [Addr_NDX1] ON [dbo].[Addr]([CityID], [StateID])"
I then Loaded this into a table and cleared it down to the columns index, table, col, using replace, charindex, and substring functions. Took about 15 minutes, but not that clean. I then used a quick cursor to "bend" the table and place each column on a separate line.
If you want my notes, email me at way0utwest@hotmail.com. Not that clean, but it worked.
Steve Jones
December 10, 2001 at 12:23 pm
Thank you VERY much. I'll take you up on that. I am going to dive into it more deeply this evening, but your help is greatly appreciated. Thanks again!
December 11, 2001 at 10:18 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply