Microsoft.SqlServer.Types NuGet Package (Spatial on Azure)

Any application that uses Entity Framework’s spatial data type support to target SQL Server requires the ‘CLR Types for SQL Server’to be available on the machine the application runs on. This also applies to applications that use SQL Server spatial data types directly, without using Entity Framework.

 

Deployment Issues

When developing your application the CLR Types for SQL Server are usually installed system-wide, since they are included in Visual Studio. Issues arise when you try to deploy to a machine that does not have the CLR Types for SQL Server installed. Initially you will get the following InvalidOperationException.

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

If you were to find and deploy the Microsoft.SqlServer.Types assembly you’ll then get the following DllNotFoundException.

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

 

The Solution

If you have control over the server you can just install the CLR Types for SQL Server. The SQL Server 2012 SP1 version of the CLR Types can be downloaded here. SQLSysClrTypes.msi is the installer you want and there is an x86 (32 bit) and x64 (64 bit) version depending on the architecture of the machine you are deploying to.

However, installing extra software on the target machine is not always an option – especially if you are deploying to a machine you don’t own (such as Windows Azure Web Sites). Fortunately the required assemblies can be deployed along with your application.

  • Step 1: Install the Microsoft.SqlServer.Types NuGet package.

    PM> Install-Package Microsoft.SqlServer.Types

  • Step 2: Ensure the appropriate version of the native SqlServerSpatial110.dll assembly is copied to the output directory and deployed with your application. Steps on how to do this are included in a ReadMe.txt file that will open in Visual Studio when you install the package.

 

What the Microsoft.SqlServer.Types Package Does

The Microsoft.SqlServer.Types package gives you the two assemblies that need to be deployed along with your application:

  • Microsoft.SqlServer.Types.dll – This is a .NET assembly that is added as a reference to your project. This assembly will be automatically copied to the output directory of you application and deployed for you.
  • SqlServerSpatial110.dll – This is a native assembly so it cannot be added as a project reference. Instead, installing the NuGet package will add the x86 and x64 version of this assembly as items in your project under a SqlServerTypes folder. You will need to ensure the appropriate version of this assembly is loaded at runtime. Steps on how to do this are included in a ReadMe file that will open in Visual Studio when you install the package.