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.
相关内容
