• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • Last Modified:

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
0
steven
Asked:
steven
  • 2
  • 2
1 Solution
 
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
 
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
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now