I'm trying to help out my security by exporting user data from a legacy access system. The back end is SQL server 2000. The data I'm particularly interested in is the user photograph, which is stored as a BLOB in the database.
With a bit of Googling I found a stored procedure to help with this, but when I tried to create it I was getting errors when declaring a variable as
DECLARE @ImageData VARBINARY (max)
A similar error occurred in this line;
SELECT convert (VARBINARY (max), Face, 1)
I was getting an error stating "max" was invalid. After a bit more Googling I realised that server 2000 wouldn't accept "max" and instead I used 8000, which is the maximum varbinary size.
My export worked to some extent, in that the image data exported, but only the first 8KB. Obviously this is due to the maximum varbinary size being 8000. the images them selves are OK for the top of the picture, but then just flat grey.
Is there a way to get the whole picture out? the stored procedure I'm using is as follows;
CREATE PROCEDURE dbo.JB_Export_Photos (
@PicName NVARCHAR (100) -- name of picture in db USE cardID
,@ImageFolderPath NVARCHAR(1000) -- name of folder which will be saved to
,@Filename NVARCHAR(1000) -- filename after export add .jpg
)
AS
BEGIN
DECLARE @ImageData VARBINARY (max);
DECLARE @Path2OutFile NVARCHAR (2000);
DECLARE @Obj INT
SET NOCOUNT ON
SELECT @ImageData = (
SELECT convert (VARBINARY (max), Face, 1)
FROM picTable-- needs changing to real database name
WHERE picturename = @PicName -- 'pictureName' needs changing to match field in db, probably cardID
);
SET @Path2OutFile = CONCAT (
@ImageFolderPath
,'\'
, @Filename
);
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
Thanks in advance.
