SQL Server 2014 – Assembly ‘system.web’ Not Found in SQL Catalog

sql serversql server 2014sql-clr

I am trying to deploy a SQL CLR function using the HTTPUtility.UrlDecode method of System.Web but I can't get it to deploy. Error received:

.Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1
Assembly 'system.web, version=4.0.0.0, culture=neutral,
publickeytoken=b03f5f7f11d50a3a.' was not found in the SQL catalog.

The function (as part of SSDT project):

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true)]
    public static SqlString udf_UrlDecode(SqlString encodedXML)
    {
        string decodedXML;

        decodedXML = HttpUtility.UrlDecode(encodedXML.ToString());

        return new SqlString(decodedXML);
    }
}

It's in relation to this thread. I am SQL Server 2014 with VS2012 SSDT and Database Project. Have tried with other Target Frameworks, eg 3, 3.5, 4 and 4.5.

I have also tried CREATE ASSEMBLY with System.Web, but then have to add other assemblies, eg Microsoft.Build, System.Xaml until they also fail. I see System.Web is not on the list of Supported Libraries so any ideas?

Best Answer

You can either use Uri.UnescapeDataString (in System), in which case you will also need to do a Replace('+', ' ') on the string before passing it to Uri.UnescapeDataString, or if you would rather not bother with it, this function is available in the Free version of SQL# (which I am the author of).

Importing System.Web is probably more work than it's worth. And in fact, it can be risky. There is a good reason that System.Web is not in the "Supported Libraries" list that you linked to in the question: it is not guaranteed to work! You might run into situations, especially when dealing with non-US ASCII character sets, that do not behave as expected, and Microsoft will not fix it. So, unless you absolutely have to, you should be careful about adding non-supported DLLs. The DLLs in the "Supported" list have been fully tested and verified to work with SQL Server collations and any other environmental issues that are different between standard CLR running in Windows and the CLR running inside of SQL Server.

Here are some additional resources from Microsoft regarding several pitfalls to incorporating unsupported .NET Framework libraries:

A few notes about your code:

  1. Please do not use .NET types for parameters, input or output. Hence, change string encodedXML to be SqlString encodedXML.
  2. This function is deterministic so should be marked as such, else you will suffer a performance hit. Add IsDeterministic = true to the SqlFunction attribute.
Related Question