Using bcp to export query results to a piped text file (SQL Server 2012). When I enter the entire command, the query works as expected. However, when I create variables to build the command (so that I can have a dynamic file name based on a date), I receive the error: " ''bcp' is not recognized as an internal or external command, operable program or batch file" even though when I print the variable to confirm I've got it right, the string is exactly the same as the command I previously ran with success.
So, the following works:
DECLARE @ProcessDate AS INT = 20150619
DECLARE @Path AS VARCHAR(300) = 'C:\ARCU\CQExtract\CQ_Extract_'+ CONVERT(CHAR(8),@ProcessDate,1) + '.txt' + '" -t"|" -c -T'''
DECLARE @bcp AS VARCHAR(300) = '''bcp "SELECT top(20) * from ##CQ_EXTRACT" queryout "'
SET @bcp = @bcp + @Path
EXEC xp_cmdshell 'bcp "SELECT top(20) * from ##CQ_EXTRACT" queryout "C:\ARCU\CQExtract\CQ_Extract_20150613.txt" -t"|" -c -T'
But, when I substitute the two parts of the command with the @bcp and @Path variables and run EXEC xp_cmdshell @bcp, (or EXEC master..xp_cmdshell @bcp) I receive the above error. When I PRINT the @bcp variable, it returns the same string I successfully run:
'bcp "SELECT top(20) * from ##CQ_EXTRACT" queryout "C:\ARCU\CQExtract\CQ_Extract_20150619.txt" -t"|" -c -T'
To confirm, I can copy the returned printed variable and paste it back into the query and it runs as it should.
I should also mention that I've had the same results running the query from my machine and directly on the SQL server.
I'm stumped, so any insights would be welcome.