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_outputBy 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.