Zip Files

Question

Post reply

Zip Files

ningaraju.n

SSCrazy

Points: 2322
More actions
November 17, 2010 at 7:14 am

#229336

HI All,
I just wanted to zip the file in filesystem through SQL server,The size of the file is 1KB and it taking
huge time to execute.
My query looks like
declare @sCmd varchar(8000)
SET @ServerName=@@SERVERNAME
set @sCmd= '"c:\Program Files\WinZip\winzip150.exe" -ex -m \\MyIP\Shared\FileName.zip \\MyIP\Shared\\FileName.txt'
EXEC @iRetCode = master..xp_cmdshell @sCmd
IF @iRetCode <> 0
print('Error')
please give me an solution for the same

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Does the query every gets completed or it just keeps running?

Also print the output of @sCmd and run it in command prompt to check if any errors pop up.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Lowell SSC Guru Points: 323518 More actions · Answer 2

is winzip150.exe a GUI? that might be an issue, especially if it is raising that nag screen about registering...you know...you've been using Winzip for 483 days....

you need to use an executable that supports command line, which is a different exe, i beleive.

Also, do this quickie diagnostic to see if the account SQL runs under has access to that share...if it doesn't, that might be your issue, an error raised about no access or something:

--\\MyIP\Shared\create table #Files (

FName varchar(1000))

insert into #Files (FName)

exec master..xp_cmdshell 'dir \\MyIP\Shared\ /b'

select * from #Files

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!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 3

The query just went executing not completed so i only canceled the execution

ningaraju.n SSCrazy Points: 2322 More actions · Answer 4

Hi Lowell,

When i executed the query which was mentioned by you,I got the list of files hence I hope the problem is not due to the path.

Regarding the winzip150.exe ,as you said it asked for registration ,Then which software i have to use so that i meet my requirement?

Adiga One Orange Chip Points: 27284 More actions · Answer 5

You may use 7-zip as I mentioned in this post.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Lowell SSC Guru Points: 323518 More actions · Answer 6

ningaraju.n (11/17/2010)
Hi Lowell,
When i executed the query which was mentioned by you,I got the list of files hence I hope the problem is not due to the path.
Regarding the winzip150.exe ,as you said it asked for registration ,Then which software i have to use so that i meet my requirement?

excellent news that xp_cmdshell has access to the shared folder; that tripps a lot of folks up.

if winzip150 is asking for registration, it's the GUI and not the command line. that nag screen is being raised, and since you cannot click a window from a command line, it seems to hang/take forever.

EVERY zip utility I've every used had a separate exe for command line.

if you buy the full version of winzip, an additional download is available, which gives you wzzip.exe and wzunzip.exe

7zip(which is free) is what i use now, and the command line executable is 7za.exe.. requiring a seperate download (7za465.zip)

another zip prog, pkzip and pkunzip were the same.

if you do not have a registered version of winzip, you cannot use it via command line(which means xp_cmdshell is out)

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!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 7

Thanks Lowell,

I too learnt that usage of winzip150 needs registration,So i started using 7Zip,but i am getting

some command line error,i am in hunt for this from last 4 hrs and i am not getting, could you

please illustrate with code example and the file which has to be zipped in shared folder

In anticipating your answer

Lowell SSC Guru Points: 323518 More actions · Answer 8

sure.

on the 7zip web site:

http://www.7-zip.org/download.html

the fifth Download link explicitly says

"7-Zip Command Line Version"

download that, the syntax is inside.

to zip a file based on your previous example, the command is like this:

(assuming you unzipped 7za465.zip to a folder named "C:\Utils" ON THE SERVER)

note i included dbl quotes around the filenames to handle long file names with spaces...it's a good habit to follow.

--\\MyIP\Shared\create table #Files (

FName varchar(1000))

declare @sCmd varchar(8000)

--use 7zip command line exe to zip a file accross file shares.

-- "a" flag is add, to uzip it's "e" to expand

set @sCmd= '"c:Utils\7za.exe" a "\\MyIP\Shared\FileName.zip" "\\MyIP\Shared\FileName.txt"'

insert into #Files (FName)

exec master..xp_cmdshell @sCmd

