SQL Server 2008 Enterprise on Windows Server 2008 Enterprise Cluster that is used to support SharePoint 2007 and 2010. We have a weekly index rebuild job that first checks the index fragmentation, and if there is greater than 10% fragmentation then the indexes are rebuilt, otherwise the index is left alone.

The job has been failing the past three runs with the only error being reported is:

Indexing in Database: SPSearchDBName [SQLSTATE 01000]
___________________________________ [SQLSTATE 01000]
Re indexing for tables not needed in this Database. Fragmentation levels ok. [SQLSTATE     01000]
Msg 208, Sev 16, State 1, Line 2 : Invalid object name '#work_to'. [SQLSTATE 42S02]
Msg 0, Sev 16, State 1, Line 230 :  [SQLSTATE 01000]

The invalid object name is just a fragment of the valid object name '#work_to_do'. How would the proc drop part of the temporary table name randomly? Some indexes are rebuilt successfully so the temp table works partially and it is not a specific database or table the job fails on. Any help on this is very much appreciated!


I guess you are feeding the temp table into a cursor and then building a string to execute as a command? If so I suspect there may be a table or index with a space in the name. You may want to try

set @cmd = 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName etc...

If that's not what you're doing you may need to post the sp definition.


  • Just re-read your question and think this may not be relevant. Still leaving up just in case. – Ally Reilly Aug 16 '10 at 11:27
  • You are absolutely correct that we are feeding the temp table in to a cursor to build a string and execute a command. Great job! I have not had time to check out your suggestion and will update once I do. Thanks! – Ddono25 Aug 17 '10 at 17:15
  • How did you get on with this? – Ally Reilly Aug 24 '10 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.