BCP command line / error capturing / bulk insert bugs

BCP command line / error capturing / bulk insert bugs

Post by frederickfl.. » Wed, 31 May 2000 04:00:00



I am attemtping to use bcp to load a file from the command line.  I
originally used the Bulk Insert statement -- using the same parameters -
- which loaded the file successfully (except for some bugs, described
below).  When I use the command line, I get

bcp tgitest.dbo.DWLStaging in
ItemMasterDetail.txt /SDelta /Usa /Pourpassword /c /F2

Starting copy...
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near 'varchar'.

What's wrong?

The reason I want to know is I want to capture errors.  The bcp command
line has the /e flag to capture errors to a file.  The Bulk Insert
documentation does not have a simillar option.  If I put "errorfile
= 'whatever'", SQL server does not parse it as an error, but I don't
get a file either (I can put garbage in the filename without it
complaining).  Does anyone know if it can be used?

When I used Bulk Insert, if there is a truncation error on one row,
that row AND the row after don't get loaded.  Is there a fix for that?
We have SP2 already.

If a Batch_size limit is set, the insert mis-counts the number of rows
by 1 for each batch.  Like, if the batch size is 1000, it will insert
1000 rows, but read 1001.  It will display "1001 rows inserted", and
the 1001st row will be missing from the load.  So if there are 10,000
total rows, it will drop 9 of them.  Any fix?

--
Yabba Dabba Doo

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

BCP command line / error capturing / bulk insert bugs

Post by Neil Pik » Thu, 01 Jun 2000 04:00:00


 Frederick - I would urge you to open a paid fault case with MS PSS on these,
but comments in-line.

Quote:> bcp tgitest.dbo.DWLStaging in
> ItemMasterDetail.txt /SDelta /Usa /Pourpassword /c /F2

> Starting copy...
> SQLState = 37000, NativeError = 170
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
> Incorrect syntax near 'varchar'.

 Please post DDL and a zip with a small amount of data to repro this

Quote:> The reason I want to know is I want to capture errors.  The bcp command
> line has the /e flag to capture errors to a file.  The Bulk Insert
> documentation does not have a simillar option.  If I put "errorfile
> = 'whatever'", SQL server does not parse it as an error, but I don't
> get a file either (I can put garbage in the filename without it
> complaining).  Does anyone know if it can be used?

 No, it isn't there.

Quote:> When I used Bulk Insert, if there is a truncation error on one row,
> that row AND the row after don't get loaded.  Is there a fix for that?
> We have SP2 already.

 That's the same as BCP and expected behaviour

Quote:> If a Batch_size limit is set, the insert mis-counts the number of rows
> by 1 for each batch.  Like, if the batch size is 1000, it will insert
> 1000 rows, but read 1001.  It will display "1001 rows inserted", and
> the 1001st row will be missing from the load.  So if there are 10,000
> total rows, it will drop 9 of them.  Any fix?

 Haven't seen that.  Does it occur with any batch size?

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq

 
 
 

BCP command line / error capturing / bulk insert bugs

Post by frederickfl.. » Thu, 01 Jun 2000 04:00:00


>  Frederick - I would urge you to open a paid fault case with MS PSS

Pardon my ignorance.  What's that?

>  Please post DDL and a zip with a small amount of data to repro this

I am not sure of how to attach a zip.    Looks as if I need the x-
attachments header.  If that's not the right one, what is?