--does the file exist?

insert into #Files (FName)

exec master..xp_cmdshell 'dir \\MyIP\Shared\ /b'

select * from #Files

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!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 9

As suggested by you i executed the following script and received error

create table #Files (

FName varchar(1000))

declare @sCmd varchar(8000)

--use 7zip command line exe to zip a file accross file shares.

-- "a" flag is add, to uzip it's "e" to expand

set @sCmd= '"c:\Utils\7za.exe" a "D:\Shared\FileName.zip" "D:\Shared\FileName.txt"'

insert into #Files (FName)

exec master..xp_cmdshell @sCmd

--does the file exist?

--insert into #Files (FName)

--exec master..xp_cmdshell 'dir \\192.168.3.220\Shared\ /b'

select * from #Files

drop table #Files

Error is:

The filename, directory name, or volume label syntax is incorrect.

Lowell SSC Guru Points: 323518 More actions · Answer 10

ok here's a working example, parsed out and tested; i don't see any difference between what i posted from memory:

--Application.StartupPath & "\7za.exe", "e HDS_9.0.4.7.zip -o" & Application.StartupPath & "*.* -y")

CREATE TABLE #results (results varchar(255))

declare @command varchar(2000)

insert into #results

exec xp_cmdshell 'dir E:\Installs\'

select * from #results

delete from #results

GO

--try 7zip:

DECLARE @results TABLE(results varchar(255))

declare @command varchar(2000)

--zip one file

SET @command =

'"E:\Installs\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!

+ '7za.exe"' --the exe: i'm using the command line utility.

+ ' a ' --the Add command: add to zip file:

+ '"C:\Data\' --path for zip

+ 'myZipFile.zip"' --zip file name, note the dbl quotes for long file names!

+ ' ' --whitespace between zip file and file to add

+ '"E:\Installs\' --path for the files to add

+ 'XP-Vista_VirtualDesktops_Setup.msi"' --the file

+ ' -y' --suppress any dialogs by answering yes to any and all prompts

print @command

insert into @results

exec xp_cmdshell @command

select * from @results

GO

--append another single file to the existing zip

--try 7zip:

DECLARE @results TABLE(results varchar(255))

declare @command varchar(2000)

--zip one file

SET @command =

'"C:\Data\7zip_CommandLine_7za465\' --path to 7za command line utility note the dbl quotes for long file names!

+ '7za.exe"' --the exe: i'm using the command line utility.

+ ' a ' --the Add command: add to zip file:

+ '"C:\Data\' --path for zip

+ 'myZipFile.zip"' --zip file name, note the dbl quotes for long file names!

+ ' ' --whitespace between zip file and file to add

+ '"C:\DB\' --path for the files to add

+ 'GMVB003262010forlowell.bak"' --the file

+ ' -y' --suppress any dialogs by answering yes to any and all prompts

print @command

insert into @results

exec xp_cmdshell @command

select * from @results

--zip all files in a directory

--unzip anything into a directory

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!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 11

Thanks Powell for your time .

I tried your code also but it didn't worked for me

I Hope I have not followed exact steps for installation of 7Zip software.

If you please get that steps it will be more helpful to me

Lowell SSC Guru Points: 323518 More actions · Answer 12

what specific error do you get?

if you print your command before executing it, you could paste it in a command line window....does it work from there? mine does.(from teh cmd window on the server)

the issue is no longer syntax, but the specifics of permissions/files on your machine, i'm thinking...WHERE is the executable 7za.exe? it's on the c: drive of SQL Server you are connecting to, right? not on your local client machine?

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!

ningaraju.n SSCrazy Points: 2322 More actions · Answer 13

ya I copied the command and executed in cmd prompt there also i received same error.

And the exe is in C drive only where SQL server resides.

Can u send out the steps you took for installation,i hope it might help me

and the error is

:

The filename, directory name, or volume label syntax is incorrect.

Lowell SSC Guru Points: 323518 More actions · Answer 14

there was no installation to do...i downloaded the command line utility i pointed at fromt he previous posts, then unzipped the contents of 7za465.zip into the C:\Utils folder; no msi or anything else. if that's not what you are also doing, let me know.

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!