how to Zip a file
-
Hi
I had transfered a table data to a text file through BCP and the size of the file is 2GB now i want to zip it.
is there anyway to zip the file from sql server.
thanx in advance
from
killer
-
This should give you something to work with
Declare @cmdstr varchar(8000)
Set @cmdstr = 'pkzipc -add '+ @zippedfile +' ' + @srcfile
exec master..xp_cmdshell @cmdstr
GO
-
Attached are procedures that I use to zip files from SQL Server. The following link is another procedure that I use to perform a backup and zip the file in one step. These all use WINZIP 9 with the Command Line interface installed.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=204371#bm204943
Eric
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[egm_zip_file]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[egm_zip_file]
GO
create procedure egm_zip_file
@File_to_Zip varchar (1000)= null
,@Zipped_File varchar(1000) = null output
,@ZipProgram varchar(500) = null
,@RemoveFile tinyint = null
,@Encrypt tinyint = null
,@Password varchar(50) = null
,@TempLocation varchar (500) = null
as
-- Author: Eric Mueller
-- Date Written: 05/10/2005
--
-- Return Codes: 0 - success
-- 1 - failure
-- Accepts the following parameters:
-- @File_to_Zip - The name of the file to be zipped
-- @Zipped_File - (optional) if not supplied then the file will be named as
-- (@File_to_Zip.zip)
-- @ZipProgram - (optional) path and .exe for the zip program, will default to
-- C:\WINZIP\WZZIP if not otherwise supplied
-- @RemoveFile - (optional) default to 0, if 1 then remove the original file when done
-- @Encrypt - (optional) default to 0, this is the same as no encryption; if a password
-- is supplied this will default to 1.
-- 0 - no encryption
-- 1 - standard Zip 2.0 encryption (password required)
-- 2 - AES128 encryption (password required)
-- 3 - AES256 encryption (password required)
-- @Password - (optional) password for zip file NOTE: THIS MUST BE RETAINED IN A SAFE
-- PLACE, IF IT IS USED THERE IS NO WAY OF RECOVERING THE PASSWORD.
-- @TempLocation -- (optional) drive/path. Use another drive for the temporary Zip file.
-- Since WZZIP creates a new temporary Zip file when it updates
-- a file, sufficient space must be available on the current drive
-- for that file.
set nocount on
-------------------------------------------------------------------------------
-- Declare and initialize Variables
-------------------------------------------------------------------------------
declare @SQL varchar (4000)
declare @rtnError int -- This will be the error code for returning from this procedure
declare @processError int -- This will be used for capturing errors from called routines
declare @CommandString varchar (500)
declare @CharIndex int
set @rtnError = 0
set @processError = 0
set @CommandString = ''
-------------------------------------------------------------------------------
-- Validate and assign input values
-------------------------------------------------------------------------------
-- validate database name
if @File_to_Zip is null
begin
-- database name is required
set @rtnError = 1
goto End_of_Logic
end
else
begin
-- validate existence to file
create table #fileexists (
doesexist smallint
,fileindir smallint
,direxist smallint)
-- Insert into the temporary table
Insert into #fileexists exec master..xp_fileexist @File_to_Zip
--Queries the temporary table to see if the file exists
If not exists (select doesexist from #fileexists
where doesexist = 1)
Begin
-- File does not exist
set @rtnError = 1
goto End_of_Logic
End
-- Clean up variable
drop table #fileexists
end
-- Set a value for the Zip program location
if @ZipProgram is null
set @ZipProgram = 'C:\WINZIP\WZZIP'
-- Set a value for the remove backup flag
if @RemoveFile is null
set @RemoveFile = 0
-- validate password and encryption and build command parameters
if @Encrypt is null
set @Encrypt = 0
if @Encrypt > 3
begin
-- Invalid encryption request
set @rtnError = 1
goto End_of_Logic
end
if @Encrypt <> 0
begin
if (@Password is null or @Password = '')
begin
-- password is required if encrytpion is requested
set @rtnError = 1
goto End_of_Logic
end
else
begin
-- encyrption is requested and password is ok
set @CommandString = '-s' + @Password + case @Encrypt
when 2 then ' -ycAES128'
when 3 then ' -ycAES256'
else ''
end
end
end
-- add location for temporary zip file (if requested)
if @TempLocation is not null
select @CommandString = @CommandString + '-b' + @TempLocation
-- Set backup name and zip name
if @Zipped_File is null
begin
select @CharIndex = CHARINDEX('.', @File_to_Zip)
select @Zipped_File =
case @CharIndex
when 0 then @File_to_Zip + '.zip'
else
substring (@File_to_Zip,1,@charindex) + 'zip'
end
end
-- Build and execute ZIP logic
set @SQL = @ZipProgram + ' ' + @Zipped_File + ' ' + @File_to_Zip
if @CommandString is not null
begin
set @SQL = @SQL + ' ' + @CommandString
end
exec @processError = master..xp_cmdshell @SQL,no_output
if @processError <> 0
begin
set @rtnError = 1
goto End_of_Logic
end
-- Remove original file if requested
if @RemoveFile = 1
begin
set @SQL = 'del ' + @File_to_Zip
exec @ProcessError = master..xp_cmdshell @SQL, no_output
if @processError <> 0
begin
set @rtnError = 1
goto End_of_Logic
end
end
-- End_of_Logic
End_of_Logic:
return @rtnError
go
--*****************************************************************************
-- Create UnZip Procedure
--*****************************************************************************
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[egm_unzip_file]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[egm_unzip_file]
GO
create procedure egm_unzip_file
@File_to_unZip varchar (1000)= null
,@unZipProgram varchar(500) = null
,@unZip_Dir varchar(1000) = null
-- ,@RemoveFile tinyint = null
-- ,@Encrypt tinyint = null
-- ,@Password varchar(50) = null
-- ,@TempLocation varchar (500) = null
as
--Author: Eric Mueller
--Date Created: 05/17/2005
-- The unzip directory will need to default to the current directory.
declare @SQL varchar (4000)
declare @rtnError int -- This will be the error code for returning from this procedure
declare @processError int -- This will be used for capturing errors from called routines
declare @CommandString varchar (500)
declare @CharIndex int
set @rtnError = 0
set @processError = 0
set @CommandString = ''
-------------------------------------------------------------------------------
-- Validate and assign input values
-------------------------------------------------------------------------------
-- validate database name
if @File_to_unZip is null
begin
-- database name is required
set @rtnError = 1
goto End_of_Logic
end
else
begin
-- validate existence to file
create table #fileexists (
doesexist smallint
,fileindir smallint
,direxist smallint)
-- Insert into the temporary table
Insert into #fileexists exec master..xp_fileexist @File_to_unZip
--Queries the temporary table to see if the file exists
If not exists (select doesexist from #fileexists
where doesexist = 1)
Begin
-- File does not exist
set @rtnError = 1
goto End_of_Logic
End
-- Clean up variable
drop table #fileexists
end
-- Set a value for the Zip program location
if @unZipProgram is null
set @unZipProgram = 'C:\WINZIP\WZUNZIP'
-- Build and execute unZIP logic
set @SQL = @unZipProgram + ' ' + @File_to_unZip + ' ' + @unZip_Dir
if @CommandString is not null
begin
set @SQL = @SQL + ' ' + @CommandString
end
print @SQL
exec @processError = master..xp_cmdshell @SQL,no_output
if @processError <> 0
begin
set @rtnError = 1
goto End_of_Logic
end
-- End_of_Logic
End_of_Logic:
return @rtnError
go
-
Hi ,
This one is really tough
I asked that i had tranferd a table in a table and i want to zip it.
i did it in 2 steps.
i used BCP utility to create the text file
and created a bacth file to zip it.
with 2 line of code
the path of the zip and code
cd..
cd..
cd program files\winzip\winzip32 -min -a "c:\dd%1.bat" "d:\ff.bcp"
hope this help u all
from
killer
-
Thanks Eric! I have shared with my team - crediting you of course!
[font="Courier New"]ZenDada[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply