Click here to Skip to main content
15,886,362 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
)
AS
BEGIN
   DECLARE @ImageData varbinary(max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT;
   Declare @File varchar(100);
 
   SET NOCOUNT ON
 
   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 (
         --'\\AW-FOS-SQL1-UT\New_folder'
		 'G:\SM_RS'
		,'\'
         , @File
         );
	print @path2outFile
    BEGIN TRY
     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;
    END TRY
    
 BEGIN CATCH
  EXEC sp_OADestroy @Obj;
 END CATCH
 
   SET NOCOUNT OFF
END
GO


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?
Posted
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
 
Comments
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"))'
, 'VARBINARY(MAX)')
I got a solution luckily,



CAST('' AS XML).value('xs:base64Binary(sql:column("picturedata"))'
						, 'VARBINARY(MAX)')
 
Share this answer
 
Comments
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