0

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!

1 Answer 1

1

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.

Cheers

3
  • Just re-read your question and think this may not be relevant. Still leaving up just in case. Aug 16, 2010 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, 2010 at 17:15
  • How did you get on with this? Aug 24, 2010 at 11:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .