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