Click here to Skip to main content
15,742,011 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
-- Image Export Stored Procedure
Alter PROCEDURE dbo.usp_ExportImage 
( @id int
   DECLARE @ImageData varbinary(max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   Declare @File varchar(100);
   SELECT @ImageData = (SELECT --picVarBinary 
          cast(PictureData as VARBINARY (max))
         FROM Pictures
         WHERE id = @id 

	     SELECT @File = picfilename
         FROM Pictures
         WHERE id = @id 
   SET @Path2OutFile = CONCAT (
         , @File
	print @path2outFile
     EXEC sp_OACreate 'ADODB.Stream' ,@Obj OUTPUT;
     EXEC sp_OASetProperty @Obj ,'Type',1;
     EXEC sp_OAMethod @Obj,'Open';
     EXEC sp_OAMethod @Obj,'Write', NULL, @ImageData;
     EXEC sp_OAMethod @Obj,'SaveToFile', NULL, @Path2OutFile, 2;
     EXEC sp_OAMethod @Obj,'Close';
     EXEC sp_OADestroy @Obj;
  EXEC sp_OADestroy @Obj;

What I have tried:

I have SQL Server 2008 images saved in nvarchar(max) column. I am trying to get the image using OLA objects and I am getting image back as well. But When I am trying to open any image, it is saying invalid format.

If anyone can help in that?
Updated 8-Mar-23 2:08am

Don't use NVARCHAR - use VARBINRY (if they are small, less than 64Kbytes) or BLOB (if they are large) instead - or better, store them on a shared folder as files, and store the path in the DB.

NVARCHAR data isn't binary - it's Unicode text, and that means that translation of some values happens when they are stored / retrieved - you do not want that to happen with images as every single bit is important and a trivial change will corrupt the file. Casting data to VARBINARY on retrieval doesn't prevent that happening.

Also check the code which stored them - very often that is the source of problems: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]
Share this answer
shamas saeed 8-Mar-23 2:20am    
Hi, Thank you for quick response

Actually, I got data as it is and the task is to convert it to images. I have tested it using cast and covert as well. Is there anything I can do to get images extracted.
OriginalGriff 8-Mar-23 3:46am    
We can't tell - we have no access at all to your data.

Do a test: use your "INSERT" code to insert a known image, then retrieve it and compare the two data object as binary data. Are they the same? If so, you stand a chance. If not ...
shamas saeed 8-Mar-23 8:08am    
CAST('' AS XML).value('xs:base64Binary(sql:column("picturedata"))'
I got a solution luckily,

CAST('' AS XML).value('xs:base64Binary(sql:column("picturedata"))'
						, 'VARBINARY(MAX)')
Share this answer
Dave Kreskowiak 8-Mar-23 9:39am    
This isn't a solution. It's an ugly workaround for recovering the images that were stored incorrectly in the first place.

If you used that in production where I work, you'd be sent back to redesign the thing.

The real fix is to rewrite the database and code to properly store the images and data in the correct field types.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900