SQL Server 2000 Hitting 8000 Byte Limit Exporting BLOB 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.
You should be using the Bulk Copy Program (BCP), a command-line utility that ships
with SQL Server 2000, for importing and exporting large amounts of data in
SQL Server 2000 databases.

For more information see the Database Journal article
Utilize BCP with SQL Server 2000.