Chris Morris (10/15/2008)

---

That's interesting, I also assumed it took a new connection.

If you create the temp table before executing the string, then (as Barry's code shows) the table is visible to the session taken by the executing string. But if the executing string creates the temp table, then the temp table is not visible to the original session:

[font="Courier New"]SET NOCOUNT ON

DECLARE @Sql NVARCHAR(100)

SET @Sql = 'SELECT TOP 1 [collation] INTO #temp FROM master.dbo.syscolumns'

EXEC(@Sql)

EXEC sp_executesql @Sql

SELECT * FROM #temp

"Server: Msg 208, Level 16, State 1, Line 9

Invalid object name '#temp'."

[/font]

Cheers

ChrisM