• Since the error is due to the #tmp table no longer being in scope once the EXEC() is done, one could fix this to work by making the table global and adding an explicit DROP after its last use. This leaves the table available to the SELECT.

    Declare @strSql varchar(2000)

    Set @strSql = ''

    Set @strSql = @strSql + 'Create table ##tmp (id int)' -- Now "##tmp', not "#tmp'

    Set @strSql = @strSql + 'Insert into ##tmp(id) values (1)'

    Exec (@strSql)

    Select * from ##tmp

    drop table ##tmp

    Of course, in practice, one must be careful in naming global temp tables to avoid possible collisions with unrelated processes.