Unable to load SqlServerSpatial.dll

问题:

I am trying to use the SqlServer Spatial CLR types in a C# .Net project. I want to use SqlGeometry to query spatial records out of my db.

I have this working on my local machine in a unit test running in Visual Studio 2010 hitting a remote SqlServer machine. All good.

I then publish a WCF Rest service to my local IIS instance that has a service that hits the same class library as the unit test to do some spatial querying and it fails.

I get an error saying

Unable to load DLL SqlServerSpatial.dll : The specified module could not be found.

I have googled this and found many, many answers - none work for me. I have:

• registered the CLR types with the GAC
• install the 64-bit, and later also, the 32-bit version of the VC++
• tried many variations of using different Microsoft.SqlServer.Types dll versions

The only thing I have not done, and frankly refuse to do, is to install anything on the actual SqlServer box. This seems unnecessary to me.

At this point the only thing that I can think is causing this is a permissions issue because it is running in an IIS app pool and not inside Studio where it works in the unit test.

Note that in my project I NEVER make reference to the dll mentioned in the error message. That dll is present on the sql box but I can't add it to studio as it gives some message when i try to. I'm running out of things to try here. It's 90's dll hell all over again.

回答1:

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

回答2:

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.

回答3:

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 types It 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); //WPF
Utilities.LoadNativeAssembliesv13(HttpRuntime.BinDirectory); //ASP.NET 

Issues when using ADO.NET to read SqlGeometry from Sql Server Despite 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;
}

回答4:

I was having issues on a Windows Server 2008 R2 machine (Azure VM), but none of the steps above were able to fix the issue. I installed the CLR types. I put the files in my web application's BIN folder. Still nothing. I finally came across this blog by the folks at Microsoft and it worked. I'm leaving the url here in case it can help anyone else.

http://blogs.msdn.com/b/adonet/archive/2013/12/09/microsoft-sqlserver-types-nuget-package-spatial-on-azure.aspx

Since the above link no longer works (thanks MSFT!), I've put instructions below:

1. Open Visual Studio and open the NuGet Package Manager
2. Search for "Microsoft.SqlServer.Types"
3. Install...

This package will install the necessary .DLLs into your solution/project. It will also copy some additional libraries directly into your /bin directory. You must wire up references to these additional libraries in your global.asax.cs/vb file. There are instructions on how to do this included in the NuGet package. Below is a direct link to the NuGet Package (hopefully MSFT doesn't move this into oblivion too).

https://www.nuget.org/packages/Microsoft.SqlServer.Types/

回答5:

Despite having SQL Server 14.x installed, VS kept insisting SqlServerSpatial110.dll was not found.

Installing Microsoft System CLR Types for SQL Server 2008 R2 did not fix it. I also tried to install the 10.5 version of Microsoft.SqlServer.Types, but received a PInvoke error about the method signature not matching.

So instead, I installed Microsoft.SqlServer.Types 14.x, then renamed the SqlServerSpatial140.dll file to SqlServerSpatial110.dll in both /x86 and /x64 folders and did the same in Loader.cs. For whatever reason, that seemed to do the trick.

回答6:

I had the same issue in godaddy VPS with windows server 2012 r2

I Resolved it by Updating my EF5 to EF6

in package manager console run to EF5 to EF lalest

Install-Package EntityFramework 

回答7:

Remove the Microsoft.SqlServer.Types.dll from References and use Nuget to instal. Check your version before install. The assemblies to x86 and x64 will be installed in the project.