thewayne73
asked on
Where clause in EXEC master..xp_cmdshell 'bpc"
Hello Experts,
I have created the following BCP script below, but keep receiving the error that follows when I execute it. I can run it without the where clause just fine.
declare @move_dt datetime
, @move_yyyymmdd varchar(8)
, @move_letter_file_location varchar(2000)
, @bcp_move_cmd varchar(2000)
set @move_dt = convert(datetime,'7/1/2008 ',101)
set @move_yyyymmdd = right(replicate('0',4) + cast(datepart(yyyy, @move_dt) as varchar(4)),4) + right(replicate('0',2) + cast(datepart(mm, @move_dt) as varchar(2)),2) + right(replicate('0',2) + cast(datepart(dd, @move_dt) as varchar(2)),2)
set @move_letter_file_location = '\\server\move_' + @move_yyyymmdd + '.txt'
set @bcp_move_cmd = 'bcp "select * from database..address_view where (right(replicate("0",4) + cast(move_yyyy as varchar(4)),4)) + (right(replicate(0,2) + cast(move_mm as varchar(2)),2)) + (right(replicate(0,2) + cast(move_dd as varchar(2)),2)) = @move_yyyymmdd order by 3" queryout "'
set @bcp_move_cmd = @bcp_move_cmd + @move_letter_file_location + '" -t -c -SDEV_SQL -T'
EXEC master..xp_cmdshell @bcp_move_cmd
Error i am receiving:
SQLState = 42000, NativeError = 137
Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@move_yyyymmdd".
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL
I have created the following BCP script below, but keep receiving the error that follows when I execute it. I can run it without the where clause just fine.
declare @move_dt datetime
, @move_yyyymmdd varchar(8)
, @move_letter_file_location
, @bcp_move_cmd varchar(2000)
set @move_dt = convert(datetime,'7/1/2008
set @move_yyyymmdd = right(replicate('0',4) + cast(datepart(yyyy, @move_dt) as varchar(4)),4) + right(replicate('0',2) + cast(datepart(mm, @move_dt) as varchar(2)),2) + right(replicate('0',2) + cast(datepart(dd, @move_dt) as varchar(2)),2)
set @move_letter_file_location
set @bcp_move_cmd = 'bcp "select * from database..address_view where (right(replicate("0",4) + cast(move_yyyy as varchar(4)),4)) + (right(replicate(0,2) + cast(move_mm as varchar(2)),2)) + (right(replicate(0,2) + cast(move_dd as varchar(2)),2)) = @move_yyyymmdd order by 3" queryout "'
set @bcp_move_cmd = @bcp_move_cmd + @move_letter_file_location
EXEC master..xp_cmdshell @bcp_move_cmd
Error i am receiving:
SQLState = 42000, NativeError = 137
Error = [Microsoft][SQL Native Client][SQL Server]Must declare the scalar variable "@move_yyyymmdd".
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Sometimes it just takes another pair of eyes to see the obvious.<<
Or a Print statement :)
As in:
Print @bcp_move_cmd
-- EXEC master..xp_cmdshell @bcp_move_cmd
Or a Print statement :)
As in:
Print @bcp_move_cmd
-- EXEC master..xp_cmdshell @bcp_move_cmd
ASKER
I did have a print on it, but did not see it.
ASKER