Forum Discussion

wasim_ahmad's avatar
wasim_ahmad
Copper Contributor
Jul 29, 2020

stored procedure to fetch image data from sql server 2005 and save to file

syntax error 

 

perty @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
CREATE PROCEDURE dbo.usp_ExportImage (   @PicName NVARCHAR (100)
   ,@ImageFolderPath NVARCHAR(1000)
   ,@Filename NVARCHAR(1000)
   )
AS
BEGIN
   DECLARE @ImageData VARBINARY (max);
   DECLARE @Path2OutFile NVARCHAR (2000);
   DECLARE @Obj INT
 
   SET NOCOUNT ON
 
   SELECT @ImageData = (
         SELECT convert (VARBINARY (max), PictureData, 1)
         FROM Pictures
         WHERE pictureName = @PicName
         );
 
   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

 

  • wasim_ahmad 

    change the concatenation got syntax error near ','

    SET @Path2OutFile =  +" - "(         @ImageFolderPath
    ,'\'
             , @Filename
             );
     

     

  • wasim_ahmad's avatar
    wasim_ahmad
    Copper Contributor

    wasim_ahmad 

    change the concatenation got syntax error near ','

    SET @Path2OutFile =  +" - "(         @ImageFolderPath
    ,'\'
             , @Filename
             );
     

     

Resources