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.
Code:
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:
Code:
EXEC CompressFile 'C:\test.zip', 'C:\test.jpg'