Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  Query into text file: bcp, dynamic sql & tables

Author  Topic  mmcardle Starting Member 1 Post Posted - 2014-09-01 : 11:38:02 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 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: 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 When I exec the code, I get: SQLState = 37000, NativeError = 137 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Must declare the scalar variable "@tblLeanLiftData". SQLState = 37000, NativeError = 8180 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. NULL So, the table variable is still out of scope. 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? 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. Cheers, Mark