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

Question

This all new to me, so bear with me...

I'm working on a Visual Studio project; it's a web service that returns some data.

I've just tried to make a particular call to the web server on my local machine (IIS) and I'm getting this error:

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

Before anyone says it - yes, obviously I am missing this DLL file. I've searched online and don't see where I could download it from (I have Microsoft SQL Server System CLR Types (x64) and non-X64 both installed. I have Microsoft System CLR Types for SQL Server 2014 and 2016 both installed)

Does anyone know how to fix this? Does anyone know if I can just download this file from somewhere?

Cane

SqlServerSpatial140.dll has been removed from NPM because it is malicious. If you have it installed, you should remove it and install the proper library for this application. – Reverso

Answer 1

Copy the dll from C:\Users\<User>\.nuget\packages\Microsoft.SqlServer.Types\14.0.314.76\nativeBinaries\x86 to your project. Right-click the file and click Properties. Set "Copy To Output Directory" to "Copy Always".

Somite

1) This option is not available for NuGet packages 2) What will happen when deploying to server? – Sinuation

@ShimmyWeitzhandler add it as a new folder SqlServerTypes in the project e.g. SqlServerTypes\x64 and SqlServerTypes\x86 and then apply the file property "Copy To Output Directory" to "Copy Always" – Mihalco

Answer 2

For those who are seeing a very similar set of errors, something like:

Could not copy the file "…\SqlServerTypes\x64\SqlServerSpatial140.dll" because it was not found

If you installed Microsoft.SqlServer.Types via NuGet and your application works locally but you get errors when building via Azure DevOps then you simply need to add the dlls to source control. As @Pure.Krome noted, these dlls exist locally at:

However, notice that by default these dlls are ignored (red icon at left). Right-click the ignored dlls and select Add Ignored File to Source Control… then commit and push your changes, then queue a new build! Note: Your solution may contain several projects, and each may have their own SqlServerTypes folder.

Mardis

Depending on your .gitignore, you may need to add these 2 lines: 1) !**/SqlServerTypes/x64/ and 2) !**/SqlServerTypes/x86/ – Control

This happened to me when pulling a fresh copy of source from git. The standard Visual Studio .gitignore prevented the files from getting checked in the first time, so anyone else pulling the code didn't have them, and their copy wouldn't compile. Had to remove and add the SqlServer Types nuget package, then add them to source control as mentioned above so they'll be available to others in the future. – Sarnen

Answer 3

When you install the Microsoft.SqlServer.Types nuget package, this should create a new folder in root:

\SqlServerTypes
   |_x64
   |_x86

which should contain the appropriate dll's. It's also auto setup to copy if newer.

Then, make sure your app loads the appropriate assembly:

• For ASP.NET Web Applications: SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
• For desktop applications / others: SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

Not sure if this works with .NET Core though.

Ambient

I can't seem to find the SqlServerTypes class. – Sinuation

Me too. Could not find SqlServerTypes – Byrom

Can you guys confirm if the file(s) exist in the solution at all? Before you build/package? After you've packaged and copied to a webserver? what version of .NET, etc. – Ambient

After installing the Microsoft.SQLServer.Types nuget package the msvcr120.dll and SqlServerSpatial140.dll files were added to solution but not to source control which is why we've had a similar issue after checking into TFS (or not as the case was). Make sure these dlls are checked in to source control before building on a build server. – Vonnievonny

No problems with our files getting noticed by git and as such, we've commited them to our git repo after installation from nuget. – Ambient

For anyone still struggling with this on a build server: andrewcbancroft.com/2017/03/27/… – Evolution

On my dev PC, I am unable to add a reference to the x86 or the x64 version of SqlServerSpatial140.dll in Visual Studio 2019 so that I can flag the DLL as one that needs to be copied to the local machine. The error is: "A reference to {path to the DLL} could not be added. Please make sure that the file is accessible, and that is a valid assembly or COM component." – Consanguineous

Once I moved the Spatial DLL from that x86 folder to the bin folder, I was able to add a reference to it. However, I still could not add a reference to "msvcr120.dll". Same error as above. – Consanguineous

To anyone not having the class SqlServerTypes, you should also copy that Loader.cs file. – Rothermere

Answer 4

My problem was git related: a fresh clone of solution (kept on TFS) seemed to be broken, although my fellow developers had no problem. In my case "resetting" did the trick:

• uninstall nuget Microsoft.SqlServer.Types 14.0.314.76
• clear / rebuild project
• install nuget Microsoft.SqlServer.Types 14.0.314.76
• clear / rebuild project

Cottrell

This and Design.Garden's answer are what's needed when pulling down a fresh project that had not committed the assemblies to source control. – Control

Answer 5

I had this problem but I found a solution for it. I had some new entities with the System.Data.Entity.Spatial.DbGeometry type and I kept getting the System.DllNotFoundException: Unable to load DLL 'SqlServerSpatial140.dll' when I ran the Add-Migration command. I had installed the SqlServerTypes library from nuget, which placed a folder in my solution like @mattavatar's post illustrated, but I was still getting the exception.