CREATE TABLE [dbo].[DWLStaging] (
        [Baan ID] [varchar] (10) NULL ,
        [Company Name] [varchar] (26) NULL ,
        [Item] [varchar] (34) NULL ,
        [Description] [varchar] (255) NULL ,
        [Material] [varchar] (50) NULL ,
        [Size] [varchar] (26) NULL ,
        [Standard] [varchar] (2) NULL ,
        [Weight] [varchar] (12) NULL ,
        [Search Key I] [varchar] (100) NULL ,
        [Search Key II] [varchar] (100) NULL ,
        [Baan Item Type] [varchar] (100) NULL ,
        [Baan Item Group] [varchar] (100) NULL ,
        [Baan Product Type] [varchar] (50) NULL ,
        [Selection code] [varchar] (2) NULL ,
        [Signal code] [varchar] (2) NULL ,
        [Tax code] [varchar] (2) NULL ,
        [Item Text] [varchar] (2) NULL ,
        [Unit Set] [varchar] (2) NULL ,
        [Inventory Unit] [varchar] (50) NULL ,
        [Storage Unit] [varchar] (50) NULL ,
        [Warehouse] [varchar] (100) NULL ,
        [Lot control] [varchar] (2) NULL ,
        [Outbound Priority] [varchar] (2) NULL ,
        [Period for Shelf life] [varchar] (2) NULL ,
        [Shelf Life(Periods)] [varchar] (2) NULL ,
        [Floor stock] [varchar] (2) NULL ,
        [ABC code] [varchar] (2) NULL ,
        [Last counting date] [varchar] (2) NULL ,
        [Cumulative Issue] [varchar] (2) NULL ,
        [Expected Annual Issue] [varchar] (2) NULL ,
        [Slow-Moving Percentage] [varchar] (2) NULL ,
        [Service Level] [varchar] (2) NULL ,
        [Safety stock] [varchar] (2) NULL ,
        [Maximum Inventory] [varchar] (2) NULL ,
        [Seasonal pattern for Safety St] [varchar] (2) NULL ,
        [Seasonal Pattern for demand] [varchar] (2) NULL ,
        [Forcast method] [varchar] (2) NULL ,
        [Inventory carrying cost] [varchar] (2) NULL ,
        [Inventory on Hand] [varchar] (20) NULL ,
        [Inventory on Hold] [varchar] (20) NULL ,
        [Inventory on Order] [varchar] (20) NULL ,
        [Allocated Inventory] [varchar] (2) NULL ,
        [Hard Allocated Inventory] [varchar] (2) NULL ,
        [Quotation Allocations] [varchar] (2) NULL ,
        [Last Inventory Transaction Dat] [varchar] (2) NULL ,
        [Order Policy] [varchar] (2) NULL ,
        [Order System] [varchar] (2) NULL ,
        [Order Method] [varchar] (2) NULL ,
        [Order Quantity Multiple of] [varchar] (2) NULL ,
        [Purchase Container Qty] [varchar] (20) NULL ,
        [Maximum Order Quantity] [varchar] (2) NULL ,
        [Update Method E-Item Relation] [varchar] (2) NULL ,
        [Fixed Order Quantity] [varchar] (2) NULL ,
        [Economic Order Qty] [varchar] (30) NULL ,
        [Re-Order Point] [varchar] (4) NULL ,
        [Order Interval] [varchar] (4) NULL ,
        [Direct Delivery from Quantity] [varchar] (22) NULL ,
        [Order Lead Time] [varchar] (30) NULL ,
        [Safety Time] [varchar] (6) NULL ,
        [First Allowed Order Date] [varchar] (4) NULL ,
        [Order Costs] [varchar] (4) NULL ,
        [Update Inventory/Order Data] [varchar] (4) NULL ,
        [Phantom] [varchar] (4) NULL ,
        [Order Qty Dependent Routing] [varchar] (4) NULL ,
        [RPT Item] [varchar] (4) NULL ,
        [Schedule Code] [varchar] (2) NULL ,
        [RPT Item Order Per.] [varchar] (4) NULL ,
        [Rate Percentage for Planning] [varchar] (4) NULL ,
        [Start Day Planned MPS Orders] [varchar] (4) NULL ,
        [Min. Remaining Percentage] [varchar] (4) NULL ,
        [Planning Method for MPS Orders] [varchar] (4) NULL ,
        [MPS/MRP Order System RPT Items] [varchar] (4) NULL ,
        [Revision-Controlled] [varchar] (4) NULL ,
        [Backflush if Material] [varchar] (4) NULL ,
        [Backflush Materials] [varchar] (4) NULL ,
        [Backflush Hours] [varchar] (4) NULL ,
        [Net Change DRP Status] [varchar] (4) NULL ,
        [Number of Pull Notes] [varchar] (4) NULL ,
        [Quantity by Pull Note] [varchar] (4) NULL ,
        [BOM Unit] [varchar] (4) NULL ,
        [Routing Unit] [varchar] (4) NULL ,
        [Scrap Factor] [varchar] (4) NULL ,
        [Critical in MPS] [varchar] (4) NULL ,
        [Time Fence] [varchar] (4) NULL ,
        [Round Off in MPS] [varchar] (4) NULL ,
        [Net Change MRP] [varchar] (4) NULL ,
        [Low Level Code] [varchar] (4) NULL ,
        [Low Level Code Indicator] [varchar] (4) NULL ,
        [MRP Indicator] [varchar] (4) NULL ,
        [Inventory (at MRP run)] [varchar] (4) NULL ,
        [Purchase Unit] [varchar] (30) NULL ,
        [Purchase Price Unit] [varchar] (6) NULL ,
        [Purchase Price Group] [varchar] (4) NULL ,
        [Purchase Statistics Group] [varchar] (4) NULL ,
        [Purchase Commission Group] [varchar] (2) NULL ,
        [Currency] [varchar] (30) NULL ,
        [Last Purchase Price Transactio] [varchar] (18) NULL ,
        [Purchase Price] [varchar] (36) NULL ,
        [Average Purchase Price] [varchar] (34) NULL ,
        [Latest Purchase Price] [varchar] (34) NULL ,
        [Supplier ID] [varchar] (30) NULL ,
        [Inspection] [varchar] (4) NULL ,
        [Cumulative Purchase Receipts] [varchar] (14) NULL ,
        [Purchase Text] [varchar] (12) NULL ,
        [Sales Unit] [varchar] (6) NULL ,
        [Sales Price Unit] [varchar] (6) NULL ,
        [Price Group] [varchar] (255) NULL ,
        [Sales Statistics Group] [varchar] (4) NULL ,
        [Commission group] [varchar] (14) NULL ,
        [Rebate group] [varchar] (14) NULL ,
        [Price] [varchar] (20) NULL ,
        [Last Sales Price Transaction D] [varchar] (18) NULL ,
        [Suggested Retail Price] [varchar] (4) NULL ,
        [Upper Margin] [varchar] (4) NULL ,
        [Lower Margin] [varchar] (4) NULL ,
        [Commodity Code] [varchar] (18) NULL ,
        [Primary Country of Origin] [varchar] (100) NULL ,
        [Sales Text] [varchar] (4) NULL ,
        [Cost Price Component] [varchar] (60) NULL ,
        [Standard Cost Price] [varchar] (36) NULL ,
        [Material Costs] [varchar] (36) NULL ,
        [Operation Costs] [varchar] (38) NULL ,
        [Cost Price Indicator] [varchar] (8) NULL ,
        [Allocation factor] [varchar] (4) NULL ,
        [Last Cost Price Transaction Da] [varchar] (18) NULL ,
        [Inventory Valuation] [varchar] (4) NULL ,
        [Buyer] [varchar] (100) NULL ,
        [Planner] [varchar] (8) NULL ,
        [Price Policy] [varchar] (4) NULL ,
        [Control Code] [varchar] (30) NULL ,
        [Cost Component] [varchar] (4) NULL ,
        [Interim Results] [varchar] (2) NULL ,
        [Cost Price Type] [varchar] (4) NULL ,
        [Manual Cost Price] [varchar] (4) NULL ,
        [Item Type (Project)] [varchar] (4) NULL ,
        [Process Item] [varchar] (4) NULL ,
        [Container Item y/n] [varchar] (4) NULL ,
        [Container] [varchar] (4) NULL ,
        [Containerized] [varchar] (2) NULL ,
        [Item (2)] [varchar] (4) NULL ,
        [Assayed Item] [varchar] (34) NULL ,
        [Standard Potency] [varchar] (4) NULL ,
        [Frequency for Storage Inspecti] [varchar] (4) NULL ,
        [Quantity Blocked for Storage I] [varchar] (4) NULL ,
        [Order System Containers] [varchar] (4) NULL ,
        [Use Formula Ratio] [varchar] (4) NULL ,
        [Number of Batches per Day] [varchar] (4) NULL ,
        [Inventory Blocked for MPS/MRP] [varchar] (4) NULL ,
        [Default Container for MRP/MPS] [varchar] (4) NULL ,
        [UPC Code] [varchar] (20) NULL ,
        [UCC Code] [varchar] (32) NULL ,
        [Qty per Inner Carton] [varchar] (20) NULL ,
        [Inner Carton Weight] [varchar] (20) NULL ,
        [Inner Carton Length] [varchar] (20) NULL ,
        [Inner Carton Height] [varchar] (20) NULL ,
        [Inner Carton Width] [varchar] (20) NULL ,
        [Number of Inner Cartons] [varchar] (20) NULL ,
        [Master Carton Qty] [varchar] (20) NULL ,
        [Master Carton Weight] [varchar] (20) NULL ,
        [Master Carton Length] [varchar] (34) NULL ,
        [Master Carton Height] [varchar] (20) NULL ,
        [Master Carton Width] [varchar] (20) NULL ,
        [Customer Service Cartons] [varchar] (12) NULL ,
        [Single Unit Carton Weight] [varchar] (4) NULL ,
        [Single Unit Carton Length] [varchar] (10) NULL ,
        [Single Unit Carton Height] [varchar] (10) NULL ,
        [Single Unit Carton Width] [varchar] (10) NULL ,
        [Logo] [varchar] (200) NULL ,
        [Schedule B Tariff Number] [varchar] (50) NULL ,
        [NMFC Code] [varchar] (28) NULL ,
        [Primary Customer] [varchar] (2) NULL ,
        [Blank1] [varchar] (2) NULL ,
        [Blank2] [varchar] (8) NULL ,
        [Packing Information] [varchar] (38) NULL ,
        [Second Country of Origin] [varchar] (100) NULL ,
        [FOB] [varchar] (30) NULL ,
        [Customer Part Number] [varchar] (28) NULL ,
        [Plastic Weight] [varchar] (4) NULL ,
        [Display Pack Weight] [varchar] (4) NULL ,
        [Inner Carton Tare Weight] [varchar] (4) NULL ,
        [Master Carton Tare Weight] [varchar] (4) NULL
) ON [PRIMARY]

