BCP exported image is not valid

• Atif

  Hall of Fame

  Points: 3578

  More actions

  May 9, 2012 at 1:36 am

  #273751

  I have exported various images from Image type column through bcp. The resultant files show valid size but could not be opened due to corrupt image format.

  I have used following bcp command inside a cursor to export the images

  'bcp "SELECT empimage FROM HRIS_R2.dbo.tblstaff WHERE empno = ''' + @PhotoID + '''" queryout "D:\data\images\' +@PhotoID + '" -n -Usa -Ppwd -SHRIS519\SSR2'

  Any idea about the reason and solution?

  Thanks

  DBDigger Microsoft Data Platform Consultancy.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  May 10, 2012 at 3:17 pm

  #1486004

  This sample (adapted from here)worked for me:

  USE tempdb

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  SET ANSI_PADDING ON

  GO

  IF EXISTS ( SELECT *

  FROM sys.objects

  WHERE object_id = OBJECT_ID(N'[dbo].[ProductImage]')

  AND type IN (N'U') )

  DROP TABLE [dbo].[ProductImage];

  GO

  CREATE TABLE [dbo].[ProductImage]

  (

  [ProductImageID] [int] IDENTITY(1, 1)

  NOT NULL,

  [ProductDESC] [varchar](50),

  [ProductIMG] IMAGE NOT NULL,

  PRIMARY KEY CLUSTERED ([ProductImageID] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

  );

  GO

  -- import JPG into table

  INSERT dbo.ProductImage

  (

  ProductDESC,

  ProductIMG

  )

  SELECT 'Comment pict1', -- ProductDESC

  BulkColumn -- ProductIMG

  FROM OPENROWSET(BULK 'C:\@\1.jpg', SINGLE_BLOB) AS ExternalFile

  GO

  EXEC master..xp_cmdshell

  'bcp "SELECT ProductIMG FROM tempdb.dbo.ProductImage WHERE ProductImageID = 1" queryout c:\@\2.jpg -n -T -S .\STD2008R2';

  GO

  I could get a picture from my machine into and out of the table and the output version opened fine.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  May 10, 2012 at 6:01 pm

  #1486049

  AShehzad (5/9/2012)

  ---

  I have exported various images from Image type column through bcp. The resultant files show valid size but could not be opened due to corrupt image format.

  I have used following bcp command inside a cursor to export the images

  ...snip...

  Any idea about the reason and solution?

  Thanks

  Dude! Go blot out the password you posted!

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)