cancel
Showing results for 
Search instead for 
Did you mean: 

Zipping file from Stored Procedure

Former Member
5,309

Is it possible from within a stored procedure to zip up files ?

Accepted Solutions (0)

Answers (3)

Answers (3)

MarkCulp
Participant
There is nothing built into the server to zip up the files, but you can easily call an [external procedure][1] to do it, or use [xp\\_cmdshell][2] to run an external process that will zip up the files for you.

[edit 2013-02-15]

Sorry, most of my response above is bogus... I confused compression with encryption 😞

Since SA v10 the compress() and decompress() functions have had a second parameter named 'algorithm' which when set to 'gzip' can be used to compress/decompress data that can be exchanged outside of the database server and decompressed/compressed using gunzip/gzip.

Example:

-- write compressed contents of variable @data to file data.gz
call xp_write_file( 'data.gz', compress( @data, 'gzip' ) );
-- and then decompress it using gunzip
call xp_cmdshell( 'gunzip data.gz' );
Breck_Carter
Participant

It's the tie you're wearing in your photo... restricts the bloodflow 🙂

Former Member
0 Kudos

I ended up downloading 7-Zip, writing a bat file to call 7 Zip, and then calling that bat file with an Event. Worked well.

VolkerBarth
Contributor
0 Kudos

What about reading the file with xp_read_file() and using the builtin COMPRESS() function to compress data and write it back to file with xp_write_file()?

Note, I can't tell whether the builtin compression with the "zip" algorithm will be compatible with ordinary zipped files. On Linux, using "gzip" should work that way. - Mark's response may indicate that "zip" itself won't work here as expected...

MarkCulp
Participant
0 Kudos
Using compress within the engine will compress the data contents but you cannot unzip the result outside of the engine (unless you know what you're doing). The same is true the other way - you cannot use uncompress to unzip contents which have been zipped outside the engine. (Note: this comment applies to SA v12 and below).

[Edit: 2013-02-15 this statement is incorrect - I have corrected my answer to this question]

Also, the question was not clear whether a single file was being zipped or a zip archive (containing multiple files) was wanted.

VolkerBarth
Contributor
0 Kudos

Thanks for the clarification - I had a weak impression I had read about that limitation in some FAQ or NNTP thread (therefore my suspicion...) but couldn't find anything relevant.

Now it's well-documented here, at least:)

VolkerBarth
Contributor
0 Kudos

(Note: this comment applies to SA v12 and below).

Hmmm, have I overseen something in the Nagano docs...?

VolkerBarth
Contributor
0 Kudos

Now it's well-documented here, at least:)

Hmm, I'm not so sure anymore:)

VolkerBarth
Contributor
0 Kudos

Okay, when Mark has been refering to encryption instead of compression, then the v16 hint makes sense ("Raw encryption", do you hear me?)...