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

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:

1. Create a table as follows:

   USE pubs
   GO
   CREATE TABLE files
   (
    file_id int IDENTITY(1,1),
    data image
   )
   						

2. Assume that the user wants to use a Word document. For example, C:\Test.doc. The size of Test.doc is 174080 bytes.
3. 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
   						

4. The standard format file as is will be as follows:

   8.0
   2
   1       SQLINT        0       4       ""                        1     file_id     ""
   2       SQLIMAGE      0       174080  ""                        2     data        ""
   						

5. Now, remove all the references to other data columns except for the IMAGE data column, which results in the following format file:

   8.0
   1
   1       SQLIMAGE      0       174080  ""                        2     data        ""
   						

6. 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
   						

7. 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')
   						

8. You may optionally change the standard format file as follows and use the bcp utility:

   8.0
   2
   1       SQLINT        0       0       ""                        0     file_id     ""
   2       SQLIMAGE      0       174080  ""                        2     data        ""