Professional Web Applications Themes

Problem with BULK INSERT on an empty file - Microsoft SQL / MS SQL Server

Hi All, I have a T-SQL program that uses bulk insert to insert data from a text file into a temporary table. The program works fine unless the bulk insert text file is empty. When the text file is empty, the process bombs out with the following error message:  [/ref] 1. Make sure the field [/ref] information [/ref] will be assumed [/ref] How do I overcome this error?...

  1. #1

    Default Problem with BULK INSERT on an empty file


    Hi All,

    I have a T-SQL program that uses bulk insert to insert data from a text file
    into a temporary table. The program works fine unless the bulk insert text
    file is empty. When the text file is empty, the process bombs out with the
    following error message:
     [/ref]
    1. Make sure the field [/ref]
    information [/ref]
    will be assumed [/ref]


    How do I overcome this error?


    David Guest

  2. #2

    Default Problem with BULK INSERT on an empty file

    I would recommend to check if the file is empty first
    before execute the bulk insert. Obviously, it seems to
    have problem if it can't find any terminator characters.

    You can use VBScript to check whether the file is empty
    and according with the result, you execute the next
    procedure step.


    Edgardo Valdez
    MCSD, MCDBA, MCSE, MCP+I
    http://www.edgardovaldez.us/
     
    data from a text file 
    the bulk insert text 
    bombs out with the [/ref][/ref]
    for row 1, column [/ref][/ref]
    provider did not give any [/ref][/ref]
    char. DataFileType [/ref][/ref]
    signature. [/ref][/ref]
    IRowset::GetNextRows returned [/ref][/ref]
    about the error.]. [/ref]
    >
    >
    >How do I overcome this error?
    >
    >
    >.
    >[/ref]
    Edgardo Guest

  3. #3

    Default Re: Problem with BULK INSERT on an empty file

    Of course I can check the file to find out if it is empty first.
    Unfortunately, that seems not to be the best solution for me. The reason
    is that the stored procedure, which loads the bulk insert file, is a
    completed, sophisticated loading stored procedures, which loads many bulk
    insert files, not just one. Some may have data, some may be empty.

    I can also use the extended stored procedure xp_getfiledetails to find out
    if a specific file is empty. But that cost...the overhead.

    The problem here is, in 6.5 and 7.0, you can bulk insert an empty ANSI file
    without any problem. SQL just simply return "0 row...." messages. With SQL
    2000, you can BULK INSERT an empty UNICODE file without any problem.

    But why does SQL throw bogus error 4866, follow by 7399 when dealing with
    ANSI text file?


    "Edgardo Valdez" <com> wrote in message
    news:012601c36b36$3e8ab6b0$gbl... 
    > data from a text file 
    > the bulk insert text 
    > bombs out with the [/ref]
    > for row 1, column [/ref]
    > provider did not give any [/ref]
    > char. DataFileType [/ref]
    > signature. [/ref]
    > IRowset::GetNextRows returned [/ref]
    > about the error.]. 
    > >
    > >
    > >How do I overcome this error?
    > >
    > >
    > >.
    > >[/ref][/ref]


    David Guest

  4. #4

    Default Re: Problem with BULK INSERT on an empty file


    The "bogus" error message seems to make people think that the file has an
    Unicode identification code at the beginning.

    But yes, the file size is ZERO in length. I can use Notepad to save the
    file with Unicode format and the BULK INSERT will have no problem.

    This is absolutely a new found SQL 2000 bug, in my opinion....


    "Russell Fields" <com> wrote in message
    news:phx.gbl... [/ref]
    reason [/ref]
    bulk [/ref]
    out 
    > file 
    > SQL [/ref]
    with 
    > >
    > >[/ref]
    >
    >[/ref]


    David Guest

Similar Threads

  1. Problem with empty string on insert
    By bpdevlin in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 9th, 09:43 PM
  2. Bulk Insert
    By Mirza in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 8th, 12:25 PM
  3. format file and bcp/bulk insert
    By Robert Taylor in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 7th, 11:26 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139