> > When I used Bulk Insert, if there is a truncation error on one row,
> > that row AND the row after don't get loaded.  Is there a fix for
> > that?
> > We have SP2 already.

>  That's the same as BCP and expected behaviour

I trust you mean a "known bug" when you say "expected behaviour".
I suppose if the error file generated by the bcp command line
catches 'em both it is passable.

> If a Batch_size limit is set, the insert mis-counts the number of
> rows by 1 for each batch.  Like, if the batch size is 1000, it will
> insert 1000 rows, but read 1001.  It will display "1001 rows
> inserted", and the 1001st row will be missing from the load.  So if
> there are 10,000 total rows, it will drop 9 of them.  Any fix?

>  Haven't seen that.  Does it occur with any batch size?

Batch sizes larger than the total number of rows won't do it.  Other
than that, if there is a batch size that doesn't do it, I haven't seen
it.

--
Yabba Dabba Doo

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

BCP command line / error capturing / bulk insert bugs

Post by Neil Pik » Fri, 02 Jun 2000 04:00:00


Frederick,

Quote:> >  Frederick - I would urge you to open a paid fault case with MS PSS

> Pardon my ignorance.  What's that?

Q.  Who are Microsoft Product Support Services and how do I contact them?

A.  These are people who support all MS's products, write fixes, take the
calls, sort out workarounds etc.  They work 24x7x365.  You can call them by
phone (U.S. is 1-800-936-3500)

