Sql-server – SQL Server 2000 Hitting 8000 Byte Limit Exporting BLOB

sql serversql-server-2000

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.

Best Answer

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.

Related Solutions

Sql-server – Monitor Sql Server 2000

The Zabbix tool, posted in the comment, is not really working, because the server doesn't run on Windows, only the agent. See info in their requirements page. This means you'd need to install the tool on a Linux machine and only an agent on the Windows machine. Great if you have a heterogeneous environment, not really helpful in a full Win environment.

The same situation is for Nagios, another interesting open source monitoring tool. They say there is a package only for Windows, but the doc page it redirects to doesn't work now.

Of complete free monitoring tools useful for SQL 2000 I'm not really aware, but you can use successfully a pair of:

Perfmon (find more info here)
Server trace (trace specific queries, not just everything) -> actually files that can be open in SQL Profiler
SQL Alerts (info in MSDN article or here, in this SimpleTalk article)

PS: found that a great monitoring tool is also working on SQL 2000. So you'd better try to test Confio Ignite free. It's fantastic for the price :-).

PS2: there is also a small free monitoring tool from Idera -> SQL Check, but I don't know if it works on SQL 2000.

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Best Answer

Related Solutions

Sql-server – Monitor Sql Server 2000

Sql-server – Handling exceptions in stored procedures called using insert-exec blocks

Related Question