Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21

    Question Unanswered: Problem with BULK INSERT: Urgent

    Hi All,
    I am creating a file using bcp as:
    bcp "select gdl_id, 2, gdl_name, GDL_DELETE_FLAG from XXXX.dbo.cqt_guideline where gdl_act_id = 18" queryout CQT_GUIDELINE.TXT -n -q -U"x" -P"x"

    and trying load this with another database using:
    BULK INSERT ##CQT_GUIDELINE
    FROM 'C:\Guideline\CQT_GUIDELINE.TXT'
    WITH
    (
    DATAFILETYPE = 'native'
    )
    I am doing this with BULK INSERT instead of bcp again because, its happening inside a sql script file.

    The sql script file is called from command prompt with 'osql'
    like:
    osql -Ux -Px -Sxxx -Dxxxx -i<filename>.sql

    I am getting this error:
    > 135> 136> 137> 138> 139> 140> 141> 142> 143> 144> 145> Msg 4866, Level 17, Sta
    te 66, Server WS10, Line 1
    Bulk Insert fails. Column is too long in the data file for row 1, column 1.
    Make sure the field terminator and row terminator are specified correctly.
    Msg 7399, Level 16, State 1, Server WS10, Line 1
    OLE DB provider 'STREAM' reported an error. The provider did not give any
    information about the error.
    The statement has been terminated.


    While creating text using 'bcp' I am specifying -n 'native' format and in BULK INSERT I am specifying DATAFILETYPE = 'native'. I hope this is right format, I couldnt figure out why this error is comming.

    It will be great and helpfull, if anyone come with early and working answer.
    Thanks in Advance
    Thazul.
    Last edited by thazanm; 01-21-03 at 19:11.

  2. #2
    Join Date
    Jan 2003
    Posts
    8

    Re: Problem with BULK INSERT: Urgent

    When exporting the data specify the column and row break characters, and then specify them in the bulk import command.

    or run a xp_CMDShell 'bcp ...' command from your sql script. This will launch a dos shell and run your bcp command.

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    both your bcp and bulk insert statments look okay. the -n on the bcp command is the counterpart to the DATAFILETYPE = 'native' in bulk load.

    I know this may seem like a silly question, but are the structure of the two tables the same? Native format is VERY unforgiving so the tables need to be the same. If you are still having trouble post the table structures and I will have another go.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    What is the other database server you are exporting to ?

  5. #5
    Join Date
    Jul 2002
    Location
    California, US
    Posts
    21

    Re: Problem with BULK INSERT: Urgent

    Originally posted by thazanm
    Hi All,
    I am creating a file using bcp as:
    bcp "select gdl_id, 2, gdl_name, GDL_DELETE_FLAG from XXXX.dbo.cqt_guideline where gdl_act_id = 18" queryout CQT_GUIDELINE.TXT -n -q -U"x" -P"x"

    and trying load this with another database using:
    BULK INSERT ##CQT_GUIDELINE
    FROM 'C:\Guideline\CQT_GUIDELINE.TXT'
    WITH
    (
    DATAFILETYPE = 'native'
    )
    I am doing this with BULK INSERT instead of bcp again because, its happening inside a sql script file.

    The sql script file is called from command prompt with 'osql'
    like:
    osql -Ux -Px -Sxxx -Dxxxx -i<filename>.sql

    I am getting this error:
    > 135> 136> 137> 138> 139> 140> 141> 142> 143> 144> 145> Msg 4866, Level 17, Sta
    te 66, Server WS10, Line 1
    Bulk Insert fails. Column is too long in the data file for row 1, column 1.
    Make sure the field terminator and row terminator are specified correctly.
    Msg 7399, Level 16, State 1, Server WS10, Line 1
    OLE DB provider 'STREAM' reported an error. The provider did not give any
    information about the error.
    The statement has been terminated.


    While creating text using 'bcp' I am specifying -n 'native' format and in BULK INSERT I am specifying DATAFILETYPE = 'native'. I hope this is right format, I couldnt figure out why this error is comming.

    It will be great and helpfull, if anyone come with early and working answer.
    Thanks in Advance
    Thazul.

    Hi All Gurus,
    Thanks a lot for all of your reply. I really appriciate it.
    This script I am doing to do offline data transfer as a part of my project installation.
    Here is what I am doing. I am populating text file based on certain setup table using bcp in my company database. Inorder to put this data in some other client database I am using sql script due to some validation and massaging on data like duplication checking etc., etc.,. So, I am using bulk insert to populate that data from text file to global temporary tables so that I can read the data from global temporary tables in my sql script(which do all validation before inserting the data into actual table) and inserting into the actual table. [Even I posted a dbforum on 12-10-2002 named 'How to read/select data from a text file in TLSQL ' but I didnt get the right answer or I am unable to follow due my busy schedule].

    This I am doing because, its a offline data transfer with some validations.
    I cant connect to all my client databases to do online data transfer. This script has to go with the product as preinstallation script or factory data generator.

    I hope this will give all of u guys atleast a picture that what I am doing.

    Once again thank you very much for all of ur immediate response.
    Waiting for early reply with solution.
    I am also working out another way r method. If i found one i will post.

    Thazul

    NB: For Paul Young 'I double checked the table structure and data type both are identicle except the source table is permanent table in some user database and the target table is global temporary table in tempdb'

    For rnealejr 'The other database is SQL Server with same configuration with the same name in different server'. In real situation the target database can be any sql server database.

    For mrdenny 'I am crating global temporary table before the bcp, If i use bcp with xp_cmdshell it says 0 rows inserted'

Posting Permissions

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