A Bulk-Copy error

Question

A Bulk-Copy error

dbaforever

Hall of Fame

Points: 3740
More actions
August 14, 2011 at 8:04 pm

#246351

Hi everyone,
-- Construct report path and file name
Set @ReportPath = '\\pncllwappco\E$\RAMCO\OUT\FINTG\LAWSON\PMI\' + 'INV' + '_' + Convert(varchar(8), @curDate, 112) + '.csv'
select @sql = 'bcp "SELECT MAINVDTL.PO_NUMBER as [PO NUMBER], MAINVDTL.LINE_NBR as [PO LINE], INVOICE=null, ceiling(MAINVDTL.ENTERED_QTY) as [INVOICE QUANTITY], MAINVDTL.TOT_BASE_AMT as [INVOICE PRICE]
FROM PROD.dbo.APPAYMENT "APPAYMENT" INNER JOIN PROD.dbo.APINVOICE "APINVOICE" ON "APPAYMENT"."COMPANY"="APINVOICE"."COMPANY" AND "APPAYMENT"."VENDOR"="APINVOICE"."VENDOR" AND "APPAYMENT"."INVOICE"="APINVOICE"."INVOICE" AND "APPAYMENT"."SUFFIX"="APINVOICE"."SUFFIX" AND "APPAYMENT"."CANCEL_SEQ"="APINVOICE"."CANCEL_SEQ" AND "APPAYMENT"."REC_STATUS"="APINVOICE"."REC_STATUS" AND "APPAYMENT"."VENDOR_GROUP"="APINVOICE"."VENDOR_GROUP" INNER JOIN "PROD"."dbo"."MAINVDTL" "MAINVDTL" ON "APPAYMENT"."COMPANY"="MAINVDTL"."COMPANY" AND "APPAYMENT"."VENDOR"="MAINVDTL"."VENDOR" AND "APPAYMENT"."INVOICE"="MAINVDTL"."INVOICE" AND "APPAYMENT"."SUFFIX"="MAINVDTL"."SUFFIX"
WHERE "APINVOICE"."REC_STATUS"=9 AND "MAINVDTL"."COMPANY"=400" queryout ' + @ReportPath + ' -T -t, -c';
EXEC master..xp_cmdshell @sql;
This is the error I get:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
Is this syntax not quite correct?
queryout ' + @ReportPath + ' -T -t, -c';
(I have tried directing the report to the local server and using S. but still get this error.)
Thanks, John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Perhaps I'm missing something I do not see the actual error?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Well sorry, Welsh.

When I run it in Mgmt Studio, no report is generated and I get this in the Results panel:

(The report path is on a different server)

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]

NULL

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Does the Service account are you using to run this job have the proper permissions?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dbaforever Hall of Fame Points: 3740 More actions · Answer 4

I'm just running the sql code in the Query Analyzer window.

And I get the same error when I set the report path to the local server.

Obviously, I'm doing something wrong.

Thanks, John

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 5

well, from a troubleshooting standpoint it looks like you aren't defining values for your switches of -T and -c... you do for -t (a comma). I've never used the bulk copy but I'd start there...