Microsoft KB Archive/197043

= BUG: BULK INSERT Fails While Inserting into an IMAGE Column =

Article ID: 197043

Article Last Modified on 10/15/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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 = '') option. The following steps will demonstrate this use.

  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.  Use Bcp.exe to create a BCP Format file to be used in association with the FORMATFILE option of BULK INSERT.  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)

<div class="status_section">

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

Additional query words: prodsql

Keywords: kbbug kbpending KB197043

-

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

© Microsoft Corporation. All rights reserved.