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

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)