ZIP files with a stored procedure

A SQL Server stored procedure that zips files from a source and destination directory that you pass when calling the 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
By placing this stored procedure in the Master database, you can access this from any of your databases. The @Result variable can be used to verify that files were zipped sucessfully and then delete the original files. In one database, I actually have this procedure use the XP_SENDMAIL procedure to send an email notifying me if the process fails.

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.

This was first published in April 2003

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close