SP's, Temp tables and ShowPlan_All

[wooddsw]

I'm attempting to troubleshoot a stored procedure performance problem that utilizes the creation of temp tables and nested user defined functions to fill the tables. The procedure runs fine although long. I ran the following to attempt to troubleshoot the performance problem:

SET SHOWPLAN_ALL ON
GO
EXEC g_FinancialReport 'bscomp','00002','200412'

When executing I now get the following error message and no showplan output:

Server: Msg 208, Level 16, State 1, Procedure g_FinancialReport, Line 263
Invalid object name '#tbl_Report'.

Has anyone encounter this conflict and know a work around or know of any link that would document that SHOWPLAN analysis won't work with temp tables?

Any direction would be greatly appreciated.

 

[vongrunt]

AFAIK this is well known limitation. Check this with QA:

create table #blah( id int )
insert into #blah (id) values (1)

... then hit Ctrl-L (show exec plan).