-
タイトル
"Invalid object name" when generating a plan with a temp table in SQL PI for SQL Server -
説明
An estimated plan cannot be created in SQL PI for a SQL Server agent. A popup appears
An error occurred while trying to retrieve the data to this screen. If this issue persists, contact Quest Software Support.
In the details of the error, a message similar to that below is printed with TEMPTABLE representing a temporary table name.
Invalid object name '#TEMPTABLE'
A message similar to the following may appear in the SQL Server agent log file.
ERROR [RMI TCP Connection(444)-{IPADDRESS}] com.quest.qsi.fason.sqlserver.collection.processor.rmi.DBSSKillSessionProcessor - Failed to execute DBSSKillSessionProcessor
com.quest.qsi.fason.framework.connections.common.exceptions.FrameworkQueryException: Invalid object name '#TEMPTABLE' -
原因
When generating an estimated plan for a batch, all of the subsequent T-SQL statements are not executed, instead SQL Server returns the execution information for the statement (without executing it).
When the user tries to generate execution plan for a batch that have ad-hoc temp table (which does not exist) "SQL Server" (not foglight) failed to generate estimated plan with error “object not found“. In this case the temp table is ad-hoc, which does not exist until the whole batch is executed.
But when the user executes the batch in SQL Server Management Studio the temp table is then created and so there is no issue in generating estimated plan.
Note: Some queries that use temp tables may work because the temp tables were created by "Create table..." statement and would have remained in the database (unless cleaned up later).
Whereas in other batches, the temp table is not created by such a statement, rather its part of a Select Into statement. -
対策
Workaround
Reconfigure the SQL statement not to use a temporary table or try creating the temporary table explicitly.
Status
Enhancement ID FGSS-I-159 has been logged to generate an estimated plan in SQL PI for batches that use a temporary table. This will be reviewed by Product Management for consideration in a future release of the SQL Server cartridge.
-
Defect ID
FGSS-I-159