bcp usage

Question

I have a command line that I cannot seem to get right. I tried the format that was illustrated online but I could get it to work. Here is what I currently have that does not work:

bcp -Snbreports -dBuyseasons_transactions -n -T query ^
"SELECT p.OrderGroupId, f.OrderFormId, p.BillingCurrency, p.Created, p.HandlingTotal, p.LastModified, p.ModifiedBy, p.Name, p.TrackingNumber, p.SoldToAddressId, p.ShippingTotal, p.SoldToName, p.Status, p.SubTotal, p.TaxTotal, p.Total, ^
       f.CreatedBy, f.CustomerPONumber, f.DaysInTransit, f.GiftMessage, f.MerchantBatchId, f.MerchantMemberNumber, f.MerchantOrderNumber, f.MerchantPONumber, f.MerchantSessionId, f.MerchantShipMethod, f.MerchantStatus, f.MerchantStatusSent, ^
       f.NonDiscountedOrderTotal, f.OrderBranding, f.OrderClassification, f.OrderMessage, f.OrderSourceId, f.Created as OrderSubmitted, f.OriginalOrderNumber, f.PrintPrices, f.PromoUserIdentity, f.RequestedDeliveryDate, f.RequestedShipLevel, f.ScreeningReasonId, f.ScreeningReleasedBy, ^
       f.SelectedShippingMethodId, f.SelectedShippingMethodName, f.ShipNote, f.SMSNotificationPhoneNumber, f.SupplierShipLocation, f.TaxExemptNumber, f.UserLock, f.ZeroPickBinLocation, f.ZpLastOpenedBy, f.ZpLastOpenedDate, ^
       b.OrderAddressId, s.OrderAddressId, l.LineItemId, ^
       b.City as BillingCity, b.CountryCode as BillingCountryCode, b.CountryName as BillingCountryName, b.DaytimePhoneNumber as BillingDaytimePhoneNumber, b.Email as BillingEmail, b.FirstName as BillingFirstName, b.EveningPhoneNumber as BillingEveningPhoneNumber, b.FaxNumber as BillingFaxNumber, b.LastName as BillingLastName, b.Line1 as BillingLine1, b.Line2 as BillingLine2, b.Name as BillingName, b.Organization as BillingOrganization, b.PostalCode as BillingPostalCode, b.RegionCode as BillingRegionCode, b.RegionName as BillingRegionName, b.State as BillingState, ^
       s.City as ShippingCity, s.CountryCode as ShippingCountryCode, s.CountryName as ShippingCountryName, s.DaytimePhoneNumber as ShippingDaytimePhoneNumber, s.Email as ShippingEmail, s.FirstName as ShippingFirstName, s.EveningPhoneNumber as ShippingEveningPhoneNumber, s.FaxNumber as ShippingFaxNumber, s.LastName as ShippingLastName, s.Line1 as ShippingLine1, s.Line2 as ShippingLine2, s.Name as ShippingName, s.Organization as ShippingOrganization, s.PostalCode as ShippingPostalCode, s.RegionCode as ShippingRegionCode, s.RegionName as ShippingRegionName, s.State as ShippingState, ^
       l.AllowBackordersAndPreorders,l.BackorderQuantity,l.Created as LineCreated,l.CreatedBy as LineCreatedBy,l.CurrentArrivalDate,l.CustomerTestValue,l.Description, ^
       l.DisplayName,l.ExtendedPrice,l.FulfillingVendorId,l.InStockQuantity,l.InventoryCondition,l.LastModified as LineLastModified,l.LineItemBranding,l.LineItemDiscountAmount, ^
       l.LineItemShipDate,l.LineItemStatus,l.LineNumber,l.LineSource,l.LineItemType,l.ListPrice,l.MerchantCommission,l.MerchantLineHandling, ^
       l.MerchantLineNumber,l.MerchantLineShipping,l.MerchantLineTax,l.MerchantOrderItemId,l.MerchantPrice,l.MerchantReturnLocation, ^
       l.MerchantSku,l.ModifiedBy as LineModifiedBy,l.OrderLevelDiscountAmount,l.OriginalArrivalDate,l.ParentLineItemId,l.ParentSku,l.PlacedPrice, ^
       l.PoLineNumber,l.PoNumber,l.PreorderQuantity,l.ProductCatalog,l.ProductCategory,l.ProductId,l.Quantity,l.RmaNumber,l.ServerName, ^
       l.ShippingMethodId,l.ShippingMethodName,l.ProductVariantId,l.Status as LineStatus^
FROM PurchaseOrders p WITH(NOLOCK) ^
INNER JOIN OrderForms f WITH(NOLOCK) ON p.OrderGroupId = f.OrderGroupId ^
INNER JOIN LineItems l WITH(NOLOCK) ON l.OrderFormId = f.OrderFormId AND l.OrderGroupId = f.OrderGroupId ^
INNER JOIN OrderAddresses b WITH(NOLOCK) ON b.OrderGroupId = p.OrderGroupId AND b.OrderAddressId = p.SoldToAddressId ^
INNER JOIN OrderAddresses s WITH(NOLOCK) ON s.OrderGroupId = p.OrderGroupId AND s.OrderAddressId = l.ShippingAddressId" queryout Orders.dat

I get the error:

Copy direction must be either 'in', 'out' or 'format'.
usage: bcp {dbtable | query} {in | out | queryout | format} datafile

followed by a usage prompt. Any idea what is wrong?

---

Kevin Burton

Answer 1

The text of the query should be the first thing in the command line. Also there should not be "query". So you should have something like:

bcp "SELECT...  " queryout Orders.dat -Snbreports -dBuyseasons_transactions -n -T

---

This posting is provided "AS IS" with no warranties, and confers no rights.