Zipping a Text file

Question

Zipping a Text file

sql_lover

Right there with Babe

Points: 724
More actions
November 30, 2010 at 7:51 pm

#144175

Hi
I need to Zip a text file so trying following command,
declare @cmd varchar(1000)
set @cmd='"\\Dell\Tessitura\Source\WinZip\winzip32.exe" -a "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"'
select @cmd
EXEC master..xp_cmdshell @cmd
But I get error saying 'System can not find path specified'
Can anyone tell me what could be going wrong?
Thanks
Varsha

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

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

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

If you run the same command in Command Prompt does it complete successfully? If yes, then the SQL Server service account might not have access to the path where the file is located.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

sql_lover Right there with Babe Points: 724 More actions · Answer 2

Yes Pradeep, I created a batch file and it works fine from Command Promt. But that batch file doesn't work in SQL.

Zip file is in C drive, probably that is not accessible to SQL Server. Can you please tell me how to give access to the C drive? I'm not an expert in this field.

Thanks

Varsha

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

Hi Varsha,

First make a note of the windows login which is starting SQL Server service (from the SQL Server Configuration Window)

Then logon to the server on which you need to copy the file and navigate to the folder. Right click on the folder and select Properties. Then add the account (from step 1) in the Securities tab and grant it sufficient privileges. Also add the login in the sharing tab and grant it permissions.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

sql_lover Right there with Babe Points: 724 More actions · Answer 4

All permission setup is done as per your suggestions but it still doesn't work. Now i feel may be my syntax is wrong.

Declare @cmd varchar(260)

Set @cmd = 'C:\"Program files"\WinZip\winzip32.exe \\Dell\Tessitura\Source\sso_pacingdata.zip \\Dell\Tessitura\Source\sso_pacingdata.txt'

select @cmd

EXEC master..xp_cmdshell @cmd

Is there any error in my script?

The batch file works correctly which has,

@Echo off

C:\WinZip\winzip32.exe -a "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"

it works proerly from command prompt.

Thanks

Varsha

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

@cmd should be

'"C:\Program files"\WinZip\winzip32.exe" -a "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"'

Also try executing the output of select @cmd from the command prompt. This should help you troubleshoot.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 6

I usually only need the double quotes from SQL Server when my path name has a space in it. You might try it without the double quotes and see what happens.

Also, what's with the double quotes after the phrase "Program Files"?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

sql_lover Right there with Babe Points: 724 More actions · Answer 7

I've create batch file:

@Echo off

"\\keenm\c$\WinZip\WZZIP.exe" "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"

---------------

Now i run

master..xp_cmdshell 'Text_Zip_Cmd.Bat'

It runs perfectly from command prompt but in SQL i get folloing error.

ERROR(\\keenm\c$\WinZip\WZZIP.EXE,103): WinZip is not installed.

You must install WinZip in order to use the WinZip Command Line Support Add-On

NULL

Earlier I thought it was Winzip license issue but after fixing it and also installing add-on i'm getting this error.

When I run xp_cmdshell 'dir \\keenm\c$\winzip' i can see all files..

similarly when I run xp_cmdshell 'dir \\dell\tessitura\source' i can see all files..

that means I'm able to access all files, but i can't understand whats missing when it comes to SQL Server.

Any help will be appreciated..

Thanks

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

I am not too sure if you can call WINZIP.exe from a UNC path. Did you try installing winzip on the computer from which you are executing this comd?

I have been using 7-zip for file compression and it works nicely.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

sql_lover Right there with Babe Points: 724 More actions · Answer 9

Actually WINZIP is installed on my computer and also SQL Server but still doesn't work.

Will 7zip work same as WINZIP? does it need any command lien add-on etc? I can try with 7Zip as well as its free to download...

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

In that case, instead of calling WinZip from an UNC path, you can directly refer to the local path as "C:\Program Files\...."

7-zip does not require any command line tool to be installed. You can call the 7z.exe from the command prompt. Here[/url] is an example.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Lowell SSC Guru Points: 323504 More actions · Answer 11

here's what i use.

note you have to download the command line version of 7zip. like all other zip files i know of, you cannot use the GUI, it's always a seperate executable (wzzip vs winzip32, 7za vs 7zip)

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

--http://downloads.sourceforge.net/sevenzip/7za920.zip

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

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!

Brandie Tarvin SSC Guru Points: 172995 More actions · Answer 12

varshakarnik 92752 (12/5/2010)
I've create batch file:
@Echo off
"\\keenm\c$\WinZip\WZZIP.exe" "\\Dell\Tessitura\Source\sso_pacingdata.zip" "\\Dell\Tessitura\Source\sso_pacingdata.txt"
---------------
Now i run
master..xp_cmdshell 'Text_Zip_Cmd.Bat'

If you are running this batch file on the server, from the server, you need to get rid of the server name. It should be C$\ at the start of the WZZIP.exe command.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

sql_lover Right there with Babe Points: 724 More actions · Answer 13

Finally after so many days of R&D, i've scheduled the zipping task on Windows Task Scheduler and its solved my problem. I defnitely wanted to use master..xp_cmdshell on SQL Server but looks like it will not be possible. I will keep trying but for time being its all sorted.

Thanks a lot to all of you for helping.

regards

Varsha

sql_lover Right there with Babe Points: 724 More actions · Answer 14

I can't believe but finally my script with 7-zip worked in SQL Server.

As per one of Tessiturian's suggestion, i used,

exec master..xp_cmdshell '\\dell\tessitura\source\7-zip\7zG.exe a "\\dell\tessitura\source\sso_pacingdata.zip" "\\dell\tessitura\source\sso_pacingdata.txt" -y'

To look at it looks exactly same as earlier but its without double quotes to exe path.

cheers

Varsha