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.
