xp_cmdshell bcp not recognized as an internal or external command

1

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.

2

I don't think you need the extra quotes at the beginning and end. So it should be:

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

If that does not work, try the following simple command (which should work) and work your way up from that

DECLARE @cmd VARCHAR(32)= 'dir';
EXEC xp_cmdshell @cmd;
2 Likes
3

James, thanks for the lightning fast reply! Your solution worked!

When running the command as a string, I was required to enclose the statement in single quotes, so that is how I built the variables. Per your suggestion, I removed them from the variable, and it ran just as expected. I'll close this post, but am curious why the quotes would be necessary in one scenario, but not the other?

4

Its a parameter, so you need either:

EXEC xp_cmdshell @MyParameter

or

EXEC xp_cmdshell 'MyStringValue'

and as such @MyParameter should contain just

    MyStringValue

and NOT include any quotes like this:

    'MyStringValue'

but when you put a value into @MyParameter you need the quotes then e.g.

    SET @MyParameter = 'MyStringValue'
1 Like
5

Makes perfect sense! Thank you.