BCP command

I am trying to query out using with the below code, with no luck can anyone help.



 declare @outfile            nvarchar(200),
              @comrun          nvarchar(400)
 
set @outfile = '\\eincutil1\fstaskman\report\outputfiles\sa\'  + db_name() + 'ALLOpenOrder.csv'


set @comrun = 'bcp  " select  order_date,
 item,
 description,  
 qty_ordered,
 qty_avail,
 job,
 opername,
 P_M_T_code
  from einc_testapp.dbo.ensopencoA queryout"' + @outfile + ' -c -t, -T -SEINCDATA'

exec master..xp_cmdshell @comrun




The results are :

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]
NULL
stevendeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
Try:

set @comrun = N'bcp  "select  order_date,
 item,
 description,  
 qty_ordered,
 qty_avail,
 job,
 opername,
 P_M_T_code
  from einc_testapp.dbo.ensopencoA" queryout ' + @outfile + N' -c -t, -T -SEINCDATA'

edit: added the N unicode prefix to the set statement to prevent the casting
0
stevendeveloperAuthor Commented:
Same result.
0
stevendeveloperAuthor Commented:
Looks like its the commas that are causing the issue..any ideas?
0
Harish VargheseProject LeaderCommented:
Hello,

There are 2 issues:
1. Double quotes for query is misplaced after Queryout.
2. Since bcp is an external applicaion (not sql statement), you cannot have line breaks in between. Change it as below:
set @comrun = 'bcp  " select  order_date,  item,  description,   qty_ordered,  qty_avail, job,  opername,  P_M_T_code   from einc_testapp.dbo.ensopencoA" queryout ' + @outfile + ' -c -t, -T -SEINCDATA'

Open in new window

Or if you want readability, then close the strings at each line like this:
set @comrun = 'bcp  " select  order_date, ' + 
      ' item,  description,   qty_ordered,  qty_avail, ' + 
      ' job,  opername,  P_M_T_code ' + 
      ' from einc_testapp.dbo.ensopencoA" queryout ' + @outfile + ' -c -t, -T -SEINCDATA'

Open in new window

-Harish
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PadawanDBAOperational DBACommented:
Doh, I didn't even think about this initially: could you try getting rid of the carriage returns like this:

set @comrun = N'bcp "select  order_date, item, description, qty_ordered, qty_avail, job, opername, P_M_T_code from einc_testapp.dbo.ensopencoA" queryout ' + @outfile + N' -c -t, -T -SEINCDATA'

edit: what harish said!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.