September 3, 2014 at 5:52 am
I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.
I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:
DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)
DECLARE @strSQL VARCHAR(2000)
DECLARE @strDB VARCHAR(50)
SET @strDB = (SELECT DB_NAME())
SELECT @strLocation = det_location FROM #PickList
SET @strFileLocation = '\\phaal\e$\ftproot\LocalUser\LIFT01\inbox\'
SET @TransNum = @Job + '-' + CAST(@Suffix AS VARCHAR(20)) + '-' + CAST(@StartingOperNum AS VARCHAR(20))
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'
INSERT INTO dbo.BWT_Lift_Transaction
SELECT@UserName, @UserName, GETDATE(), GETDATE(), @RptSessionID, 0, 0, @TransNum, GETDATE(),det_JobMatlItem,CAST(det_QtyToPick AS VARCHAR(20)),
N'-',det_LotDescription,det_Location
FROM#PickList
SET @strSQL = 'SELECT [TransNum],[TransDate],[Item],[Quantity],[Direction],[Lot],[LiftName] FROM ' + @strDB + '.dbo.BWT_Lift_Transaction WHERE [TransNum] = ''' + @TransNum + ''''
SET @bcpCommand = 'bcp "' + @strSQL + '" queryout "'
SET @bcpCommand = @bcpCommand + @strFileLocation + @strFileName + '" -c -T'
EXEC master..xp_cmdshell @bcpCommand
DELETE FROM dbo.BWT_Lift_Transaction WHERE [TransNum] = @TransNum
The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:
output
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.
I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.
Slightly confused though, but I'm sure some bright spark has the answer.
Hope that makes sense.
Cheers,
Mark
September 3, 2014 at 7:55 am
Quick thought, there is something missing in the code, check that all variables are declared and initialized.
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply