Exec vs. Temp Table 2

[Pranklin]

Can anybody explain why this does not work:

EXEC ('CREATE TABLE #tmpMine (MyData Int)')
EXEC ('INSERT INTO #tmpMine (MyData) VALUES (54)')
EXEC ('SELECT * FROM #tmpMine')

I get the following errors:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tmpMine'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#tmpMine'.


And yes for specific reasons I have to use Exec.

This is using SQL 8.00.

Thanks in advance for your help.

 

[MaxZak]

Because temp tables goes out of scope (and are dropped) at every execution of "EXEC". You'd have to include all the commands into a single stored procedure.

 

[moonshadow]

Could you explain why you have to use EXEC, I'd be very interested ?