Stored procedure to Zip a folder

• Sql Student-446896

  Default port

  Points: 1425

  More actions

  November 8, 2007 at 10:24 am

  #119201

  Hi,

  I have a folder in my server location C:/temp/Ouput.

  I have to schedule a job which will call a stroed procedure to zip the file called Output and store it in the same temp folder.

  How can i do that?

  Thanks,

  Rama

• fhanlon

  SSChampion

  Points: 13131

  More actions

  November 8, 2007 at 10:32 am

  #749576

  xp_cmdshell can be used to execute DOS commands. SO assuming you have zip software installed that has a command line version (like Winzip or 7-Zip and I'm sure others as well) you can execute the require command as a parameter to xp_cmdshell.

  You must ensure that xp_cmdshell is enabled and you have authority to use it.

  Francis

• TheRedneckDBA

  SSChampion

  Points: 14003

  More actions

  November 8, 2007 at 10:33 am

  #749578

  That will vary based on your zip program, but something like this ought to get you started.

  I think this will work for PKZIP, but I didn't look up the exact command line syntax.

  DECLARE @CMD VARCHAR(512)

  SET @CMD = 'C:\PKZIPC\PKZIPC.exe -add d:\folder\destination.zip d:\folder\source.txt '

  EXECUTE xp_cmdshell @CMD

  I might have the destination and source parameters reversed...don't remember

  The Redneck DBA