Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: BULK INSERT failing with truncation error...

    Howdy y'all, what the hey am I doing wrong here?

    I am trying to suck in a HUGE flat file that is tab-delimited and each row ends with a hex :0D:0A.

    The first few lines of the file are:
    Code:
    00000000h: 31 30 30 30 32 09 32 30 30 33 2D 30 31 2D 32 39 ; 10002.2003-01-29
    00000010h: 20 30 30 3A 30 30 3A 30 30 2E 30 30 30 09 32 30 ;  00:00:00.000.20
    00000020h: 2E 33 39 30 30 09 31 39 2E 38 30 30 30 09 32 30 ; .3900.19.8000.20
    00000030h: 2E 33 34 30 30 09 34 32 31 33 37 09 31 2E 30 30 ; .3400.42137.1.00
    00000040h: 30 30 0D 0A 31 30 30 30 32 09 32 30 30 33 2D 30 ; 00..10002.2003-0
    00000050h: 31 2D 33 30 20 30 30 3A 30 30 3A 30 30 2E 30 30 ; 1-30 00:00:00.00
    00000060h: 30 09 32 30 2E 33 35 30 30 09 31 39 2E 38 30 30 ; 0.20.3500.19.800
    00000070h: 30 09 31 39 2E 38 37 30 30 09 33 33 39 33 33 09 ; 0.19.8700.33933.
    here is my table script:
    Code:
    CREATE TABLE [dbo].[HSF_Staging_TEST] (
    	[OSID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Date] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Time] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[High] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Low] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Price] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Volume] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Splits] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    Here is my BULK INSERT statement:
    Code:
    osql -S(local) -Uusername -Ppassword -Q "BULK INSERT Trades.dbo.HSF_Staging_Test FROM '\\devserver\inputfiles\Data\Data\HSF.txt' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', TABLOCK)" -o".\HSF_Staging_Test_LOG.txt" -e".\HSF_Staging_Test_ERR.txt"
    yeah, dang near perfect code, eh?

    well...here is the error I get...
    Code:
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 1, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 2, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 3, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 4, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 5, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 6, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 7, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 8, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 9, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 10, column 8
    (Splits).
    Msg 4863, Level 16, State 1, Server TRADES, Line 1
    Bulk insert data conversion error (truncation) for row 11, column 8
    (Splits).
    Msg 4865, Level 16, State 1, Server TRADES, Line 1
    Could not bulk insert because the maximum number of errors (10) was
    exceeded.
    Msg 7399, Level 16, State 1, Server TRADES, Line 1
    OLE DB provider 'STREAM' reported an error. The provider did not give
    any information about the error.
    OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
    returned 0x80004005:  The provider did not give any information about
    the error.].
    The statement has been terminated.
    Is the problem something about the :OD:0A at the end of each row, rather than just the :0A??? If so, how the heck do I specify that? I think based on my testing so far (also tried '\r\n' as the rowtermination param, but then it gives me a truncation error for the first row only, leading me to think it cannot find the end of the row that way.

    I am still looking through archives and on the web, but have not seen anything specific to my issue yet...and cannot believe that I am the first to BULK INSERT this kind of data.

    Help is appreciated!
    ~Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    ummm...nevermind...sorry...*hanging head*

    My table definition has an extra column in it...the TIME, which I had inserted early in the process before I knew the difference in the file between spaces and the TAB character...

    *sigh*

    Sorry if I forced the unnecessary use of brain cells... *blush*
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •