xp_cmdshell winzip string constuction help plz

Question

xp_cmdshell winzip string constuction help plz

Mark Hunter-303176

SSC Veteran

Points: 224
More actions
February 17, 2006 at 7:15 am

#93388

Hi! My first post on this forum
The problem is this.... I have written a script that detaches, zips, and moves databases to an archive location, however I have just realised that it dosent deal with databases that have spaces in their name. As a result I have tried to enclose my db names in " " however when executed on cmd line it dosent work!?
Problem lines:
set @zipcmd = '"C:\Program Files\WinZip\wzzip" ' + '"' + @zipfilename + '" "' + @filename + '"'
print @zipcmd
exec xp_cmdshell @zipcmd
error returned:
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
the print @zipcmd line looks perfect:
"C:\Program Files\WinZip\wzzip" "C:\Copy of An Example.zip" "C:\Copy of An Example.mdf"
Without the extra quotes(green) to deal with spaces the line works perfectly. Im guessing that Im not constructing the string properly?? Any help greatly appreciated!

Viewing 8 posts - 1 through 7 (of 7 total)

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

Firstly add the path of the WZZIP.EXE files to the PATH environment variable. Having done so restart MSSQLSERVER and SQLSERVERAGENT. You should now be able to call WZZIP without a fully qualified path.

Personally I avoid spaces in filenames like the plague. They cause more problems than they are worth.

I have had problems with \ characters. Try using \\ instead.

LinkedIn Profile

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 2

Try this:

In TSQL split the path from the executable; @Path & @exe

Create a command string and execute it:

Set @Cmd='CD /D '+@Path+' & "'+@Exe+'" '"+@Parm1+"' '"+@Parm2+'" ...'

(notice that the CD command does NOT require double-quotes around its argument; notice the & command separator; notice the double quotes around @exe and the @Parm expansions...)

Exec master..xp_CmdShell @Cmd

Command Prompt is a quagmire of legacy convention...

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

mfink SSCommitted Points: 1632 More actions · Answer 3

David.Poole (2/17/2006)
Firstly add the path of the WZZIP.EXE files to the PATH environment variable. Having done so restart MSSQLSERVER and SQLSERVERAGENT. You should now be able to call WZZIP without a fully qualified path.
Personally I avoid spaces in filenames like the plague. They cause more problems than they are worth.
I have had problems with \ characters. Try using \\ instead.

I am having the same problem. But I do not get add the path of the WZZIP.EXE and the restart MSSQL & SQL Agent

I have tried c:\Program Files\Winzip\WZZIP.exe and that does not work either.

:w00t:

mfink SSCommitted Points: 1632 More actions · Answer 4

I have added C:\Program Files\Winzip to the path on my pc. I remoted to the server and added the path to the server that SQL Server 2005 is running on.

1. Open System from the Control Panel to the Advanced tab

2. Click the Environment Variables button

3. In the System variables section of the dialog that opens ( lower half) click Path to select it and click Edit

4. Move to the end of the text in the Variable value text box, enter a semicolon (;) and type C:\Program Files\WinZip

5. Click OK in all three open dialogs to close them

These are per the instructions of WINZIP tech.

How ever when I try to run this as a SQL statement

Declare @Work varchar(2000)

set @work= 'WZZIP G:\Production\Backups\Test.zip G:\Production\Input\*.*'

Execute xp_cmdshell @work

It does not work

If I try to run it as a SQl AGent Job it does not work

If I copy the text with in the Quoates and paste it into a dos command box it works

:w00t:

sunil7289 SSC Journeyman Points: 83 More actions · Answer 5

Hi,

I'm facing with the same problem. If any answer to solve it in Sql Server 2000, then please reply..Thanks in advance...

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 6

Are your QUOTED_IDENTIFIERs ON?

Try using SET QUOTED_IDENTIFIER OFF at the beginning of the query.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 7

From BOL (http://msdn.microsoft.com/en-us/library/ms175046.aspx):

command_string cannot contain more than one set of double quotation marks.

Also, "If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround."

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com

Follow me on
Twitter: @sqltwins