BUG: BULK INSERT Fails While Inserting into an IMAGE Column

ID: Q197043

The information in this article applies to:
BUG #: 52682 (SQLBUG_70)

SYMPTOMS

When you use BULK INSERT to insert large binary objects (BLOBs) into a SQL Server Table with a column defined as an IMAGE datatype, the insert fails with the following error:

Server: Msg 7399, Level 16, State 1, Line 0
OLE DB provider 'STREAM' reported an error. The provider did
not give any information about the error.
The statement has been terminated.


WORKAROUND

Use BULK INSERT and the WITH (FORMATFILE = '<file_name>') option. The following steps will demonstrate this use.

  1. Determine the length of the file that you want to insert into the SQL Server table with the BULK INSERT statement. To do this, use the MS-DOS dir command:
    
      D:\>dir *.bmp
      Directory of D:\ 
      11/21/98  10:04p  37,255 image_file.bmp
    Note, that the length of this file is 37,255 bytes.


  2. Use Bcp.exe to create a BCP Format file to be used in association with the FORMATFILE option of BULK INSERT.


  3. Alter the length of field for the IMAGE column (c1) to be the same length as the file you are inserting into the SQL Server table. For example, run the following command from an MS-DOS prompt:
    D:\>bcp pubs..image_table out d:\image_table.dat -Sjtknt0 -Usa -P
    Use these values:
    
      Enter the file storage type of field c1 [image]:
      Enter prefix-length of field c1 [4]: 0
      Enter length of field c1 [0]: 37255
      Enter field terminator [none]:

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

      Starting copy...

      0 rows copied.
      Network packet size (bytes): 4096
      Clock Time (ms.): total        1


The SQL Server table image_table had no rows, therefore 0 rows were copied. Bcp.exe was used in this example to generate a correctly formatted BCP Format File. You can use any text editor to create the BCP Format File. Run the BULK INSERT again using the WITH FORMATFILE option from a query window. The following example 

   use pubs
   go
   BULK INSERT pubs..image_table FROM 'd:\image_file.bmp'
      WITH (FORMATFILE = 'd:\image_table.fmt')
   go
   select * from image_table
   go

results in this information being returned: 

   c1
   ------------------------------
   0x310D0A320D0A330D0A[ASCII 133]

    (1 row(s) affected)


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words: prodsql 


Keywords          : SSrvBCP SSrvTran_SQL kbbug7.00 
Version           : WINNT:7.0
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 16, 1999