set @sql = N'create table #tmp_table(guarid int)'

From: Zach (zchauvin10787_at_hotmail.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 19:19:21 GMT

When I execute:

declare @sql nvarchar(200)
set @sql = N'create table #tmp_table(guarid int)'
exec sp_executesql @sql
select * from #tmp_table

I get: "Invalid object name '#tmp_table'." and there is no entry in tempdb

However; if I change the temp table to a global table (##tmp_table) it runs
successfully and updates tempdb. Is this a bug or by design?

Thanks in adavnce,
Zach

Relevant Pages

  • Re: Application role to access xp_cmdshell
    ... This necessitates that your user procs be owned ... See Cross-database chaining in the SQL 2000 ... You will also need to allow non-sysadmin users to execute xp_cmdshell. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Convert / transpose rows to columns using SQL - a solution
    ... -- Create unique temp table name. ... EXEC (@SQL) ... OPEN Cols ...
    (microsoft.public.sqlserver.programming)
  • xp_sqlagent_proxy_account: Specified user cannot login
    ... I am trying to configure SQL so that a user without admin privileges can ... execute xp_cmdshell. ... EXEC master..xp_sqlagent_proxy_account N'SET' ...
    (microsoft.public.sqlserver.security)
  • Re: Need to make Sort ORDER in SP defined by Input variable
    ... If I enter 'ISBN' when I execute the SP below, it executes, ... SET NOCOUNT ON ... DECLARE @SQL nvarchar ... > exec sp_executesql @SQL ...
    (microsoft.public.sqlserver.programming)
  • Dynamaic SQLs
    ... I use two dynamic SQL to contruct a SQL in a stored ... SQL for inserting record into temp table ... when I execute in crystal report. ...
    (microsoft.public.sqlserver.programming)
Loading