Have you ever needed to zip a file from SQL server? Ever got frustrated with using the old cmd approach with winzip, what if your database server and the guys who admminister it don't allow you to install winzip or some third party application (I don't blame then) then what are your options? Well, you could always use a CLR Function to accomplish this.
My last post I explained what a CLR Function is, this post i'm going ot talk about how to set up your own CLR zip utiliity add it to the database and off you go zipping.
If your short on time and just want to go straight to the juice, you can download the code and utility ('you'll need to compile and change the password though to get you running).
ZipFileSQLCLR.zip (430.94 kb) Click this link to download the sample project
The solution contains to libraries and one test application.
Start with the Asm.ZipFile project. open the commonfunctions.cs code file and change the username domain and password used or alternatively take out the impersonator. I decided to use the impersonator because it allows me to access network resources.
Before putting your assembly into the SQL server you'll want to test it to ensure that it actually does zip a file! Run the TestApplication to ensure that zipping is occuring.
After compilation you'll need to follow the follow steps to get the assembly to run on the server.
1. In order to use CLR you must run this on the database
ALTER DATABASE ARTSDW SET TRUSTWORTHY ON
2. Assemblies should now be there, now you must enable CLR execution so that users can excecute the assembly
-- Needed to enable clr excecution
sp_configure 'clr enabled', 1
go
reconfigure with override
3. First you must add the Ionic.Utils.Zip assembly to the database!
Now go ahead and add teh ZipFileQuick.dll assembly.
5. To test create
the following Hello World Example
-- Hello World Example
CREATE FUNCTION dbo.clrHelloWorld
(
--@name as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME
[ZipFileQuick].[ZipFileQuick.CommonFunctions].[NameMe]
go
SELECT dbo.clrHelloWorld()
6. Create a Stored procedure to execute the CLR
zip functionality.
CREATE PROCEDURE dbo.Web_CLRZIPFILE
(
@namein as
nvarchar(200),
@nameout as
nvarchar(200)
)
AS EXTERNAL NAME
[ZipFileQuick].[ZipFileQuick.CommonFunctions].[ZipFileAfile]
execute using the following
example:
exec clrZipFile '\\corpdata\Data\ARTSDEV\test.xls','\\corpdata\Data\ARTSDEV\test.zip'
Some more things to know:
I've had problems before with certain owners of the database, try changing the owner of the database.
Before or after you add the assemblies you'll need to add the msmgdsrv.dll (5.76 mb)
