>Hi, I'm still trying to find a best solution to this. I have this simplified query that works OK.
>

>set @SQL = 
>'
>select * INTO #TEMP from ( 
>
>SELECT 5 AS myorder, 
>''AltModeParticipation'' as Label, 
>[Year],
>AltModeParticipation 
>FROM HistoricalSummary 
>WHERE CompanyName = @CompanyName
>) Table1 
>pivot (sum(AltModeParticipation) for year in (' + @Columns + ')) p5 '	
>	
>execute sp_ExecuteSQL @SQL, N'@CompanyName  nvarchar(65)', @CompanyName ;	

>
>However, when I try to access #TEMP with a followup select or insert I get an error that the object does not exist. Since it seems the original query executes OK, what happens to the tempory table? I assume what is happening is that the query is creating a temp connection that is closed after the execute and taking #TEMP with it. Does anyone know how I can hang onto it?
>
>Thanks

If you need access to the temporary table, the only way is to create a global temp table, e.g. ##temp. Otherwise that temp table exists only in that connection that executes dynamic SQL and you can not access it outside.