BCP exported image is not valid

Question

Post reply

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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

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: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions