Trick: Compress file using SQL Server

[dee-u]

I just want to share this little snippet which could be used to compress a file using SQL Server. I have made this so that I can compress my database back-up files to conserve hard disk space. I thought this could come in handy to somebody else.

CREATE PROCEDURE CompressFile 
                @ZipFile   VARCHAR(255), 
                @FileToZip VARCHAR(255) 
AS 
  --author: dee-u of CodeGuru, vbforums
  DECLARE  @hr           INT, 
           @folderObject INT, 
           @shellObject  INT, 
           @src          VARCHAR(255), 
           @desc         VARCHAR(255), 
           @command      VARCHAR(255), 
           @password     VARCHAR(255), 
           @username     VARCHAR(255) 
   
  SET @username = 'username'    
  SET @password = 'password' 
   
  --Create table to save dummy text to create zip file 
  CREATE TABLE ##DummyTable ( [DummyColumn] [VARCHAR](255)) 
   
  --header of a zip file 
  DECLARE  @zipHeader VARCHAR(22)    
  SET @zipHeader = CHAR(80) + CHAR(75) + CHAR(5) + CHAR(6) + REPLICATE(CHAR(0),18) 
   
  --insert zip header 
  INSERT INTO ##DummyTable (DummyColumn) VALUES (@zipHeader) 
   
  --save/create target zip 
  SET @command = 'bcp "..##DummyTable" out "' + @ZipFile + '" -c -U "' + @username + '" -P "' + @password + '"'    
  EXEC MASTER..xp_cmdshell @command 
   
  --Drop used temporary table 
  DROP TABLE ##DummyTable 
   
  --get shell object 
  EXEC @hr = sp_OACreate 
    'Shell.Application' , 
    @shellObject OUT 
   
  IF @hr <> 0 
    BEGIN 
      EXEC sp_OAGetErrorInfo @shellObject , @src OUT , @desc OUT 
       
      SELECT hr = convert(VARBINARY(4),@hr), 
             Source = @src, 
             DESCRIPTION = @desc 
       
      RETURN 
    END 
   
  --get folder 
  SET @command = 'NameSpace("' + @ZipFile + '")'    
  EXEC @hr = sp_OAMethod @shellObject , @command , @folderObject OUT    
  IF @hr <> 0 
    BEGIN 
      EXEC sp_OAGetErrorInfo @shellObject , @src OUT , @desc OUT 
       
      SELECT hr = convert(VARBINARY(4),@hr), 
             Source = @src, 
             DESCRIPTION = @desc 
       
      RETURN 
    END 
   
  --copy file to zip file 
  SET @command = 'CopyHere("' + @FileToZip + '")'    
  EXEC @hr = sp_OAMethod @folderObject , @command 
  IF @hr <> 0 
    BEGIN 
      EXEC sp_OAGetErrorInfo @folderObject , @src OUT , @desc OUT 
       
      SELECT hr = convert(VARBINARY(4),@hr), 
             Source = @src, 
             DESCRIPTION = @desc 
       
      RETURN 
    END 
   
  --Destroy the objects used. 
  EXEC sp_OADestroy @shellObject    
  EXEC sp_OADestroy @folderObject 

GO

Sample usage:

EXEC CompressFile  'C:\test.zip',  'C:\test.jpg'