[RESOLVED] Using T-SQL to compress file using Windows compressing utility

[dee-u]

Can this be done? I've seen samples that uses winzip but I don't have winzip so I'm wondering if this is doable using the built-in windows compressing utility. This is the VB6 code that does it in Vista, not sure if this is convertible at all to T-SQL. The other option I am looking into is to make a small program/dll that will do it and be called from SQL Server.

Public Sub ZipFile(ByVal strFileToZip As String, ByVal strTargetZip As String, Optional ByVal bolFileType As Boolean = True)
    CreateEmptyZip strTargetZip
    Dim folder      As Folder3
    Dim zipObject   As IShellDispatch5

    Set zipObject = CreateObject("Shell.Application")
    Set folder = zipObject.nameSpace(strTargetZip)

    If bolFileType Then
        folder.CopyHere strFileToZip
    Else
        folder.CopyHere zipObject.nameSpace(strFileToZip).Items
    End If
End Sub

Private Sub CreateEmptyZip(ByVal sPath As String)
    Dim strZIPHeader As String
    
    strZIPHeader = Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String$(18, 0) ' header required to convince Windows shell that this is really a zip file
    CreateObject("Scripting.FileSystemObject").CreateTextFile(sPath).Write strZIPHeader
   
End Sub

[si_the_geek]

It is possible to have VBScript inside jobs etc, so it might work... creating a program will almost certainly work, you can use the SP xp_shell (or something like that!) to run it.

edit: I was fairly close, but no cigar... it is actually xp_cmdshell

[dee-u]

Thanks si. So it does look that my only option is to call another application.

[brucevde]

FYI: SQL Server supports OLE Automation using some system stored procedures.

The link is to BOL for SQL Server 2008 but these sprocs exist in all versions (well maybe not 6.5).

http://msdn.microsoft.com/en-us/library/ms190501.aspx

[dee-u]

Hmmmnnn... Cool, will dig deeper into it. Thanks for the information!

[dee-u]

Some problems when I tried it. For some reason I cannot duplicate this VB6 code:

strZIPHeader = Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String$(18, 0)

Below is my attempt. How could I pass a string with trailing empty characters?

DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @return int
DECLARE @property int

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	PRINT @src
    RETURN
END



EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @property OUT, 'C:\PIS\Test.zip'
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	print @src
	print @desc
    RETURN
END

DECLARE @xx nchar(22)
SET @xx = CHAR(80) + CHAR(75) + CHAR(5) + CHAR(6) + REPLICATE(CHAR(0),18)

PRINT len(@xx)

EXEC @hr = sp_OAMethod @property, 'Write', NULL, @xx
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @property, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	print @src
	print @desc
    RETURN
END

-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
    RETURN
END

And this has been my attempt at instantiating the folder object but I am getting a type mismatch error on the sp_OAMethod. Since NameSpace seem to like variant (as seen ) I even tried declaring @xx as sql_variant to no avail.

DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @return int
DECLARE @property int

EXEC @hr = sp_OACreate 'Shell.Application', @object OUT
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	PRINT @src
    RETURN
END

-- Get a property by calling the method.
EXEC @hr = sp_OAMethod @object, 'NameSpace',  @property OUT, 'C:\Test.zip'
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	print @src
	print @desc
    RETURN
END

Any help will be highly appreciated.

[dee-u]

Okay, here is the finished code which will allow us to compress a file like in Windows. I have made this so that I can compress my database back-up files to conserve hard disk space.

CREATE PROCEDURE CompressFile 
                @ZipFile   VARCHAR(255), 
                @FileToZip VARCHAR(255) 
AS 
  --author: dee-u of www.vbforums.com, 08-28-2009 
  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 -S "(local)" -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'

[Simply Me]

i tried your code above in sql management studio express.

EXEC CompressFile  'C:\Documents and Settings\Admin\Desktop\test.zip',  'C:\Documents and Settings\Admin\Desktop\sql backup.txt'

I get this error messages

(1 row(s) affected)
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online. 
Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. 
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. 
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. 
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. 
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

[dee-u]

Haven't tried it yet with SQL Server Express, only with SQL Server 2000, will check it out and see how it goes. Have you tried the given suggestions?

[Simply Me]

what suggestions?

[dee-u]

The suggestions given in the error messages. I was able to find how to turn on the xp_cmdshell, will look into how to turn on Ole Automation Procedures.

[Simply Me]

Not yet.

[dee-u]

Those can be set in SQL Server 2005 Surface Area Configuration but somehow it does still have an error, will look into it when I have more time.

[dee-u]

Aside from enabling xp_cmdshell and OLE Automation in Surface Area Configuration for Features the other modification needed for the script to work with SQL Express is to specify the server clause.

SET @command = 'bcp "..##DummyTable" out "' + @ZipFile + '" -c -S ".\SQLEXPRESS" -U "' + @username + '" -P "' + @password + '"'

[dickbaua]

Would it be possible to post the Code for uncompress decompress the zip file?

[dee-u]

I believe it is possible but I am busy right now to tweak it, you could play with it yourself.