SQL Server Bulk Insert fixed width file failure -


i attempting use bulk insert upload large data file (5m rows). columns varchars no conversion. format file simple...

11.0 29 1   sqlchar     0   8   ""  1   accountid       "" 2   sqlchar     0   10  ""  2   trandate        "" 3   sqlchar     0   4   ""  3   transcode       "" 4   sqlchar     0   2   ""  4   additionalcode      "" 5   sqlchar     0   11  ""  5   currentprincipal    "" 6   sqlchar     0   11  ""  6   currentinterest     "" 7   sqlchar     0   11  ""  7   lateinterest        "" ... 27  sqlchar     0   8   ""  27  operator        "" 28  sqlchar     0   10  ""  28  updatedate      "" 29  sqlchar     0   12  ""  29  timeupdated     "" 

but each time, @ point, same error:

msg 4832, level 16, state 1, line 1 bulk load: unexpected end of file encountered in data file. msg 7399, level 16, state 1, line 1 ole db provider "bulk" linked server "(null)" reported error. provider did not give information error. msg 7330, level 16, state 2, line 1 cannot fetch row ole db provider "bulk" linked server "(null)".

i have tried following:

bulk insert      [tablename] 'datafileppathspecification'     (formatfile = 'formatfileppathspecification') 

but error after 5-6 minutes, , no data has been inserted.

when added batchsize parameter, error after longer time, near end of file, after except few of rows have been inserted successfully.

bulk insert      [tablename] 'datafileppathspecification'     (batchsize = 200,            formatfile = 'formatfileppathspecification') 

when set batchsize 2000 runs faster, (fewer, larger transacxtions assume), still fails.

does have how bulk insert recognizes end of file? if so, need format file fix ?

explicitly state row terminator:

    bulk insert tablename 'path'        (           datafiletype = 'char',           rowterminator = '\r\n'           (formatfile = 'formatfileppathspecification')     ); 

if still fails, check file see if have unexpected terminators embedded in text fields.


Comments

Popular posts from this blog

Unable to open serial port using Putty to bluetooth device on windows 7 64 bit -

bash - How do you programmatically add a bats test? -

java - Getting exception in JDBC thin driver -