Convert a file into zip using Stored Procedure in SQL
In this we can conver file into zip format using SQL and WINRAR and BATCH command. there are lot of ways to do this. but the simplest way is using 'XP_CMDShell' command..
xp_CMDShell can execute the shell/system command which includes the batch file.
Step1:- First you need to create on .bat file, Just copy below line and open notepad paste it and save as .bat file
""C:\PROGRAM FILES\WinRar\rar.exe" a "c:\sample\sample.zip" "c:\Test\""
Note: - only make sure the rar.exe must be available in specified directory.
a – use for append file to zip
2nd parameter specifies the path and filename of zip/rar, which you want to, create.
3rd and last parameter specifies the path and folder name which you want to create zip file. You may specify the file name if you want to zip of single file instead of whole folder.
Step2:- Create one stored proc which run that bat file
Create PROCEDURE CreateZipFile
AS
BEGIN
DECLARE @SQL VARCHAR(2000)
SET @SQL =''
SET @SQL = 'c:\rar.bat' --the correct path of bat file
PRINT @SQL
EXEC MASTER..XP_CMDSHELL @SQL
END
GO
Step3:- Finally you run the stored procedure to create zip/rar file as follows
EXEC(CreateZipFile)