1

I am trying to execute a UDF which uses a CLR assembly. Each time I try to run it I get the below error:

Msg 6522, Level 16, State 1, Line 45
A .NET Framework error occurred during execution of user-defined routine or aggregate "Geocode":
System.NullReferenceException: Object reference not set to an instance of an object.

at ProSpatial.UserDefinedFunctions.GeocodeUDF(SqlString countryRegion, SqlString adminDistrict, SqlString locality, SqlString postalCode, SqlString addressLine)

It is a geocoding assembly, which is based on the below blog:

https://alastaira.wordpress.com/2012/05/04/geocoding-in-sql-server-with-the-bing-maps-locations-api/

Is there anything I can do to fix it? It was working fine, but just stopped working recently. I changed the Bing Maps key to a new one, but that hasn't resolved the issue. Anyone have any ideas?

Cheers

Edit: I entered a formatted URL into Chrome, to see if I can get the error. I entered the below URL format:

http://dev.virtualearth.net/REST/v1/Locations?countryRegion={0}&adminDistrict={1}&locality={2}&postalCode={3}&addressLine={4}&key={5}&output=xml

All I did was to replace items 0 to 5 with their respective entries. I left the curly brackets in there, and then also tried it without the curly brackets. Without the curly brackets, the URL returned a result with no issues.

In the browser, I am getting geocoded results, but not in SQL any more

Improve this question
7
  • If the code hasn't change then it could very well be that the response from Bing has changed. That code does not have very good error handling so you should paste what should be the formatted urltemplate value into a browser to see what is returned. I'm guessing you will see either an error or XML in a different format than what was previously being sent. Commented Aug 3, 2020 at 15:56
  • Any idea how I can retrieve the formatted URL from SQL server? Commented Aug 3, 2020 at 16:07
  • See main post, for UL error. Do you have any further pointers? Commented Aug 3, 2020 at 16:13
  • Are you sure your API Key is valid? If so, you will need to check Bing Maps API documentation for that error. Some info here: learn.microsoft.com/en-us/bingmaps/rest-services/… Also, for the test you are doing in Chrome: 1) you definitely need to remove the curly braces, and 2) you definitely need to fill out item #5 as that is your authorization key. Also, encode spaces in any of those replacements as %20. Commented Aug 3, 2020 at 16:30
  • Sorry I edited my answer - I did replace fields 0 to 5, not 4 as originally written. The API key works fine in Chrome, I am getting geocoded results as expected. Running the assembly throws the error as outlined in main post. Commented Aug 3, 2020 at 16:32

2 Answers 2

Reset to default
0

Just worked it out. One of the address fields that was being geocoded was null in the database, and the API did not like that. So I removed that from the geocoding list

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

1 Comment

I'm pretty sure that the Bing API never got the request. Please see my answer for details.
0

Looking at the code in that blog, if you are passing in a NULL, then it's not the API that's throwing the error. The error is happening because NULL values are not being handled when the method is first called. The code just casts the SqlString input parameters to string without first checking to see if there is a value in the SqlString variable. SqlString is very similar to a nullable string.

Fortunately, it is rather easy to adjust the code to properly handle NULL values in the input parameters. Starting with a redacted snippet of the original code below, I will show how to do this for one parameter and you can repeat that modification for the others (well, the ones that might actually be NULL in the DB; no need to do this for parameters that are guaranteed to be there due to being in a NOT NULL column).

    public static SqlGeography GeocodeUDF(
      SqlString countryRegion,
      SqlString adminDistrict,
      SqlString locality,
      SqlString postalCode,
      SqlString addressLine
      )
    {
 
      ...

      string localAdminDistrict = string.Empty;

      if (!adminDistrict.IsNull)
      {
          localAdminDistrict = adminDistrict.Value;
      }

 
      // Attempt to geocode the requested address
      try
      {
        geocodeResponse = Geocode(
          (string)countryRegion,
          localAdminDistrict,
          (string)locality,
          (string)postalCode,
          (string)addressLine
        );
      }

All I did was:

  1. Added a local variable for localAdminDistrict, defaulted to an empty string.
  2. Added an if block to set localAdminDistrict if adminDistrict is not null.
  3. Updated the call to Geocode() to use localAdminDistrict instead of (string)adminDistrict.

This should work as long as the Bing Maps API is ok with an empty value for adminDistrict (i.e. ...&adminDistrict=&locality=... ), as opposed to adminDistrict not being present in the GET request (which is easy enough, it just requires an additional modification).

ALSO: As long as you are going to be in there updating the code, you really should move the calls to the Close() and Dispose() methods into a finally block for that try / catch.

For more info on working with SQLCLR in general, please visit: SQLCLR Info

Improve this answer

Comments

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.