Assembly 'EDCLR' references assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089 error

Question

I'm try to create clr function in c# with this code:

[SqlFunction]
        public static string Decrypt(string Input)
        {
            var test = "";
            var url = "http://www.url/feed/";
            using (XmlReader reader = XmlReader.Create(url))
            {
                SyndicationFeed feed = SyndicationFeed.Load(reader);
                //MessageBox.Show("Title:" + feed.Title.Text);
                //MessageBox.Show("Uri:" + feed.Links[0].Uri);
                foreach (SyndicationItem item in feed.Items)
                {
                    test=item.Title.Text;
                }
            }
            return test;
        }

when try add that to sql server assembly with this script:

create assembly behzad
from 'd:\EDCLR.dll'

i get this error:

Assembly 'EDCLR' references assembly 'system.servicemodel, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: 2(The system cannot find the file specified.)). Please load the referenced assembly into the current database and retry your request.

how can i solve that?thanks.

Answer 1

There are two problems here:

1. ServiceModel is not a supported .NET Framework Library for SQLCLR. Meaning, it is not included in SQL Server's CLR host. In such cases, one needs to manually load, via CREATE ASSEMBLY, the Assembly. BUT,

2. Starting in SQL Server 2012, you cannot reference / load ServiceModel as that version is the first to be linked to CLR v 4.0 in which ServiceModel became a mixed mode Assembly. Only pure MSIL Assemblies are allowed.

You will need to find a different way to accomplish this. One way is to use HttpWebRequest and HttpWebResponse.

This issue has been documented on Stack Overflow here:

• (Unfixable) Assembly exists on SQL Server 2014 but it claims it doesn't have it

• Register CLR function (WCF based) in SQL Server 2012

• SQL CLR - Migration from 2008 R2 to 2012.

More info here:

• SQL Server: "CREATE ASSEMBLY for assembly 'Test' failed because assembly 'Test' is malformed or not a pure .NET assembly."

• SQL Server custom CLR fails with error "Could not load file or assembly or one of its dependencies. The system cannot find the file specified."