A BCP error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

A BCP error

Author

Topic

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-08-03 : 21:56:34

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';

Thanks, John

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-03 : 22:00:22

You forgot the -S parameter to specify the server, this should fix it:

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 + ' -S. -T -t, -c';

EXEC master..xp_cmdshell @sql;

jbates99
Constraint Violating Yak Guru

396 Posts

Posted - 2011-08-04 : 13:41:04

Thanks for the idea, Rob. I was hopeful but I got the same error ...

WHERE "APINVOICE"."REC_STATUS"=9 AND "MAINVDTL"."COMPANY"=400" queryout ' + @ReportPath + ' -S, -T -t, -c';

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-04 : 13:56:39

-S, won't work, you either use -S. to refer to the local SQL Server, or specify the full name -Sservername

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources