9

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?

9

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.
7

As you have noted, System.Web is an unsupported library. In order to reference System.Web you will need to make a call to CREATE ASSEMBLY. It seems like you tried that, but how did you reference the location of System.Web.dll? Did you copy/paste it to a different location? SQL Server will try to locate dependent assemblies in the same location. In other words, if you reference the location of System.Web.dll with all of the other dependent libraries living in the same directory, it should work just fine. Here is a working example. I was able to add both the System.Web assembly as well as your assembly:

create assembly [System.Web]
from 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll'
with permission_set = unsafe;
go

create assembly SystemWebTest
from 'c:\SqlServer\SystemWebTest.dll'
with permission_set = safe;
go

You can see from the client messages all of the other assemblies that SQL Server loads. But take note, SQL Server displays the following warning for each of these:

you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.

Likewise, but adding System.Web, take a look at the following assemblies that are added:

select
    name,
    permission_set_desc,
    is_visible
from sys.assemblies
where is_user_defined = 1
order by is_visible desc;

name                                            permission_set_desc is_visible
System.Web                                      UNSAFE_ACCESS       1
SystemWebTest                                   SAFE_ACCESS         1
Microsoft.Build.Framework                       UNSAFE_ACCESS       0
System.Xaml                                     UNSAFE_ACCESS       0
System.ComponentModel.DataAnnotations           UNSAFE_ACCESS       0
System.Runtime.Caching                          UNSAFE_ACCESS       0
System.Web.ApplicationServices                  UNSAFE_ACCESS       0
System.Drawing                                  UNSAFE_ACCESS       0
Microsoft.Build.Utilities.v4.0                  UNSAFE_ACCESS       0
System.DirectoryServices                        UNSAFE_ACCESS       0
System.DirectoryServices.Protocols              UNSAFE_ACCESS       0
System.EnterpriseServices                       UNSAFE_ACCESS       0
System.Runtime.Remoting                         UNSAFE_ACCESS       0
System.Runtime.Serialization.Formatters.Soap    UNSAFE_ACCESS       0
System.Design                                   UNSAFE_ACCESS       0
System.Windows.Forms                            UNSAFE_ACCESS       0
Accessibility                                   UNSAFE_ACCESS       0
System.Drawing.Design                           UNSAFE_ACCESS       0
System.Web.RegularExpressions                   UNSAFE_ACCESS       0
Microsoft.Build.Tasks.v4.0                      UNSAFE_ACCESS       0
System.ServiceProcess                           UNSAFE_ACCESS       0
System.Configuration.Install                    UNSAFE_ACCESS       0
System.Runtime.Serialization                    UNSAFE_ACCESS       0
System.ServiceModel.Internals                   UNSAFE_ACCESS       0
SMDiagnostics                                   UNSAFE_ACCESS       0

It is worth being mindful about what is actually happening here, and even though the other additional assemblies don't have ways for T-SQL entry points, they are now a dependency. I would weigh the options to see if you really need to reference System.Web, or if there is another route to accomplish what you want.

  • Thanks Thomas, that has worked. I was trying CREATE ASSEMBLY pointing at System.Web.dll scripted via the GUI. I was experimenting with this as an answer to a newsgroup post (linked previously), so no burning desire to use it myself. – wBob Oct 28 '14 at 14:40
  • 1
    @wBob : unless you absolutely have to, you should be careful about adding non-supported DLLs. There is a good reason that System.Web is not in the "Supported Libraries" list that you linked to: 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. I will add a note about this in my answer just to be clear for those who might not know. – Solomon Rutzky Oct 28 '14 at 14:53
  • @srutzky I agree. – wBob Oct 28 '14 at 14:55
  • You are a life saver. I was going to be SOL if I couldn't import this library. (It's one of those extremely rare cases.) – devinbost Dec 9 '14 at 19:03
4

Check out this answer. You don't have to use Uri.UnescapeDataString or System.Web. There is a class called WebUtility inside of System.Net with the functions HtmlEncode and HtmlDecode.

  • You should point out that WebUtility is only available to those using SQL Server 2012, 2014, or newer. Those still on SQL Server 2005, 2008, and 2008 R2 won't be able to use this due to it being introduced in .NET Framework 4.0. – Solomon Rutzky Feb 3 '15 at 16:02
  • @srutzky The question is for 2014 but it is important to let users know about this limitation. Do you have a link that I could post that shows the relationship between .NET Framework version and SQL Server version? – skeletank Feb 3 '15 at 19:29
  • My article was published today so now there does exist a chart showing the relationship between SQL Server versions and SQLCLR features (including Framework and CLR versions): Stairway to SQLCLR Level 5: Development (Using .NET within SQL Server) (free registration is required). – Solomon Rutzky Mar 11 '15 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.