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
thewayne73Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Try it this way:

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
0
 
thewayne73Author Commented:
Thanks acperkins.  Sometimes it just takes another pair of eyes to see the obvious.


0
 
Anthony PerkinsCommented:
>>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
0
 
thewayne73Author Commented:
I did have a print on it, but did not see it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.