title: Referencing a #temp table from an external process
body: <p>Using SQL Server 2008, I have two stored procedures -</p>
  
  <pre><code>create procedure [dbo].[SH_Export_data] (@unit varchar(5)) as
    declare @sqlquery varchar(max) = 'select check_dt from chk_data where unit = ' 
      + @unit
  
    IF object_id('tempdb..#TempTbl') IS NOT NULL
      DROP TABLE #TempTbl
    create TABLE #TempTbl (
      col1 varchar(max), col2 varchar(max)
    )
  
    insert #TempTbl(col1)
    exec(@sqlquery)
  
  
  
  create procedure [dbo].[SH_Export] as
    DECLARE @unit varchar(5), @sql varchar(max) = '', @file_name = 'c:\export.xls'
  
    DECLARE crsr CURSOR for
      select unit
      from communities
  
    OPEN crsr
    FETCH NEXT FROM crsr
    into @unit
  
    while @@FETCH_STATUS = 0 
    BEGIN
      set @sql = 'exec master..xp_cmdshell ''bcp "exec dbo.SH_Export_data ' +
        @unit + '" queryout "' + @file_name + '" -c -T "''
    END
  </code></pre>
  
  <p>Note that there is one stored procedure calling another. The stored procedure being called creates a temp table and uses it within itself. </p>
  
  <p>when I run -</p>
  
  <pre><code>exec dbo.SH_Export
  </code></pre>
  
  <p>I get this error message -</p>
  
  <pre><code>Error = [Microsoft][SQL Server Native Client 10.0][SQL Server] 
  Invalid object name '#TempTbl'.
  </code></pre>
  
  <p>BUT I can run, without error -</p>
  
  <pre><code>exec dbo.SH_Export_data 63058
  </code></pre>
  
  <p>what causes that?</p>