Link to home
Start Free TrialLog in
Avatar of thewayne73
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
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thewayne73
thewayne73

ASKER

Thanks acperkins.  Sometimes it just takes another pair of eyes to see the obvious.


>>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
I did have a print on it, but did not see it.