Home / server / Questions / 170320
Accepted
Ddono25
Asked: 2010-08-14 10:54:24 +0800 CST

Index rebuild stored procedure fails while calling temporary table

  • 772

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!

sql-server sql indexing stored-procedures

1 Answers

  1. Best Answer

    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

    • 1