how to zip file
-
hi,
i created a dts that outputs the result of a query in an excel file. but its too big and i want it zipped. any ideas?
thanks
ann
-
You can use activex script task.
Suppose the path of excel file is "c:\DTS" and you want name of zip file as DTS.ZIP
This script will zip all the files from folder.
*******************************************
Function Main()
set WshShell = CreateObject("WScript.Shell")
Dim sDirToZip,sMyZipFile
sMyZipFile = "c:\DTS.zip"
sDirToZip = "c:\DTS"
WshShell.Run("C:\progra~1\winzip\winzip32.exe -a -s " & chr(34) & sMyZipFile & chr(34) & " " & sDirToZip)
Main = DTSTaskExecResult_Success
End Function
*************************************************************
Hope this helps.
-
This method can be tailored to use Winzip for zipping or unzipping files within SQL Server (SPs, DTS Packages ...)
You will also have to download the current version of the WinZip Command Line interface from their site.
The reason for the license is that when you register Winzip, it removes the commercial/company header message from being displayed when you run Winzip in the command line. If you do not register the software, the zip will hang. So in say that ... here is the code to zip a file thru SQL Server ...
declare @ZipFileName VarChar(50) declare @SQLCommand VarChar(400) SET @ZipFileName = 'MyZipFile.Zip' SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'C:\MyDir\Winzip\WZZIP -yb C:\MyDir\' + @ZipFileName + ' C:\MyDir\' + '''' EXEC (@SQLCommand)
My Blog:
http://dineshasanka.spaces.live.com/ -
for those of you who are too cheap to buy and register winzip(command line winzip is not free, i beleive)
you can use PKZIP25.exe, which is the last free command line zip program i could find;
if you put a copy of that in the path of th OS, or in the bin directory of sql server, you could use this kind of syntax:
declare @Pdy varchar(150),@DY varchar(15),@filename varchar(150)
set @filename='D:\MSSQL7\BACKUP\somefile.xls
'set @DY = (select datename(dw, getdate()) )
SET @Pdy=(select 'pkzip25.exe -a "'+ @filename +'-' + @DY + '.zip"' +' "' +@filename "'
exec @result =xp_cmdshell @Pdy
IF (@result = 0)
PRINT 'Successfully Created Zip File'
ELSE
PRINT 'Failed to Create Zip File. Check Drive mappings and available harddrive space for both the Server and The remote location.'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
You can also use a freeware product that someone sent to me this week, 7-Zip.
It has a command line interface too as well as a GUI. There seems to be a drawback to the product in that you can't reference mapped drives or \\servername, but it may be useful to you.
Check it out at: http://www.7-zip.org
It also supports other file types besides ZIP.
hth..
Mark Gelatt
-
What if I use a long filename?
Ann
-
pkzip25.exe handles longfilenames; you simply wrap them in double quotes;
pkzip25.exe -a "D:\MSSQL\BACKUPS\DatabaseName-Thursday.bak" "\\SomeServer\SharedFolder\DatabaseName-Thursday.zip"
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have been using a product called WinRAR for about three or four years and it is perfect for large file compression. It can be easily configured for the amount of compression you want by simply using a pick list from a dropdown box. It costs about $20, so it won't break the bank. Also, they have great tech support. After originally downloading, paying for and registering the product three or four years ago, I could not find the original file I downloaded so I could install it on the replacement server I am installing. I contacted the company that is in Germany, I think, was advised they couldn't help, found an American arm of the company and explained the situation to them. They did a little legwork for me and in a few days, I had the original registration number and access to the file to download again. This time, it was burned to a disk.
Just do a search on Google for WinRAR and you'll have what you need in a few minutes with no pain at all.
Good luck.
Gary Hileman
TN Farmers Ins. Co.
-
is pkzip25.exe a freeware? i can't seem to find anything over the net. all i got is a pkzip.exe and it does not handle long filenames.
ann
-
I'm not aware of there being a freeware version of PKZip, but if there is, I don't think it will compress large files greater than 2 GB. I would stress again that you try WinRAR as your compression tool. It is inexpensive and easy to use. Unless you just enjoy writing all of those scripts to compress a large file, then a long file name is not an issue with WinRAR. Just fire it up, select the file(s) you wish to compress and select OK. Short and sweet.
-
LanMan we were refering to using command line functionality to script a zip file; as far as i know, WINRAR is a gui interface only. WinRAR is a great utility, but i don't know that you can script out the command line arguments to use with xp_cmdshell
if you cannot find pkzip25.exe on the web, email me and I will send you a copy.
PKZIP25 was the last freeware command line utility that i could find, which also supported long file names.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, I understand what you are trying to do, but being a bit new in the world of the DBA, I am uncertain what the advantage(s) is/are to using a command line to script a zip file over just using a product's gui and getting the file(s) compressed. I would appreciate an explanation just for my own understanding as it might help me do as better job. Thanks. LanMan
-
it's all about scheduling and automating a task, vs user intervention. once set up, a scheduled script never needs to be handled again, and the DBA is freed up to do other work. Automation and autochecking the database is an important part of a DBA's job I think, getting basic repetitive jobs out of the way so he can concentrate on other items.; doing it thru a GUI adds a few minutes to the DBA's daily plate of troubles when he doesn't really need to.
The scenario we have been talking about is this: a DBA has identified a need to back up all his databases, conserve disk space wherever possible, and maybe add additional disaster recovery capabilities by saving a copy of the compressed backup to another server, which might even back that data up to tape each night.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
OK. Makes perfect sense. Thanks for the 411. I'll take this under advisement and look into scripting more of my tasks whenever possible. LanMan
-
i want to zip all my jpeg file in the image folder by using asp.... can anyone tell me how to do it?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply