Results 1 to 16 of 16

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

  1. #1

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

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

    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.

    Code:
    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
    Last edited by dee-u; Aug 26th, 2009 at 01:20 AM.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: Using T-SQL to compress file using Windows compressing utility

    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
    Last edited by si_the_geek; Aug 26th, 2009 at 06:03 AM.

  3. #3

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Using T-SQL to compress file using Windows compressing utility

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


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  4. #4
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: Using T-SQL to compress file using Windows compressing utility

    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

  5. #5

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Using T-SQL to compress file using Windows compressing utility

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


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  6. #6

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Using T-SQL to compress file using Windows compressing utility

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

    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?
    Code:
    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.
    Code:
    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.
    Last edited by dee-u; Aug 27th, 2009 at 02:11 AM.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: Using T-SQL to compress file using Windows compressing utility

    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.
    Code:
    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:
    Code:
    EXEC CompressFile  'C:\test.zip',  'C:\test.jpg'
    Last edited by dee-u; Sep 18th, 2009 at 08:54 PM.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  8. #8
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

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

    i tried your code above in sql management studio express.
    Code:
    EXEC CompressFile  'C:\Documents and Settings\Admin\Desktop\test.zip',  'C:\Documents and Settings\Admin\Desktop\sql backup.txt'
    I get this error messages
    Code:
    (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.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  9. #9

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

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

    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?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  10. #10
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

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

    what suggestions?
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  11. #11

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

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

    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  12. #12
    PowerPoster Simply Me's Avatar
    Join Date
    Aug 2003
    Posts
    2,725

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

    Not yet.
    To give is always to be NOBLE...
    To received is always to be BLESSED....
    Each day strive to be NOBLE
    Each day strive to be BLESSED

    If this post has helped you. Please take time to rate it.

    >=|+|=< Simply Me >=|+|=<

    ----------------------------------------
    Connection Strings | Number Only in Textbox | Splash Screen with Progress Bar | Printing to 1/2 of perforated bond paper |
    Freeze 2005 DataGridView Column

  13. #13

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

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

    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.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  14. #14

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

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

    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.

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


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  15. #15
    New Member
    Join Date
    Mar 2011
    Posts
    1

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

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

  16. #16

    Thread Starter
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

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

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


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width