在 Azure 上找不到“ Microsoft.SqlServer.Type”10或更高版本

我正在尝试用 ASP.NET MVC 4制作一个 webapi。Webapi 使用实体框架5空间类型，我写了一个非常简单的代码。

  public List<Area> GetAllAreas()
{
List<Area> aList = db.Areas.ToList();
return aList;
}

区域包含 DbGeography。

当我在本地运行这个程序时，它工作正常，但是当我将它发布到 Azure 时，它会给我一个错误:

由于程序集“ Microsoft”，此提供程序无法使用空间类型和函数。SqlServer.找不到类型10或更高版本。

有人知道如何解决这个问题吗? :)

谢谢！

70087 次浏览
小开
最佳答案

I found the solution ! Just install the nuget package Microsoft.SqlServer.Types

PM> Install-Package Microsoft.SqlServer.Types

Link for more info

小开

I also encountered this problem, but the Microsoft.SqlServer.Types nuget package was already installed.

What solved the problem for me was going to Solution > References > System.Data.Entity > Properties > Copy Local, and setting it to True.

Note: Copy Local for Microsoft.SqlServer.Types was already set to true, and even though the problem was with System.Data.Entity, the error message was still about Microsoft.SqlServer.Types.

The solution is from Windows Azure forum.

小开

The solution for me was just adding this line of code to Global.asax.cs in Application_Start():

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

Good luck my brothers.

小开

None of the above solutions worked me.

  • SQL Server Feature pack installed? Yes
  • NuGet package installed? Yes
  • DLL exists in GAC and in the project bin? Yes

You know what, this error can also be due to low resources on the server. I restarted SQL server and it got resolved automatically.

小开

The answer above works fine when version 11 (SQL Server 2012) of the assembly can be used.

I had a problem with this as my solution has other dependencies on version 13 (SQL Server 2016) of the same assembly. In this case note that Entity Framework (at least v6.1.3) is hardcoded in its SqlTypesAssemblyLoader (the source of this exception) to only look for versions 10 and 11 of the assembly.

To work around this I discovered you can tell Entity Framework which assembly you want to use like this:

SqlProviderServices.SqlServerTypesAssemblyName = typeof(SqlGeography).Assembly.FullName;
小开

Just had the same issue. I am using EF6 and calling SQL which has a SQL function that uses spatial commands. I tested this through a unit test and it worked fine. When I went to wire up my Asp.Net solution I received the 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.

By adding the NUGET package "Microsoft.SqlServer.Types" and adding SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin")); to the Application_Start method in Global.asax.cs everything worked fine.

小开

There are 2 ways to fix that:

  1. If you have server access, just Install “Microsoft System CLR Types for SQL Server 2012” it’s from https://www.microsoft.com/en-us/download/details.aspx?id=29065 Or Use Direct Link Below Direct Link to X86 :http://go.microsoft.com/fwlink/?LinkID=239643&clcid=0x409 , Or Direct Link to X64 :http://go.microsoft.com/fwlink/?LinkID=239644&clcid=0x409

  2. Second way is to use NuGet package manager and install

    Install-Package Microsoft.SqlServer.Types

Then follow the plugin notes as below

To deploy an application that uses spatial data types to a machine that does not have 'System CLR Types for SQL Server' installed you also need to deploy the native assembly SqlServerSpatial110.dll. Both x86 (32 bit) and x64 (64 bit) versions of this assembly have been added to your project under the SqlServerTypes\x86 and SqlServerTypes\x64 subdirectories. The native assembly msvcr100.dll is also included in case the C++ runtime is not installed.

You need to add code to load the correct one of these assemblies at runtime (depending on the current architecture).

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);
小开

In my case, a badly composed connection string caused this. Verify if your connection string is properly composed.

小开

For some reason I was missing a binding redirect which fixed this problem for me.

Adding the following fixed my problem

    <dependentAssembly>
<assemblyIdentity name="Microsoft.SqlServer.Types" publicKeyToken="89845dcd8080cc91" culture="neutral" />
<bindingRedirect oldVersion="10.0.0.0-11.0.0.0" newVersion="14.0.0.0" />
</dependentAssembly>
小开

Please add "dependentAssembly" the Web.config file

<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-14.0.0.0" newVersion="14.0.0.0" />
</dependentAssembly>
</assemblyBinding>
</runtime>

This worked for me

小开

Following a comment in an answer for current post, adding these two lines (preferebly to the main function) solved my problem for Console App:

SqlProviderServices.SqlServerTypesAssemblyName = typeof(SqlGeography).Assembly.FullName;
SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);
小开

In my case (a WebForms App) I solved the problem adding the following lines in the Application_Start of the Global.asax file.

SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin"));
System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";

I hope it helps someone.

小开

None of these worked for me because Visual Studio 2017 has a bug. It creates both folder of sql server types outside the project but is not recognized on global.asax

I just drag from folder outside the project to inside of the project than it was regonized in global and worked.


提交答案