?
Solved

Where clause in EXEC master..xp_cmdshell 'bpc"

Posted on 2008-11-12
4
Medium Priority
?
1,264 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:thewayne73
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 22946198
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
 

Author Comment

by:thewayne73
ID: 22949943
Thanks acperkins.  Sometimes it just takes another pair of eyes to see the obvious.


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22952930
>>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
 

Author Comment

by:thewayne73
ID: 22953443
I did have a print on it, but did not see it.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question