Microsoft KB Archive/271344

= BUG: An Error Message May Occur with the bcp Utility and BULK INSERT When You Load IMAGE Data Into Tables =

Article ID: 271344

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q271344



BUG #: 235567 (SHILOH_BUGS)



SYMPTOMS
If you try to insert a binary large object (BLOB), such as a Microsoft Word document, into a table that uses the bulk copy program or BULK INSERT, one or both of the following error messages may occur:

Server: Msg 4832, Level 16, State 1, Line 1 Bulk Insert: Unexpected end-of-file (EOF)

encountered in data file.

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'STREAM' reported an error.

The provider did not give any information about the error. The statement has been terminated.



CAUSE
The error messages occur because both the bulk copy program and BULK INSERT assume that the file being used is a delimited file.



WORKAROUND
Edit the format file and remove all references to the rest of the data columns in the table from the format file as shown in the example in the &quot;More Information&quot; section.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
The bcp utility provides a simple way of producing a standard format file for use with the bcp utility or the BULK INSERT Transact-SQL command. However, when you attempt to load a non-delimited file into a table, the format file must be manually edited so that it will ignore other columns in the table.

You can use the steps that follow as a workaround for this problem:

  Create a table as follows: USE pubs GO CREATE TABLE files ( file_id int IDENTITY(1,1), data image )  Assume that the user wants to use a Word document. For example, C:\Test.doc. The size of Test.doc is 174080 bytes.  Use the bulk copy program to generate a standard format file as follows: C:\bcp pubs..files in test.doc -Sserver_name\instance_name -Usa -P

Enter the file storage type of field file_id [int]: Enter prefix-length of field file_id [0]: Enter field terminator [none]:

Enter the file storage type of field data [image]: Enter prefix-length of field data [4]: 0 Enter length of field data [0]: 174080 Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] y Host filename [bcp.fmt]:

Starting copy... SQLState = S1000, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total      16   The standard format file as is will be as follows: 8.0 2 1      SQLINT        0       4       &quot;&quot;                        1     file_id     &quot;&quot; 2      SQLIMAGE      0       174080  &quot;&quot;                        2     data        &quot;&quot; </li>  Now, remove all the references to other data columns except for the IMAGE data column, which results in the following format file: <pre class="fixed_text">8.0 1 1      SQLIMAGE      0       174080  &quot;&quot;                        2     data        &quot;&quot; </li>  You may now use the bcp command line utility, with the preceding modified format file to bulk load the binary large object: C:\bcp pubs..files in test.doc -Sserver_name\instance_name -Usa -P -f bcp.fmt

Starting copy...

1 rows copied. Network packet size (bytes): 4096 Clock Time (ms.): total      94 </li>  You may also use the BULK INSERT command to load the BLOB objects as follows: BULK INSERT pubs..files FROM 'C:\test.doc' WITH ( formatfile = 'C:\bcp.fmt') </li>  You may optionally change the standard format file as follows and use the bcp utility: <pre class="fixed_text">8.0 2 1      SQLINT        0       0       &quot;&quot;                        0     file_id     &quot;&quot; 2      SQLIMAGE      0       174080  &quot;&quot;                        2     data        &quot;&quot; </li></ol>

Keywords: kbbug kbcodesnippet kbpending KB271344

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.