ZIP files with a stored procedure

Here is a simple SQL Server stored procedure that zips files from a source and destination directory that you pass...

when calling the procedure. It uses the DOS-based wzzip.exe to zip files specified into a zip file named the current date and time, e.g., 200303030809.zip. You must have the wzzip.exe file in the path you supply for the @ZIPEXE parameter. You could optionally remove this parameter and hard code the path to the wzzip.exe or add the wzzip.exe path to an environment variable.

ALTER PROCEDURE dbo.SP_ZIPFAXFILES (@ZIPEXE VARCHAR(8000),@SOURCE VARCHAR(8000), @DEST VARCHAR(8000))
AS
DECLARE @WINZIP VARCHAR(8000),
        @ZipName VARCHAR(8000),
        @DateConvert SMALLDATETIME,
        @Result INT
SET @DateConvert = GETDATE() --Declare the current date time so that
                             --conversions happen at the same date time
SET @ZipName = CONVERT(VARCHAR(10), @DateConvert, 112) +  --Add the date to the file name 
    SUBSTRING(CONVERT(VARCHAR(10), @DateConvert, 108), 1,2) + --add the hour to the file name
    SUBSTRING(Convert(varchar(10), @DateConvert, 108), 4,2) + --add the minute to the file name
    + '.ZIP'
SET @Dest = @ZIPEXE + ' ' + @DEST + @ZipName 
SET @WINZIP = @Dest + ' ' + @Source
EXEC @Result = master.dbo.XP_CMDSHELL @Winzip, no_output

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free SQL Server tips and scripts.
• Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
• Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.