Spatial SQL types

Solving the “Spatial types and functions are not available …” -problem

This page describes mutiple ways on how to fix the exception ‘Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.‘ which comes up during debugging or publishing your program, app or service using DbGeography.

Symptoms

While running a console program or perhaps installing an ASP.NET MVC website on a machine, where SQL Server (apart from the one that comes with the Visual Studio) has not been installed, you may encounter the following error:

Spatial types and functions are not available for this provider because the assembly ‘Microsoft.SqlServer.Types’ version 10 or higher could not be found.

At least for me, this was baffling as that assembly was included in the project, and would compile and run flawlessly on other machines, just not on this one. I’m documenting here all the possible fixes to the issue I am aware of.

Problem / Reason

For me, the actual reason was that even though the DLL was loaded and included in the project as a nuget package, the actual native assemblies for this dll were not loaded to memory, as this needs to be done in the runtime. This issue was made worse by the fact that I first created this solution on a machine, where SQL Server was installed, so I was very much oblivious of the issue until it hit me on the face while trying to run my program on this particular machine, which did not have SQL Server.

Solution

After googling around, I found a few non-working solutions – they may work in other contexts, so I’m documenting them here.

  1. Install the Microsoft.SqlServer.Types NuGet package.
    Install-Package Microsoft.SqlServer.Types

    For my another dev machine this alone was enough to get me going.

  2. If you have it already, and the issue persist, reinstall it:
    Update-Package -reinstall Microsoft.SqlServer.Types

    For me, this added the much-needed dlls to the actual project, as they didn’t exist on this machine (but did on the other, where I first ran this command).

  3. If it’s still throwing the exception, you’ll need to load the assemblies on the fly – this can be done this way:

    ASP.NET applications

    For ASP.NET applications, add the following line of code to the Application_Start method in Global.asax.cs:

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

    Desktop applications

    For desktop applications, add the following line of code to run before any spatial operations are performed:

    SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

    For the record, this worked for me.

  4. If it’s still not working, you can try installing the SQL server on the dev machine. Pain in the ass, I know, but it should fix the situation in case nothing else works.

Good luck!

The following two tabs change content below.

Antti K. Koskela

Solutions Architect / Escalations Engineer at Koskila / Norppandalotti Software / Valo Solutions
Antti Koskela is a proud digital native nomadic millenial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as a Solutions Architect for Valo Intranet, the product that will make you fall in love with your intranet. Working with the global partner network, he's responsible for the success of Valo deployments happening all around the world. He's been a developer from 2004 (starting with PHP and Java), and he's been bending and twisting SharePoint into different shapes since MOSS. Nowadays he's not only working on SharePoint, but also on .NET projects, Azure, Office 365 and a lot of other stuff. This is his personal professional (e.g. professional, but definitely personal) blog.

4 thoughts on “Solving the “Spatial types and functions are not available …” -problem

  1. Hello.
    First of all thank you for solving the problem.

    I get the same error while running the test in the UnitTest project, and I keep getting the same error when I make the package install and reinstall.

    Have you tried anything for that?

    1. Hey Serdar,

      Thanks for your comment! I haven’t, unfortunately.. Does the error occur even when running the tests locally? Have you been able to include the DLLs in the test project as well, using the instructions on step 3 in my blog post?

  2. I got this problem after rebuilding my machine and after many attempts and various Google searches — this resolved the error. Thanks!

Let me know your thoughts!