none
Issue with BULK Insert in SQL 2005

    Question

  • Hi,

    I have created a SQL Job in SQL 2005 to load a text file in database table, to load data in table i am using "BULK INSERT" command as follow

    BULK INSERT Staging_Data..tire_wheel_raw FROM '\\ftp\XXX_Wheel.TXT'

    Table definition where bulk data is loaded.

    Table definition where Bulk data got loaded

    But i am getting below error as soon as it reach Bulk insert step, error below

    Executed as user: xxxxxx. The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly. [SQLSTATE 42000] (Error 4866)  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399)  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". [SQLSTATE 42000] (Error 7330).  The step failed.

    But, when I open same file in "WordPad" and without doing any changes in file, just save it and the try to load the data it is loading successfully, using same above command in SQL job step.

    Please help me to understand what is the issue, is their any specific file encoding/format SQL job needs to process the file or is there any parameter i can pass to load the file.

    Thanks & Regards,
    Israq.


    Thanks in advance, ----------------------------- Israq Khan



    • Edited by Israq Khan Wednesday, November 06, 2013 6:51 AM
    Wednesday, November 06, 2013 6:47 AM

Answers

  • But, when I open same file in "WordPad" and without doing any changes in file, just save it and the try to load the data it is loading successfully, using same above command in SQL job step.

    Then I guess the row terminator is a "none-Windows" standard. Windows uses usually CR+LF, some Unix and other system uses only LF or only CR. When you save the file with Wordpad, it changes the new line chars.

    In the BULK INSERT command you can define the row terminator sign; see BULK INSERT (Transact-SQL) => ROWTERMINATOR


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Wednesday, November 06, 2013 7:01 AM
    • Marked as answer by Israq Khan Wednesday, November 06, 2013 7:27 AM
    Wednesday, November 06, 2013 7:00 AM
  • Thanks Olaf,

    It helped, actually the file was created in Unix system, hence need to put below code to rectify my issue.

    with (ROWTERMINATOR = '0x0a')


    Thanks in advance, ----------------------------- Israq Khan

    • Marked as answer by Israq Khan Wednesday, November 06, 2013 7:27 AM
    Wednesday, November 06, 2013 7:27 AM

All replies

  • But, when I open same file in "WordPad" and without doing any changes in file, just save it and the try to load the data it is loading successfully, using same above command in SQL job step.

    Then I guess the row terminator is a "none-Windows" standard. Windows uses usually CR+LF, some Unix and other system uses only LF or only CR. When you save the file with Wordpad, it changes the new line chars.

    In the BULK INSERT command you can define the row terminator sign; see BULK INSERT (Transact-SQL) => ROWTERMINATOR


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Wednesday, November 06, 2013 7:01 AM
    • Marked as answer by Israq Khan Wednesday, November 06, 2013 7:27 AM
    Wednesday, November 06, 2013 7:00 AM
  • Thanks Olaf,

    It helped, actually the file was created in Unix system, hence need to put below code to rectify my issue.

    with (ROWTERMINATOR = '0x0a')


    Thanks in advance, ----------------------------- Israq Khan

    • Marked as answer by Israq Khan Wednesday, November 06, 2013 7:27 AM
    Wednesday, November 06, 2013 7:27 AM