3

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.
Improve this question
2
  • 1
    The method must be static. Commented Jan 6, 2017 at 16:31
  • Concerning the comment, you are right, I missed that part of the error message. Deleting my answer. Thanks! Commented Jan 6, 2017 at 20:57

1 Answer 1

Reset to default
5

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?

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Thanks. I'm getting close, but still have a couple of hiccups.
@Pete I just updated with more info to address your UPDATE section.
I think we're almost there, but I'm waiting for my DBA to return and do the last little bit. Thank you so much for your help. I'll accept the answer once it's working. I haven't read your article in detail yet, but I did notice that the namespace (which tripped me up a bit) isn't really addressed in your series. I've never created code in C# that wasn't in a namespace and had you not covered it in the CREATE FUNCTION example above, I wouldn't have figured it out. I think that's an important thing to point out!
@Pete True, I did not cover the namespace thing. Primarily because for what I usually do, I had not been using namespaces much so never really encountered that. But, you are correct that it is important enough to cover, so I will see about going back to update that graphic showing how the various pieces fit together. Thanks for mentioning that :)
We still don't have it quite working because apparently the web service stuff using threading under the hood which requires unsafe access and that's a step our DBA doesn't want to take, so I'm going to go put together some web service code that doesn't use threads and I think we'll be good. Thanks for the help.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.