Unable to load SqlServerSpatial.dll

I had the same problem on a Windows Server 2012 machine. It had an SqlServerSpatial110.dll file in \Windows\System32, but no SqlServerSpatial.dll. The solution was installing the Microsoft System CLR Types for SQL Server 2008 R2 on the machine.

1. http://www.microsoft.com/en-us/download/details.aspx?id=26728
2. Click Download

3. Check off one of these depending on your processor architecture:

   • 1033\x64\SQLSysClrTypes.msi
   • 1033\x86\SQLSysClrTypes.msi
   • 1033\IA64\SQLSysClrTypes.msi

4. Click Next

My problem was similar to yours: I installed my ASP.NET MVC project on a remote Azure Virtual Machine and I got this exception:

"Unable to load DLL 'SqlServerSpatial110.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)" 

To solve the issue I followed these steps:

1. I added the reference to the missing package in my project:

   PM> Install-Package Microsoft.SqlServer.Types

2. Then I forced the "Copy to output directory" option to "Copy always" for the SqlServerSpatial110.dll (probably this step is not strictly required...)

3. For ASP.NET projects, you need to add the following line of code to the Application_Start method in Global.asax.cs:

   SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));

   This last step was fundamental for me, because whitout this line of code the DLL is not loaded by the web application.

I have been using Microsoft.SqlServer.Types.dll in WPF and ASP.NET apps to work with SqlGeometry type and spatial queries for years (since v.10) and here is the latest tips I found to successfully load the SqlServerSpatialXXX.dll as one of the prerequisites of the Microsoft.SqlServer.Types.dll.

• SqlGeometry and SqlGeography types can be used in VS projects (e.g. C#) by referencing the Microsoft.SqlServer.Types.dll.
• Microsoft.SqlServer.Types.dll is a managed library and has some unmanaged library as prerequisites and they are like SqlServerSpatialXXX.dll and msvcrXXX.dll
• Since Sql Server 2008, different versions of Microsoft.SqlServer.Types.dll are available, however, I don't see any functionality change from 2012 on.

Consider 64bit/32bit issues

• For 64 bit machanies, if you install CLR Types for Sql Server, you can find 64bit versions of these prerequisites files in Windows/System32 and also you can find 32bit versions of prerequisites files in Windows/SysWOW64 folder
• If CLR Types are not installed on a machine, You should manually load proper versions (32bit/64bit) of these prerequisites based on your project (32bit or 64bit) otherwise you will errors like

Error Loading SqlServerSpatialXXX.dll

You can check 32bit/64bit issue at runtime in C# using Environment.Is64BitProcess. Here is a sample code:

[DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]private static extern IntPtr LoadLibrary(string libname);private static void LoadNativeAssembly(string nativeBinaryPath, string assemblyName){    var path = Path.Combine(nativeBinaryPath, assemblyName);    if (!File.Exists(path))    {        throw new FileNotFoundException($"{path} not found");    }    var ptr = LoadLibrary(path);    if (ptr == IntPtr.Zero)    {        throw new Exception(string.Format(            "Error loading {0} (ErrorCode: {1})",            assemblyName,            Marshal.GetLastWin32Error()));    }          }public static void LoadNativeAssembliesv13(string rootApplicationPath){    var nativeBinaryPath = Environment.Is64BitProcess    ? Path.Combine(rootApplicationPath, @"SqlServerTypes\x64\")    : Path.Combine(rootApplicationPath, @"SqlServerTypes\x86\");    LoadNativeAssembly(nativeBinaryPath, "msvcr120.dll");    LoadNativeAssembly(nativeBinaryPath, "SqlServerSpatial130.dll");}

Consider binary path in different project typesIt is recommended to have a folder named SqlServerTypes in the execution path of your project like this

SqlServerTypes>x64

SqlServerTypes>x32

and load unmanaged assemblies like this

Utilities.LoadNativeAssembliesv13(Environment.CurrentDirectory); //WPFUtilities.LoadNativeAssembliesv13(HttpRuntime.BinDirectory); //ASP.NET 

Issues when using ADO.NET to read SqlGeometry from Sql ServerDespite which version of Microsoft.SqlServer.Types.dll you are using, if you try to read them from Sql Server using ADO.NET you may encounter a cast exception because SQL Client will by default load version 10.0.0.0 of Microsoft.SqlServer.Types.dll. In this case some years ago I tried WKB (approach 1 and 2) and WKT as a medium to convert between SqlGeometry type for different version of Microsoft.SqlServer.Types.dll and found WKB is about 10 times faster but some month ago I found using assembly redirection we can force the program to load the version we are using and using a simple cast we can get the SqlGeometry (approach 3)

private List<SqlGeometry> SelectGeometries(string connectionString){    SqlConnection connection = new SqlConnection(connectionString);    var command = new SqlCommand(select shapeCol from MyTable, connection);    connection.Open();    List<SqlGeometry> geometries = new List<SqlGeometry>();    SqlDataReader reader = command.ExecuteReader();    if (!reader.HasRows)    {        return new List<SqlGeometry>();    }    while (reader.Read())    {        //approach 1: using WKB. 4100-4200 ms for hundred thousands of records        //geometries.Add(SqlGeometry.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes((byte[])reader[0]), srid).MakeValid());        //approach 2: using WKB. 3220 ms for hundred thousands of records        //geometries.Add(SqlGeometry.Deserialize(reader.GetSqlBytes(0)));         //approach 3: exception occur if you forget proper assembly redirection. 2565 ms for hundred thousands of records        geometries.Add((SqlGeometry)reader[0]);    }    connection.Close();    return geometries;}