title: SQL Server 2008 query into text file: bcp, dynamic SQL, temp tables and table variables
body: <p>I am adding a section to some vendor code to create a text file from data in a temp table and send it to a network location. The temp table is #PickList</p>
  
  <p>I've created a table type for the table variable, so I can pass it as a parameter to the dynamic SQL, but this in turn needs to be passed to bcp which creates the text file. This is what I have so far:</p>
  
  <pre><code>DECLARE @strFileLocation VARCHAR(1000)
  DECLARE @strFileName VARCHAR(1000)
  DECLARE @bcpCommand VARCHAR(8000)
  DECLARE @strSQL VARCHAR(2000)
  
  SET @strFileLocation = '\\phaal\FTP\LocalUser\LIFT01\inbox\'
  
  SET @strFileName = 'BPL' + @Job + '-' + CAST(@Suffix AS VARCHAR(20)) + '-' +
  CAST(@StartingOperNum AS VARCHAR(20)) + CAST(DATEPART(hh,GETDATE()) AS
              VARCHAR(10)) +  CAST(DATEPART(mi,GETDATE()) AS VARCHAR(10)) +   
  CAST(DATEPART(ss,GETDATE()) AS VARCHAR(10)) + '.txt'
  
  DECLARE @tblLeanLiftData AS [dbo].[BWT_LeanLiftPickTableType]
  
  INSERT INTO @tblLeanLiftData (intSeq, strText)
  SELECT  0,  @Job + '-' + CAST(@Suffix AS VARCHAR(20)) + '-' + 
  CAST(@StartingOperNum AS VARCHAR(20))
  UNION
  SELECT  det_JobSequence, det_JobMatlItem + ':' + det_LotDescription + ',-,' +
  CAST(det_QtyToPick AS VARCHAR(20))
  FROM    #PickList
  
  SET @strSQL = 'EXEC sp_executesql N''SELECT strText FROM @tblLeanLiftData
  ORDER BY intSeq'', N''@tblLeanLiftData LeanLiftPickTableType READONLY'',
                                    @tblLeanLiftData=@tblLeanLiftData'
  
  SET @bcpCommand = 'bcp "' + @strSQL + '" queryout "'
  
  SET @bcpCommand = @bcpCommand + @strFileLocation + @strFileName + '" -T -c'
  
  EXEC master..xp_cmdshell @bcpCommand
  </code></pre>
  
  <p>When I exec the code, I get:</p>
  
  <blockquote>
    <p>SQLState = 37000, NativeError = 137<br>
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@tblLeanLiftData".<br>
    SQLState = 37000, NativeError = 8180<br>
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.<br>
    NULL</p>
  </blockquote>
  
  <p>So, the table variable is still out of scope.</p>
  
  <p>I was hoping someone with fresh eyes could spot where I've gone wrong or an alternative route or if it's just not possible this way?</p>
  
  <p>It's a bit of a double-edged sword, because I've tried various ways and either the temp table or table variable ends up being out of scope.</p>