Cannot Create local temp table using sp_executesql

Does anyone know why this doesn't work (it doesn't give an error if you comment out the last line, but it doesn't seem to create the table either):

DECLARE @.SQL nvarchar(128)

SET @.SQL = N'CREATE TABLE #MyTempTable (ID INT)'

EXEC sp_executesql @.SQL

SELECT * FROM #MyTempTable

Using the same syntax, this works:

CREATE TABLE #MyTempTable (ID INT)

SELECT * FROM #MyTempTable

As does this, removing the pound signs and creating the table in the context of the current db:

DECLARE @.SQL nvarchar(128)

SET @.SQL = N'CREATE TABLE MyTempTable (ID INT)'

EXEC sp_executesql @.SQL

SELECT * FROM MyTempTable

Interestingly, it works with a global temp table:

DECLARE @.SQL nvarchar(128)

SET @.SQL = N'CREATE TABLE ##MyTempTable (ID INT)'

EXEC sp_executesql @.SQL

SELECT * FROM ##MyTempTable

Is this because sp_executesql is operating under a different connection and so my connection does not recognize the local temp table created by sp_executesql?

Thanks,

-Dave

If it works with ## global temp table and not # local temp table it is scope related, Reporting services most times reject code with local temp table but accept global temp tables. SQL Server estimates the time needed for the task and decideds if local or temp table is needed because a stored proc will run with local temp table load the stored proc into a Reporting services dataset and it rejects it.|||Dynamic SQL is always executed in a separate scope than the calling batch. So any temporary objects that you create or SET options that you modify or variables you declare etc are available only within the dynamic SQL batch. Global temporary tables are connection specific so they will persist across the dynamic SQL batch. What are you trying to do with temporary tables?|||

If you create your #Temp table BEFORE executing the dynamic SQL, the #Temp table will be available to the Dynamic SQL. You can then populate it with the dynamic SQL, and use that data after the dynamic SQL finishes. (IF the #Temp table exists BEFORE the dynamic SQL, it doesn't fall out of scope when the dynamic SQL concludes.)

For Example:

Code Snippet

USE Northwind
GO

CREATE TABLE #MyTemp
( RowID int IDENTITY,
LastName varchar(20)
)

DECLARE @.SQL nvarchar(250)

SET @.SQL = 'INSERT INTO #MyTemp SELECT LastName FROM Employees;'

EXECUTE sp_executesql @.SQL

SELECT *
FROM #MyTemp

RowID LastName
-- --
1 Buchanan
2 Callahan
3 Davolio
4 Dodsworth
5 Fuller
6 King
7 Leverling
8 Peacock
9 Smith
10 Suyama