In some corporate environments the only access web servers in a DMZ have to the internal network is their connection to SQL Server and it's data.  This SQL CLR example provides an option to put that connection to work serving up other files through the Firewall without having to change any external security.

I have seen a number of posts on various forums regarding issues with loading the System.Web assembly as Unsafe in SQL Server so that the HttpUtility.HtmlEncode/HtmlDecode method can be used to encode/decode an input for usage. The following function can be cataloged as SAFE in SQL Server and does exactly the same things that the Method in System.Web does for the HtmlDecode.

I have seen a number of posts on various forums regarding issues with loading the System.Web assembly as Unsafe in SQL Server so that the HttpUtility.HtmlEncode/HtmlDecode method can be used to encode/decode an input for usage. The following function can be cataloged as SAFE in SQL Server and does exactly the same things that the Method in System.Web does for the HtmlDecode.

The ability to insert a file into a VARBINARY(MAX) or VARCHAR(MAX) column using OPENROWSET is an awesome tool.  The obstacle comes when you wish to insert all or specific files from a foler into sql.  To do this you can use the System.IO namespace and utilizing directory and file objects you can iterate through all, or selected files in a path specified in the above clr stored procedure.  After deploying this proc I would recommend adding a default value to the extn input parameter of '*.*', which indicates all files.

For a project I was working on, users wanted to be able Trigger exports from SQL to a Mainframe application that used a Fixed Column Width format for the data transfer.  Since the Mainframe expects the same file name each time, the users wanted a way to maintain a history of the files that were exported across.  I tried to set up the parameters so that it could be reused without having to rewrite it each time.