-
Aug 26th, 2009, 01:10 AM
#1
[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.
-
Aug 26th, 2009, 05:50 AM
#2
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.
-
Aug 26th, 2009, 03:20 PM
#3
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.
-
Aug 26th, 2009, 04:43 PM
#4
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
-
Aug 26th, 2009, 05:10 PM
#5
Re: Using T-SQL to compress file using Windows compressing utility
Hmmmnnn... Cool, will dig deeper into it. Thanks for the information!
-
Aug 27th, 2009, 12:32 AM
#6
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.
-
Aug 28th, 2009, 08:51 AM
#7
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.
-
Sep 7th, 2009, 08:11 PM
#8
PowerPoster
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.
-
Sep 7th, 2009, 08:13 PM
#9
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?
-
Sep 7th, 2009, 08:39 PM
#10
PowerPoster
Re: [RESOLVED] Using T-SQL to compress file using Windows compressing utility
-
Sep 7th, 2009, 09:08 PM
#11
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.
-
Sep 7th, 2009, 09:11 PM
#12
PowerPoster
Re: [RESOLVED] Using T-SQL to compress file using Windows compressing utility
-
Sep 7th, 2009, 09:26 PM
#13
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.
-
Sep 18th, 2009, 08:45 PM
#14
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 + '"'
-
Mar 7th, 2011, 05:55 AM
#15
New Member
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?
-
Mar 8th, 2011, 01:14 AM
#16
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|