Partager via

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

  • Article

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.

Comments

  • Anonymous
    December 09, 2013
    This is quite awesome. Since there is no pure CLR solution, I assume you can't use the GeoSpatial types in mono?

  • Anonymous
    December 09, 2013
    @Justin Dearing - Correct, at least not the SQL Server implementations.

  • Anonymous
    December 16, 2013
    It's now common to deploy both the x86 and the x64 binaries. Then load the right one at runtime using a statement like this: Native.LoadLibrary(Path.Combine(IntPtr.Size > 4 ? "x64" : "x32", "Sqlite.Interop.dll")); I'd like to see this package implement this pattern. This has the advantage of working with "Any CPU". It eliminates the need for the developer to decide which item he wants to keep as part of his project.

  • Anonymous
    December 18, 2013
    @Brannon - Good idea, I'm working on an update now that uses this approach.

  • Anonymous
    December 19, 2013
    The comment has been removed

  • Anonymous
    February 12, 2014
    @Jeremy - Same here. Deploying the 2 DLLs (SqlServerSpatial110.dll and Microsoft.SqlServer.Types.dll) to the bin folder of my app did not fix this issue. The strange thing is, sometimes SqlServerSpatial110 DOES get detected, but usually not. It's sporadic. I might need to install the CLR types on the web servers and be done with it... George

  • Anonymous
    February 12, 2014
    Follow-up: My "intermittent" issue was caused by web farm nodes being out of sync. One node already had geo types installed, the others didn't. I installed the types on all nodes and we're good now. It looks like the SqlServerSpatial110 dll has dependencies on a number of other dlls that are not guaranteed to exist. George

  • Anonymous
    February 20, 2014
    @Jeremy & @George - I just pushed an update to the package (11.0.1) that uses a different approach to load the native assemblies explicitly from code (without needing to copy them to the output directory manually). That should solve (or help you diagnose) issues with resolving the native assemblies.

  • Anonymous
    March 19, 2014
    Thanks @Rowan for the update. For now I think I'm fine installing the types anyway, but this package will be handy in the future! George

  • Anonymous
    March 20, 2014
    @Rowan and @Jeremy: I pulled down the latest package that Rowan mentions, and I can confirm that now all necessary DLLs get deployed  correctly. I had a production deployment and did not want to take down any nodes in order to install the types (I would have had to deal with taking down nodes, wait to drain active connections, coordinate with IT group, etc). So I went with the latest NuGet package and we're in business! Thanks Rowan. George

  • Anonymous
    April 03, 2014
    did Global.asax SqlServer Types.Utilities.Load Native Assemblies (Server.MapPath ("~ / bin")); did not work, where am I going wrong?

  • Anonymous
    April 04, 2014
    @Wans - Are you seeing an error? Any chance you can post more details?

  • Anonymous
    May 07, 2014
    We had an issue with the transition from SQL Server 2008 R2 to SQL Server 2012. Even though we added the Nuget Package to our MVC application we kept getting the "DataReader.GetFieldType(...) returned null" Exception. The cause of the issue can be read here: technet.microsoft.com/.../ms143179.aspx TLDR: The SqlClient loads by default the Microsoft.SqlServer.Types Version 10.0 and with SQL Server 2012 V. 11 is needed. This problem can be solved by adding the following to the web.config. <dependentAssembly>        <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />        <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0" />      </dependentAssembly> It would be great if the Nuget package could be updated to automatically add this to the web.config.

  • Anonymous
    May 08, 2014
    @Arne Klein - Feel free to open an issue for us to make this change entityframework.codeplex.com/.../Create

  • Anonymous
    May 29, 2014
    everything is working correcty in my local compile however when i publish to azure. I am having the same issue as @Wan Environment is Azure Mobile Service .net backend public class WebApiApplication : System.Web.HttpApplication    {        protected void Application_Start()        {            SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));            WebApiConfig.Register();        }    } the dll's  are copy to output director : copy allways yet after publishing I receive this message in the azure log Could not load assembly 'D:homesitewwwrootbinSqlServerTypesx86SqlServerSpatial110.dll'. Error received: 'Could not load file or assembly 'file:///D:homesitewwwrootbinSqlServerTypesx86SqlServerSpatial110.dll' or one of its dependencies. The module was expected to contain an assembly manifest.'. Could not load assembly 'D:homesitewwwrootbinSqlServerTypesx86msvcr100.dll'. Error received: 'Could not load file or assembly 'file:///D:homesitewwwrootbinSqlServerTypesx86msvcr100.dll' or one of its dependencies. The module was expected to contain an assembly manifest.'. Could not load assembly 'D:homesitewwwrootbinSqlServerTypesx64SqlServerSpatial110.dll'. Error received: 'Could not load file or assembly 'file:///D:homesitewwwrootbinSqlServerTypesx64SqlServerSpatial110.dll' or one of its dependencies. The module was expected to contain an assembly manifest.'. Could not load assembly 'D:homesitewwwrootbinSqlServerTypesx64msvcr100.dll'. Error received: 'Could not load file or assembly 'file:///D:homesitewwwrootbinSqlServerTypesx64msvcr100.dll' or one of its dependencies. The module was expected to contain an assembly manifest.'.

  • Anonymous
    June 02, 2014
    @Carroll Lee - I've opened a new work item for us to track the issue you are seeing - entityframework.codeplex.com/.../2311.

  • Anonymous
    October 22, 2014
    The comment has been removed

  • Anonymous
    October 22, 2014
    Arne Klein's solution below worked for me: <dependentAssembly>       <assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />       <bindingRedirect oldVersion="10.0.0.0" newVersion="11.0.0.0" />     </dependentAssembly>

  • Anonymous
    October 27, 2014
    @Chris - Glad you found a solution :)

  • Anonymous
    November 17, 2014
    After adding this to my VS2013 Web API V2 Project using EF6, I always get the file is in use when building my solution after a run.  I run Visual Studio as Administrator because I need IIS Express to have access to port 80 so Fiddler can redirect the client requests to me for debugging.  I think its the Admin privs that causes the file in use error (from what I have found minimal references on the internet for this).  If I remove the package I no longer have this problem.  I really want to use this with EF to replace the complicated Stores Procedures for calculating distances in queries.  Any advice?  Currently I'm having to manually stop the IIS Express site before building and its getting tiresome.

  • Anonymous
    November 17, 2014
    @Dale - Perhaps try changing the 'Copy to Output Directory' setting on the files in the SqlServerTypes folder to 'Copy if Newer' rather than 'Copy Always'. That may resolve the issue for you.

  • Anonymous
    February 04, 2015
    Can we have an update to this package to use a .targets file (see link) so we don't need to include assemblies in our solution. npe.codeplex.com/.../462174

  • Anonymous
    February 05, 2015
    @Steve Hipwell - Could you open a request here entityframework.codeplex.com/.../Create. I'm not promising we'll do it right away, but I agree it would be good to do.

  • Anonymous
    March 26, 2015
    We're using the NuGet package for this, however Microsoft Code Analysis rules fail, specifically CA2101 and CA1060.  Any plans to make this right?

  • Anonymous
    May 12, 2015
    @Brent Pabst - Can you open an issue on our CodePlex project and we'll look at correcting the violations entityframework.codeplex.com

  • Anonymous
    August 07, 2015
    Does this package work with SqlServer2014 (v12) on azure?

  • Anonymous
    October 22, 2015
    Well, i just installed the package and it hosed my application.  I'm getting this error and have no idea what to do about it: Error Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.

  • Anonymous
    December 22, 2015
    Is there a package available for version 12?

  • Anonymous
    December 23, 2015
    @jrummell - no v12 package yet. Feel free to open a request for one and we'll add it to our queue of work - entityframework.codeplex.com