xp_cmdshell problem - 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 2000 Forums

SQL Server Development (2000)

xp_cmdshell problem

Author

Topic

garmon648
Starting Member

7 Posts

Posted - 2010-09-07 : 12:02:15

I have the following query I am trying to run with xp_cmdshell

EXEC xp_cmdshell 'bcp "select a.drug_dose_id, barcode_id, b.drug_display_name, PACKAGE_CODE,case when wholesaler_price = 0 and preferred_barcode = 1 then 'Preferred price is 0' else null end as Price,case when package_code = '?' and preferred_barcode = 1 then 'No package size' else null end as Pack, case when fulfill_primary_flag = 1 and preferred_barcode = 0 then 'Invalid Fulfill Primary' else null end as FulFillPrimary from crx_data.dbo.ahi_drug_barcode A, crx_data.dbo.ahi_drug_dose B where a.drug_dose_id = b.drug_dose_id order by price desc,pack desc, fulfillprimary desc" queryout "C:\mytest.csv" -T -c -t; '

I get the following error: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

I am guessing it is due to the quote marks being used in the query but I am not sure how to avoid it. Any suggestions?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 12:33:03

Put your query in a view and then bcp out the view. Bcp can't handle certain queries.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog