SQL Server : BCP not outputting a text file but runs

Question

I have the following code which compiles and runs with results in the viewer in SQL Server but the output file does not get generated. I do know that BCP works on my server with simple SQL statements but I can't get it to produce the file with this SQL statement. Anyone know why this is not working for me? I continually get the usage and parameters listing, but no output report or output file.

BEGIN
    SET NOCOUNT ON;

EXEC xp_cmdshell 'bcp 'select '"' + CONVERT(varchar,fo.orderno) + '"' as [STOP_ID],
  CASE WHEN (isnull(i.pickuptime,'') <> '') THEN convert(datetime,i.pickuptime,126) ELSE convert(datetime,fo.pickeduptime,126) END as [PICKUP_TIME],
  CASE WHEN (isnull(i.deliveredtime,'') <> '') THEN convert(datetime,i.deliveredtime,126) ELSE convert(datetime,fo.deliveredtime,126) END as [DELIVERY_TIME],
  CASE WHEN (isnull(i.pickupcontact,'') <> '') THEN '"' + i.pickupcontact + '"' ELSE '"' + og.marketsitenumber + '"' END as [SENDER_CODE],
  CASE WHEN (isnull(i.pickupcompany,'') <> '') THEN '"' + i.pickupcompany + '"' ELSE '"' + fo.pickupcompanyname + '"' END as [SENDER_NAME],
  '"' + (CONVERT(varchar, fo.PickupstreetNo) + ' ' + fo.PickupStreet + ' ' + fo.PickupUnit) + '"' as [SENDER_STREET],
  '"' + fo.pickupcity + '"' as [SENDER_CITY],
  '"' + fo.pickupprovince + '"' as [SENDER_STATE],
  '"' + fo.pickuppostalcode + '"' as [SENDER_POSTAL_CODE],
  '"US"' as [SENDER_COUNTRY],
  CASE WHEN (isnull(i.driver,'') <> '') THEN '"' + i.driver + '"' ELSE '"' + CONVERT(varchar,fo.pickupdriver) + '"' END as [DRIVER_ID],
  CASE WHEN (isnull(i.deliveryfacilitycode,'') <> '') THEN '"' + i.deliveryfacilitycode + '"' ELSE '""' END as [RECIPIENT_CODE], --Issue for left-joined "FO Only" records:  No safety net.
  CASE WHEN (isnull(i.deliverycompanyname,'') <> '') THEN '"' + i.deliverycompanyname + '"' ELSE '"' + fo.deliverycompanyname + '"' END as [RECIPIENT_NAME],
  '"' + (CONVERT(varchar, fo.deliverystreetNo) + ' ' + fo.DeliveryStreet + ' ' + fo.DeliveryUnit) + '"' as [RECIPIENT_STREET],
  '"' + fo.deliverycity + '"' as [RECIPIENT_CITY],
  '"' + fo.deliveryprovince + '"' as [RECIPIENT_STATE],
  '"' + fo.deliverypostalcode + '"' as [RECIPIENT_POSTAL_CODE],
  '"US"' as [RECIPIENT_COUNTRY_CODE],
  CASE WHEN (og.accounttype = 'stat' and st.servicetypeid in ('324','154','156','122','303','290')) THEN '"STAT"'
        WHEN ((og.accounttype = 'stat' and st.servicetypeid in ('186','304','305')) or (og.accounttype = 'scheduled' and fo.[route] in ('B1','B2','B3','B4'))) THEN '"MULTI-STAT"'
        WHEN (fo.route not like 'LH%' and og.accounttype = 'scheduled' and st.servicetypeid not in ('329') and fo.[route] not in ('B1','B2','B3','B4')) THEN '"ROUTE"'
        WHEN ((og.accounttype = 'scheduled' and st.servicetypeid in ('329')) or (og.accounttype = 'stat' and st.servicetypeid in ('372','373','374','377','386','329','356','387','388','389','390','391','392'))) THEN '"SWEEP"'
        WHEN (og.accounttype = 'scheduled' and fo.route like 'LH%') THEN '"LINE-HAUL"' 
        ELSE '"PLEASE CONTACT PDI IT WITH THIS STOP_ID"' END as [DELIVERY_TYPE],
  CASE WHEN (fo.route not like 'LH%' and og.accounttype = 'scheduled' and st.servicetypeid not in ('329') and fo.[route] not in ('B1','B2','B3','B4')) THEN '"' + fo.reference + '"' ELSE '""' END as [ROUTE_REFERENCE],
  CASE WHEN (fo.route not like 'LH%' and og.accounttype = 'scheduled' and st.servicetypeid not in ('329') and fo.[route] not in ('B1','B2','B3','B4')) THEN '"0"' ELSE '"' + convert(varchar,fo.distance) + '"' END as [MILEAGE],
  CASE WHEN ((og.accounttype = 'scheduled') or (og.accounttype = 'stat' and fo.servicetypeid in (329))) THEN '"' + convert(varchar,fo.waitingtimedriver1) + '"' ELSE '"0"' END as [WAIT_TIME],
  '""' as [WAIT_START_TIME],    --"The time the courier began waiting after the grace period."  Not currently captured anywhere in system.
  CASE WHEN (((og.accounttype = 'scheduled') or (og.accounttype = 'stat' and fo.servicetypeid in (329))) and fo.waitingtimedriver1 > 0) THEN '"' + fo.reference + '"' ELSE '""' END as [WAIT_TIME_ROUTE_REFERENCE],
  '""' as [ARRIVAL_TIME], --"The time the courier arrived at the Pharmacy to pick up the shipment.  REQUIRED in the case of wait time."  Not currently captured anywhere in system.
  '""' as [DEPARTURE_TIME], ----"The time the courier departed from the Pharmacy with the shipment.  REQUIRED in the case of wait time."  Not currently captured anywhere in system.
  '"USD"' as [CURRENCY],
  '"0"' as [FUEL_SURCHARGE],
  '"0"' as [TAX],
  '"0"' as [HOSPICE_CHARGE],
  '"0"' as [MISC_CHARGE],
  '"' + convert(varchar,fo.totalamount) + '"' as [TOTAL_CHARGE]
  from CC.dbo.finalizedorders fo
inner join servicetypes st on fo.servicetypeid = st.servicetypeid
inner join aropentransactions at on fo.invoicenumber = at.transactionnumber
inner join OBR_Generator og on og.AccountNumber = fo.AccountNumber
LEFT JOIN OrderSearchView osv ON fo.OrderNo = osv.[Order No]
  LEFT JOIN [IRIS\SQLEXPRESS].IDSImport.dbo.IDSData i ON osv.[Cust. Field 2] = i.TripID
where og.Market = 'Columbus'
and at.transactiondate = '20150124'
and fo.totalamount > 0
and og.accounttype <> 'invoice'' queryout "C:\bcptest.txt" -T -c -t,'

END

Answer 1

The query is too long. I think you are restricted to 4000 characters. I make yours 4753. Try creating a view and then doing select * from the view.