When I execute the below, the system hangs an no response. Am I missing anything? Exec master..xp_cmdshell '"crogram fileswinzipwinzip32.exe" –min –a –r -en F:Users.zip F:Users.txt' Madhivanan Failing to plan is Planning to fail
Perhaps you should try to execute this command from a command window to see if it works first. There could be a number of things wrong. For instance maybe you are getting a message indicating that you can't type 'Crogram files...' since this has a space in it; maybe you need to type Crogra~1... Perhaps you didn't pay for winzip and the server is trying to issue a message saying you are unlicenced. Perhaps users.zip doesn't contain a txt file. If this command does in fact work properly directly from the command window it should work from the cmdshell as well.
Yes. That may be the reason. But I tried to assign the file name to variable and used that inside cmdShell. Still it hangs Madhivanan Failing to plan is Planning to fail
Hi madhivanan, well fhanlon has made good points to consider while running xp_cmdshell. try doing zipping at cmd first.
When I run crogram fileswinzipwinzip32.exe at command line, it is executing and Winzip application opens Any other ideas? Madhivanan Failing to plan is Planning to fail
No, you must type the whole command line, including any quotes, and see what happens then. Around the file names, are you using double-quote characters CHAR(34), or two single quote characters CHAR(39)+CHAR(39)? You have to use double-quote characters. Also make sure you are logged on to Windows under the same user account under which the SQL Server service is running. That account may have insufficient permissions to launch winzip, or to read/write files in the given locations.
In my experience its better not to use the other programs using XP_CMDSHELL, perform the compression of files by using other means of software not from SQL server. Anyway Tektips- #declare @ZipFileName VarChar(50) declare @SQLCommand VarChar(400) SET @ZipFileName = 'MyZipFile.Zip' SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'C:MyDirWinzipWZUNZIP -yb C:MyDir' + @ZipFileName + ' C:MyDir' + '''' EXEC (@SQLCommand) Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya, I execute this. Still no response declare @ZipFileName VarChar(50) declare @SQLCommand VarChar(400) SET @ZipFileName = 'Users.Zip' SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + '"crogram fileswinzipwinzip32.exe" -yb F:' + @ZipFileName + ' F:Users.txt' + '''' print @SQLCommand EXEC (@SQLCommand) Why I want to do this is I want to export data to Excel, zip it and send it by mail. I want to automate this so that every saturday to run this. I can do export and send mail except zipping it Madhivanan Failing to plan is Planning to fail
You want the WinZip Command Line Support Add-On, You can download it from their website. use the commandline commands WZZIP and WZUNZIP. Note: WinZip must be registered, otherwise it will prompt the user.
i think rockmoose might be on the right track- i had a similar problem with Winrar and had to download a different version that had command line support
Madhivanan If you feel the filesize is really big when using with excel sheet, then you can plan to output the data to a text file in CSV format and then mail the contents. I feel you need to purchase the license for winzip to obtain the further utilities. Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Yes, it seems that I need to have licence. Well I will use text file Madhivanan Failing to plan is Planning to fail
