Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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