What fixed it for me was copying these DLLs to C:\Windows\SysWOW64. This is dependent on the architecture of your machine, and your IIS configuration. For my I am running on a 64 bit machine, but IIS is configured to run 32 bit apps. In this case, I had to copy the 32 bit dll to C:\Windows\SysWOW64. If you're running a 32 bit machine, you'll want to copy the 32 bit dll to C:\Windows\System32. If you copy a 64 bit dll where it is expecting a 32 bit one, and you run the Add-Migration command, you'll get the System.BadImageFormatException.

Hopefully this helps someone. I spent want too long trying to figure this out. Credit to @pflous' comment https://mcmap.net/q/392422/-unable-to-load-dll-39-sqlserverspatial-dll-39

Akmolinsk

This worked for me. Previous installations of SQL Server had put the DLLs into these folders, but the version of SQL Server I had installed was different than the version the application was trying to load. – Reek

Answer 6

I fixed this by issuing the following command in the Package Manager Console:

Update-Package -Reinstall Microsoft.SqlServer.Types

The output included some dire-looking warnings and errors, but in the end it indicated that the package was successfully installed. And the build errors vanished.

Lasso

Answer 7

What worked for me was to go to this file location below and copy the SqlServerSpatial240.dll to my project bin folder.

C:\Users<user>.nuget\packages\microsoft.sqlserver.types\14.0.1016.290\nativeBinaries\x64

Blackjack

Answer 8

Finally fixed the issue by doing the following.

1. Uninstalled the SqlServer.Types Nuget package

2. Reinstalled the SqleErver.Types Nuget package (Mainly did this to ensure the dll was correctly uploaded)

3. Verify you have the SqlServerTypes folder created, and verify you have x64/x86 with your dlls inside, if they aren't loaded, right click, click git, then click Add

4. Verify you have a Loader.cs in that folder, open it and verify its referencing the correct dll.

5. Finally, this step is what fixed it for me, go to your Global.asax.cs file. Inside, Application_Start() add this line

#if DEBUG

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

#endif

Clasp

Answer 9

Thank you very much. It worked for me very well. I lost a lot of time on this, but you saved me! :)

I added this to my Global.asax.cs =>

 `protected void Application_Start()
    {
     SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));`

And then in my Solution Explorer => toggled show all file and Add Ignored File to Source Control… as Pure.Krone said.

Stein

Answer 10

In my case, finally did the trick:

1. Upgrading entire solution to EntityFramework 6.4.4

2. Upgrading nuget Microsoft.SqlServer.Types to 160.1000.6

3. Removing any reference to SqlServerTypes.Utilities.LoadNativeAssemblies whatsoever

Selfrising

Answer 11

I had a similar problem and my files from the sub-folder \SqlServerTypes (installed as @Pure.Krome described) where missing/discarded by my settings in NuGet.Config. I had to uninstall and reinstall the Nuget package Microsoft.SqlServer.Types

Laquanda

Answer 12

I tried manually copying the files msvcr120.dll and SqlServerSpatial140.dll to the 64 bit and 32 bit Windows directories (C:\Windows\SysWOW64 and C:\Windows\System32) but this did not work.

What worked for me was to install the Microsoft System CLR Types for SQL Server 2017 on the machine in both x86 and x64 versions.

Without that I would continue to see the following MSBUILD errors...

"ResolveAssemblyReferences:
         Primary reference "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL".
     9>C:\Program Files (x86)\MSBuild\12.0\bin\amd64\Microsoft.Common.CurrentVersion.targets(1697,5): warning MSB3245: Could not resolve this reference. Could not locate the assembly "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors."

         Considered "SqlServerTypes\x64\msvcr120.dll", but its name "msvcr120" didn't match.
         Considered "SqlServerTypes\x64\SqlServerSpatial140.dll", but its name "SqlServerSpatial140" didn't match.
         Considered "SqlServerTypes\x86\msvcr120.dll", but its name "msvcr120" didn't match.
         Considered "SqlServerTypes\x86\SqlServerSpatial140.dll", but its name "SqlServerSpatial140" didn't match.
         For SearchPath "{HintPathFromItem}".
         Considered "..\packages\Microsoft.SqlServer.Types.14.0.314.76\lib\net40\Microsoft.SqlServer.Types.dll", but it didn't exist.
         For SearchPath "{TargetFrameworkDirectory}".

         Considered "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL", which was not found in the GAC."

This combination resolved the issue...

• I installed SQLSysClrTypes.msi in both the x64 and x86 versions which is part of the Microsoft SQL Server 2017 Feature Pack from https://www.microsoft.com/en-us/download/details.aspx?id=55992

Confirm the Microsoft.SqlServer.Types Assembly Name is listed in the assembly cache at: C:\WINDOWS\assembly\

or in: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.Types\

• I installed the Microsoft.SqlServer.Types nuget package to the project

• I added the SqlServerTypes folder that was created by the nuget package installation including the x64 and x86 folder contents to source control (msvcr120.dll and SqlServerSpatial140.dll)

• For both msvcr120.dll and SqlServerSpatial140.dll change the File Property "Copy to Output directory" to "Copy always"

Finally the following configuration key was added to the app.config for the project.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
                <bindingRedirect oldVersion="0.0.0.0" newVersion="14.0.0.0"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
</configuration>

Mihalco