SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 xp_cmdshell problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

garmon648
Starting Member

7 Posts

Posted - 09/07/2010 :  12:02:15  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 09/07/2010 :  12:33:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

garmon648
Starting Member

7 Posts

Posted - 09/07/2010 :  13:19:14  Show Profile  Reply with Quote
that worked. Thanks for the help!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 09/07/2010 :  13:34:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
You're welcome, glad to help.

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

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000