Trouble Executing a CLR SQL Function

Question

I have created a simple, 1 method DLL to convert a string address to a Lat/Lon string using an in-house web service. The code is simply this:

public class GeoCodeLib
{
    [SqlFunction(IsDeterministic=false)]
    public SqlString GetLatLon(SqlString addr)
    {
        Geo.GeoCode gc = new Geo.GeoCode();
        Geo.Location loc = gc.GetLocation(addr.Value);
        return new SqlString(string.Format("{0:N6}, {1:N6}", loc.LatLon.Latitude, loc.LatLon.Longitude));
    }
}

I have targeted .NET Framework 2.0. We're using SQL Server 2008 R2. The assembly has been added to the database (the assembly is called GeoCodeSqlLib.dll). We've set no permissions on it.

I can't seem to call it. I've tried:

select GeoCodeSqlLib.GeoCodeLib.GetLatLon('12143 Thompson Dr. Fayetteville, AR 72704')

That gives me the message, Cannot find either column "GeoCodeSqlLib" or the user-defined function or aggregate "GeoCodeSqlLib.GeoCodeLib.GetLatLon", or the name is ambiguous.

I've tried:

CREATE FUNCTION GetLatLon(@amount varchar(max)) RETURNS varchar(max) 
AS EXTERNAL NAME GeoCodeSqlLib.GeoCodeLib.GetLatLon

That gives me the message, Could not find Type 'GeoCodeLib' in assembly 'GeoCodeSqlLib'.

I'm clearly missing something. The documentation's samples are all really basic and whatever step I'm missing, I just haven't been able to find it in the documentation.

Update

Thanks for all the help. So a few things:

• I need to make it external access to get be able to make a web service call.. I didn't realize I needed to create a SQL Server project. I was just doing a regular C# class library.
• So I created the SQL Server project. The SQL Server project doesn't support web service references. I tried adding a reference to my original DLL, so the new SQL Server project DLL acts as a proxy to the original DLL (the code in my original question).
• This, however, gives me: Assembly 'geocodesqllib, version ...' was not found in the SQL catalog. But I can't add it to the catalog because it needs External Access, which I can't do from a non SQL Server project (that I know of).

They really don't want to make this easy, do they?

Update 2

As per my comments below, the final issue appears to be the config file. It isn't finding the setting. The name of the server has been changed to protect the innocent:

<applicationSettings>
    <GeoCodeSqlLib.Properties.Settings>
        <setting name="GeoCodeSqlLib_ourserver_GeoCode" serializeAs="String">
            <value>http://ourserver/GeoCode.svc</value>
        </setting>
    </GeoCodeSqlLib.Properties.Settings>
</applicationSettings>

The error I get is:

System.Configuration.SettingsPropertyNotFoundException: The settings property 'GeoCodeSqlLib_ourserver_GeoCode' was not found.

Answer 1

A few things:

1. As Jeroen Mostert mentioned in a comment on the question, SQLCLR methods must be declared as static.

2. In your CREATE FUNCTION statement, you need to use NVARCHAR instead of VARCHAR. SQLCLR does not support VARCHAR.

3. In your CREATE FUNCTION statement, there is no need to use MAX for the input parameter and return type. A latitude and longitude combination will always be less than 4000 characters, and I am pretty sure that the address will also be less than 4000 characters. There is a definite performance hit for there being even a single MAX datatype in the signature. You are better off making both be NVARCHAR(4000).

4. If you still get the "cannot find type" error, then most likely you also have a namespace that is not being shown in the code in the question. The EXTERNAL NAME syntax is:

   EXTERNAL NAME AssemblyName.[NamespaceName.ClassName].MethodName;
   

   So that should end up looking something like:

   CREATE FUNCTION dbo.GetLatLon(@Address NVARCHAR(4000))
   RETURNS NVARCHAR(4000)
   AS EXTERNAL NAME GeoCodeSqlLib.[{namespace_name}.GeoCodeLib].GetLatLon;
   

5. Regarding this statement:

   I have targeted .NET Framework 2.0. We're using SQL Server 2008 R2

   You can use up to .NET Framework version 3.5 as 3.0 and 3.5 run in CLR 2.0, and SQL Server 2005, 2008, and 2008 R2 are linked to CLR 2.0.

---

As per the UPDATE section in the question:

No, you don't need to make the Visual Studio Project a "Database Project", but it does make publishing it a bit easier.

In order to set the GeoCodeSqlLib library to be EXTERNAL_ACCESS, you can do that via Project Properties of the Database Project in Visual Studio. You can also add WITH PERMISSION_SET = EXTERNAL_ACCESS to your CREATE ASSEMBLY statement, which is all the Visual Studio publish process (handled by SSDT) is doing anyway.

Now, in order to be able to set any Assembly to EXTERNAL_ACCESS (or even to UNSAFE) you need to do the following:

1. Sign the Assembly (also can be done easily in Visual Studio Project Properties) and be sure to "protect the key file with a password".

2. Build the project (not publish) so that it creates the DLL.

3. In the master Database, create an Asymmetric Key from that DLL.

4. Still in master, create a Login from that Asymmetric Key.

5. Grant the new Key-based Login the EXTERNAL ACCESS ASSEMBLY permission.

Now you can use WITH PERMISSION_SET = EXTERNAL_ACCESS (in either CREATE ASSEMBLY or ALTER ASSEMBLY statements for any Assembly signed with that particular key) without getting an error.

It might help to get a clearer sense of how to work with SQLCLR first. Please see the series of articles I am writing on this topic on SQL Server Central (free registration is required to read the content on that site):

Stairway to SQLCLR

Level 7 in that series even describes a method for using Visual Studio / SSDT to manage the steps noted above to get this working in an automated fashion, since VS / SSDT doesn't handle this aspect of security (which is sad as it encourages people to go the easy route of setting the Database to TRUSTWORTHY ON which is a security hole). And my next article will describe an even easier method using T-4 templates within Visual Studio.

---

For the Update 2 section and additional comments on this answer:

1. Since there is a single App Domain per each Database / Owner combination, all sessions executing code within a particular assembly will be sharing that exact code and memory. If you have a static class variable (i.e. a variable that retains its value for the life of the App Domain) then it is shared across all sessions, and this can lead to unexpected / unreliable behavior. For this reason, using a static class variable requires that the Assembly be marked as UNSAFE. Else, you can have a static class variable in SAFE and EXTERNAL_ACCESS Assemblies if the variable is marked as readonly.

2. Regarding app config files, you can use them, but the "app" in this case is SQL Server. So, you can always place configuration details in the machine.config that is global to all process of a particular CLR version (CLR 2.0 in your case), or you can create a config file for SQL Server as shown in this answer of mine, also here on S.O.:

   Does SQL Server CLR Integration support configuration files?