For details of your nearest PSS support centre and other info try :-

       http://support.microsoft.com/support/supportnet/default.asp
   http://www.microsoft.com/support/customer/itpro.htm

To report on the Web :-

   http://www.microsoft.com/technet/support/incident.htm
   http://support.microsoft.com/support/webresponse.asp

All incidents are chargeable either to your credit card or an existing account.
 The charge WILL be re-imbursed if the problem turns out to be an Microsoft
bug, or a feature that hasn't been publicly documented.  The person answering
the phone cannot know it's a bug, so they have to take details of your credit
card before passing you on to a technician.  When the call is closed the
technician decides whether to mark the call as "free" in which case you get a
refund automatically.  (Same for Web incidents)

===

v1.02 1999.12.16
Applies to SQL Server versions  : All
FAQ Categories                  : Troubleshooting
Related FAQ articles            : n/a
Related Microsoft Kb articles   : n/a
Other related information       : n/a
Authors                         : Neil Pike

Quote:> >  Please post DDL and a zip with a small amount of data to repro this

> I am not sure of how to attach a zip.    Looks as if I need the x-
> attachments header.  If that's not the right one, what is?

 E-mail it to me.

Quote:> >  That's the same as BCP and expected behaviour

> I trust you mean a "known bug" when you say "expected behaviour".
> I suppose if the error file generated by the bcp command line
> catches 'em both it is passable.

 Nope - expected behaviour.  If you read past the end of a line then you've
