xp_cmdshell and bcp

  • I'm dynamically building a BCP command and executing it.

    If I manually execute the command like so;

    EXEC master..xp_cmdshell 'bcp "exec myDB.dbo.EXTRACT_data ''2015-06-01 00:00:00.000'', ''2015-06-30 23:59:59.000'' " queryout "n:\results\data.txt" -U my_user -P happy11 -c'

    It works.

    But if I make a variable = to the command

    IE - set @bcpCommand = bcp "exec myDB.dbo.EXTRACT_data ''2015-06-01 00:00:00.000'', ''2015-06-30 23:59:59.000'' " queryout "n:\results\data.txt" -U my_user -P happy11 -c

    Like EXEC master..xp_cmdshell @bcpCommand

    it fails. Incorrect syntax near '2015'.

    I think it goes to how the command is handling the quotes but I just can't seem to get it to work....

  • jackimo (7/23/2015)


    I'm dynamically building a BCP command and executing it.

    If I manually execute the command like so;

    EXEC master..xp_cmdshell 'bcp "exec myDB.dbo.EXTRACT_data ''2015-06-01 00:00:00.000'', ''2015-06-30 23:59:59.000'' " queryout "n:\results\data.txt" -U my_user -P happy11 -c'

    It works.

    But if I make a variable = to the command

    IE - set @bcpCommand = bcp "exec myDB.dbo.EXTRACT_data ''2015-06-01 00:00:00.000'', ''2015-06-30 23:59:59.000'' " queryout "n:\results\data.txt" -U my_user -P happy11 -c

    Like EXEC master..xp_cmdshell @bcpCommand

    it fails. Incorrect syntax near '2015'.

    I think it goes to how the command is handling the quotes but I just can't seem to get it to work....

    Try this:

    DECLARE @bcpCommand AS varchar(200);

    SET @bcpCommand = 'bcp "exec myDB.dbo.EXTRACT_data ''2015-06-01 00:00:00.000'', ''2015-06-30 23:59:59.000'' " queryout "n:\results\data.txt" -U my_user -P happy11 -c';

    You need a single quote at both ends of that entire string.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

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

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