BCP QUERY OUT

steven
steven used Ask the Experts™
on
BCP QUERY OUT, I am not sure how to query out, so i am attempting to output the headers which i will then combine with the data. I have never used query out to do this as we usually create a table and place the headers in the bcp the data out of the table then combine.   IS it possible to do this with query out..

                                                declare @outfile nvarchar(1000),@comrun nvarchar(500)
                        set @outfile = '\\eincutil01\Syteline\report\outputfiles\ezimmerman\'  + db_name() + 'PaperLessBak.csv'


                     SET @HEAD = 'BCP "select ''CONUM'',''LINE'',''CUSTPO'',''BILLTO'',''BILLTOADDR'',''SHIPTO'',''SHIPTOADDR'',''SHIPCODE'',''DESCTIPTION'',''ITEM'',
                                   ''DESCRIPTION'',''UM'',''QTYORDER'',''QTYREQUIRED'',''QTYSHIPPED'',''WHSE'',''DUEDATE'',''PROMISEDATE'',''CERTYPE'',''COFC'',''COLLECTACCT'',
                                   ''LOCATION'',''QTYONHAND'',''ITEMTOBECUT'',''ITEMTOCUTTO'',''QTYTOCUT'',''LOC'' " queryout ' +  @outfile +'  -c -t, -T -SEINCDATA01'
                                       PRINT  @HEAD
                        
                              exec master..xp_cmdshell @HEAD
                        
                              exec master..xp_cmdshell @HEAD

ERROR
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"]         [-x generate xml format file]
  [-d database name]        [-K application intent]
NULL
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Consultant
Top Expert 2009
Commented:
the below code will fix your bcp statement

DECLARE @HEAD NVARCHAR(2000);
declare @outfile nvarchar(1000),@comrun nvarchar(500)
set @outfile = '\\eincutil01\Syteline\report\outputfiles\ezimmerman\'  + db_name() + 'PaperLessBak.csv'


SET @HEAD

    = N'BCP "select ''CONUM'',''LINE'',''CUSTPO'',''BILLTO'',''BILLTOADDR'',''SHIPTO'',''SHIPTOADDR'',''SHIPCODE'',''DESCTIPTION'',''ITEM'',''DESCRIPTION'',''UM'',''QTYORDER'',''QTYREQUIRED'',''QTYSHIPPED'',''WHSE'',''DUEDATE'',''PROMISEDATE'',''CERTYPE'',''COFC'',''COLLECTACCT'',''LOCATION'',''QTYONHAND'',''ITEMTOBECUT'',''ITEMTOCUTTO'',''QTYTOCUT'',''LOC'' " queryout ' + @outfile +'  -c -t, -S EINCDATA01 -T ';
---PRINT @HEAD;
EXEC master..xp_cmdshell @HEAD;
stevendeveloper

Author

Commented:
Thank you very much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial