xp_cmdshell command not executing last command when run as job

Question

First off, before everybody shouts at me - I'm bug fixing in legacy code and a re-write is off the cards for now - I have to try to find a fix using the xp_cmdshell command.

I have a proc which is executed via a scheduled job. The proc is full of TSQL like the below to dump data to a log file.

SELECT *    
INTO Temp
FROM MyView

SET @cmd1 = 'bcp "SELECT * FROM [myDatabase].dbo.Temp" queryout "C:\temp.txt" -T -c -t" "'
SET @cmd2= 'type "C:\temp.txt" >> "C:\output.txt"'
EXEC master..xp_cmdshell @cmd1
EXEC master..xp_cmdshell @cmd2

DROP TABLE Temp

The problem is that the last of these commands in the proc doesn't appear to run. I can see the result in the text.txt file, but not the output.txt file. All of the preceding work fine though and it works fine when I run this on it's own.

Can anyone suggest why this might happen or suggest an alternative way to achieve this?

Thanks

Answer 1

I think, that BCP as external process runs async. So it could be, that your file is not yet written in the moment you are trying to copy its content.

• Suggestion 1: Include an appropriate wait time
• Suggestion 2: Call your first command a second time with changed target file name
• Suggestion 3: Use copy rather than type

You might create a file c\temp.txt with just hello world in it. Try to type it into one file before the BCP and type it into another file after the BCP.