38

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?

ChirpingPanda
  • 50
  • 4
reviloRetals
  • 485
  • 1
  • 7
  • 14
  • 1
    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. – OverBakedToast Jul 06 '23 at 04:26

10 Answers10

30

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:

enter image description here

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.

Design.Garden
  • 3,607
  • 25
  • 21
  • 3
    Depending on your `.gitignore`, you may need to add these 2 lines: 1) `!**/SqlServerTypes/x64/` and 2) `!**/SqlServerTypes/x86/` – Metro Smurf Aug 09 '22 at 20:11
  • 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. – Andy S. Feb 16 '23 at 20:44
28

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".

Kristoffer Jälén
  • 4,112
  • 3
  • 30
  • 54
24

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.

Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • 1
    I can't seem to find the `SqlServerTypes` class. – Shimmy Weitzhandler Apr 28 '17 at 13:34
  • Me too. Could not find SqlServerTypes – Venkat Dec 01 '17 at 17:26
  • 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. – Pure.Krome Dec 05 '17 at 02:55
  • 2
    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. – ChirpingPanda Mar 06 '18 at 09:11
  • No problems with our files getting noticed by git and as such, we've commited them to our git repo after installation from nuget. – Pure.Krome Mar 06 '18 at 22:13
  • For anyone still struggling with this on a build server: https://www.andrewcbancroft.com/2017/03/27/solving-spatial-types-and-functions-are-not-available-with-entity-framework/ – johnstaveley Nov 27 '18 at 10:41
  • 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." – Tim Sep 21 '20 at 17:14
  • 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. – Tim Sep 21 '20 at 17:24
  • To anyone not having the class `SqlServerTypes`, you should also copy that Loader.cs file. – Éric Bergeron Mar 16 '21 at 21:40
6

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
Jarosław Maruszewski
  • 71
  • 1
  • 6
  • This and [Design.Garden's answer](https://stackoverflow.com/a/52973513/9664) are what's needed when pulling down a fresh project that had not committed the assemblies to source control. – Metro Smurf Aug 09 '22 at 18:50
4

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://stackoverflow.com/a/39009634/6697928

nightwuffle
  • 311
  • 3
  • 10
  • 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. – Justin Jul 28 '23 at 23:05
3

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

Dharman
  • 30,962
  • 25
  • 85
  • 135
Brian W.
  • 118
  • 6
3

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

Dicpherrr
  • 117
  • 5
2

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.

Lauren
  • 43
  • 4
1

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.

Marc-Alexandre Chervaz
  • 36
  • 2
0

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

ascripter
  • 5,665
  • 12
  • 45
  • 68
Roar Jørstad
  • 105
  • 2
  • 5