BUG: BULK INSERT Fails While Inserting into an IMAGE Column (197043)



The information in this article applies to:

  • Microsoft SQL Server 7.0

This article was previously published under Q197043
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.

Modification Type:MajorLast Reviewed:10/15/2002
Keywords:kbBug KB197043