July 23, 2015 at 11:25 am
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....
July 23, 2015 at 12:19 pm
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