SQL Server Performance

WinZip a file

Discussion in 'General Developer Questions' started by Madhivanan, Jun 2, 2005.

  1. Madhivanan Moderator

    When I execute the below, the system hangs an no response. Am I missing anything?

    Exec master..xp_cmdshell '"c:program fileswinzipwinzip32.exe" –min –a –r -en F:Users.zip F:Users.txt'



    Madhivanan

    Failing to plan is Planning to fail
  2. dineshasanka Moderator

  3. Madhivanan Moderator

    Yes. But I could not solve that


    Madhivanan

    Failing to plan is Planning to fail
  4. fhanlon New Member

    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 'C:program files...' since this has a space in it; maybe you need to type C:progra~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.
  5. Madhivanan Moderator

    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
  6. ranjitjain New Member

    Hi madhivanan,
    well fhanlon has made good points to consider while running xp_cmdshell.
    try doing zipping at cmd first.
  7. Madhivanan Moderator

    When I run c:program fileswinzipwinzip32.exe at command line, it is executing and Winzip application opens
    Any other ideas?


    Madhivanan

    Failing to plan is Planning to fail
  8. Adriaan New Member

    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.
  9. satya Moderator

    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.
  10. Madhivanan Moderator

    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 ' + '''' + '"c:program 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
  11. rockmoose New Member

    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.
  12. benwilson New Member

    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
  13. satya Moderator

    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.
  14. Madhivanan Moderator

    Yes, it seems that I need to have licence. Well I will use text file


    Madhivanan

    Failing to plan is Planning to fail

Share This Page