Deploying Sitefinity site that makes use of geo-location functionality throws error Unable to load DLL 'SqlServerSpatial.dll when accessing the geo-location functionality.

Using Sitefinity API for GeoLocation as per Sitefinity documentation, geo location service #filter dynamic content items https://www.progress.com/documentation/sitefinity-cms/144/for-developers-geo-location-service#filter-dynamic-content-items
an error occurs when using SortByDistance.

return ((IGeoLocationManager)manager).SortByDistance<DynamicContent>(itemsList, geoLocationsList, latitude, longitude, DistanceSorting.Asc).ToList();

 

The issue is caused by a bug in Sitefinity.

Sitefinity cannot find SqlServerSpatial.dll compatible with Microsoft.SqlServer.Types version 10.50.1600.1.
Sitefinity can`t install NuGet package Microsoft.SqlServer.Types with version higher than 10.50.1600.1 and use one of the newer versions of SqlServerSpatial.dll, e.g. SqlServerSpatial140.dll / SqlServerSpatial160.dll

The assembly SqlServerSpatial.dll must be present on the machine where Sitefinity runs. The assembly SqlServerSpatial.dll is by default shipped with SqlServer 2008 R2.

 

None at this time.
The bug will not have resolution in the long term. Refer to the workaround. 

 

1. Load a version of SqlServerSpatial.dll compatible with Microsoft.SqlServer.Types version 10.50.1600.1.
A function to load a predefined version of SqlServerSpatial.dll is needed to accommodate the workaround.
Create a class

using System;
using System.IO;
using System.Runtime.InteropServices;

namespace SitefinityWebApp
{
    public class SqlServerSpatialUilities
    {
        [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern IntPtr LoadLibrary(string libname);
        
        public static void LoadNativeAssemblies(string rootApplicationPath)
        {
            var nativeBinaryPath = string.Empty;
            if (IntPtr.Size > 4)
            {
                nativeBinaryPath = Path.Combine(rootApplicationPath, @"SqlServerTypes\win-x64\");
            }
               
            LoadNativeAssembly(nativeBinaryPath, "SqlServerSpatial.dll");
        }
        private static void LoadNativeAssembly(string nativeBinaryPath, string assemblyName)
        {
            var path = Path.Combine(nativeBinaryPath, assemblyName);
            var ptr = LoadLibrary(path);
            if (ptr == IntPtr.Zero)
            {
                throw new Exception(string.Format(
                    "Error loading {0} (ErrorCode: {1})",
                    assemblyName,
                    Marshal.GetLastWin32Error()));
            }
        }
    }
}

2. Add file Global.asax.cs if its not present in the site and 

protected void Application_Start(object sender, EventArgs e)
{
   SqlServerSpatialUilities.LoadNativeAssemblies(SystemManager.CurrentHttpContext.Server.MapPath("~/bin"));
}

3. In the /bin folder of the Sitefinity site create the folder structure: \bin\SqlServerTypes\win-x64\ and inside it place SqlServerSpatial.dll compatible with Microsoft.SqlServer.Types version 10.50.1600.1.

4. The file SqlServerSpatial.dll can be obtained from:
https://www.microsoft.com/en-us/download/details.aspx?id=44272 download file SQLSysClrTypes.msi
Download the installer SQLSysClrTypes.msi and after installing the file will be located in:

\Windows\system32
\Windows\Syswow64

The installer doesn`t say where the installed files will go. Use the search feature in windows when you are in the Windows folder and search for SqlServerSpatial.dll.

or get the file from the file attachment to this article.

A side-effect is a dummy message when the site is starting up. Ignore it. Attempt to load \SqlServerSpatial.dll failed.



 

---

Alternatively refer to the suggested resolutions in the below two articles:

Stackoverflow, questions 14265845 unable to load sqlserverspatial dll http://stackoverflow.com/questions/14265845/unable-to-load-sqlserverspatial-dll
Stackoverflow, questions 16868326 unable to load dll sqlserverspatial dll http://stackoverflow.com/questions/16868326/unable-to-load-dll-sqlserverspatial-dll
Based on the setup of the current machine where the site runs one of the listed suggestions will resolve the issue. The most common case is to add SqlServerSpatial.dll to the site assembly references.