Query into text file: bcp, dynamic SQL, temp tables and table variables

Question

Query into text file: bcp, dynamic SQL, temp tables and table variables

mmcardle

SSCommitted

Points: 1543
More actions
September 1, 2014 at 9:09 am

#311164

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)
SELECT0, @Job + '-' + CAST(@Suffix AS VARCHAR(20)) + '-' + CAST(@StartingOperNum AS VARCHAR(20))
UNION
SELECTdet_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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Local temp tables and table variables will loose scope when executing xp_cmdshell. Have you tried with a global temp table? Or simply creating a permanent table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mmcardle SSCommitted Points: 1543 More actions · Answer 2

Thank you for your reply Luis.

I dismissed the global temp table because this is something that multiple users will be doing concurrently, so for now I am going down the permanent table route and will come back to it when I have more time.

Regards,

Mark