started reading the next line.  SQL Server then realises your terminators are
wrong and ignores everything it's read (both lines), finds the next row
terminator, and starts from there again.  You have to get the data in the right
format.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq

 
 
 

BCP command line / error capturing / bulk insert bugs

Post by frederickfl.. » Fri, 02 Jun 2000 04:00:00


Quote:> > >  Frederick - I would urge you to open a paid fault case with MS
> > >  PSS

> > Pardon my ignorance.  What's that?

> Q.  Who are Microsoft Product Support Services and how do I contact

Ohhhh.  You mean call support.  I thought you were suggesting we were
gonna get paid cuz it's their fault :).

Quote:> > >  Please post DDL and a zip with a small amount of data to repro
> > > this

I found a workaround.  I took all spaces and punctuation out of my
column names.  Now it "works".

Ironically, my purpose for doing all this doesn't.  Whenever I specify
an error file on the bcp command line and there is an error, bcp
completely blows up without loading a thing :(.

--
Yabba Dabba Doo

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

BCP command line / error capturing / bulk insert bugs

Post by Neil Pik » Sat, 03 Jun 2000 04:00:00


 Fred,

 > Ohhhh.  You mean call support.  I thought you were suggesting we were

Quote:> gonna get paid cuz it's their fault :).

 Now there's a good idea!  It would certainly encourage people to report bugs!

Quote:> I found a workaround.  I took all spaces and punctuation out of my
> column names.  Now it "works".

 Aha - there had to be a reason why I've never seen this error.  (I never use
spaces/punctuation in column names...)

Quote:> Ironically, my purpose for doing all this doesn't.  Whenever I specify
> an error file on the bcp command line and there is an error, bcp
> completely blows up without loading a thing :(.

 Do you mean BULK INSERT here?

 Please still send me that zip as requested - cheers

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq

 
 
 

BCP command line / error capturing / bulk insert bugs

Post by frederickfl.. » Sat, 10 Jun 2000 04:00:00


Quote:> > Ironically, my purpose for doing all this doesn't.  Whenever I
> > specify an error file on the bcp command line and there is an
> > error, bcp completely blows up without loading a thing :(.

>  Do you mean BULK INSERT here?

Nope.  The Bulk Insert statement inside sql server will not, as far as
I know, let me specify an error file at all.

I definitely mean the command line bcp utility with the /e flag.

Quote:>  Please still send me that zip as requested - cheers

Ok.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. BCP command line / error capturing / bulk insert bugs

I am attemtping to use bcp to load a file from the command line.  I
originally used the Bulk Insert statement -- using the same parameters -
- which loaded the file successfully (except for some bugs, described
below).  When I use the command line, I get

bcp tgitest.dbo.DWLStaging in
ItemMasterDetail.txt /SDelta /Usa /Pourpassword /c /F2

Starting copy...
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
Incorrect syntax near 'varchar'.

What's wrong?

The reason I want to know is I want to capture errors.  The bcp command
line has the /e flag to capture errors to a file.  The Bulk Insert
documentation does not have a simillar option.  If I put "errorfile
= 'whatever'", SQL server does not parse it as an error, but I don't
get a file either (I can put garbage in the filename without it
complaining).  Does anyone know if it can be used?

When I used Bulk Insert, if there is a truncation error on one row,
that row AND the row after don't get loaded.  Is there a fix for that?
We have SP2 already.

If a Batch_size limit is set, the insert mis-counts the number of rows
by 1 for each batch.  Like, if the batch size is 1000, it will insert
1000 rows, but read 1001.  It will display "1001 rows inserted", and
the 1001st row will be missing from the load.  So if there are 10,000
total rows, it will drop 9 of them.  Any fix?

--
Yabba Dabba Doo
--
Yabba Dabba Doo

Sent via Deja.com http://www.deja.com/
Before you buy.

2. Oracle Objects Vs Relational Tables

3. Error when bulk insert follows another large bulk insert

4. unattended installation finish when?

5. BCP Command Line Help Needed (End of File Error)

6. How to show what I've found?

7. BCP and BULK INSERT Error

8. International characters in strings? C'est possible?

9. Capture result of BULK INSERT

10. SQL from command line capturing screen display.

11. Length of BCP command-line from DTS Execute Process Task (RE: DTS: length of BCP command-line in Win32 Process Task -SQL Server 7)

12. Copying bulk data in a command line

13. BCP, BULK command, package & JOB