'Microsoft.SqlServer.Types' version 10 or higher could not be found on Azure

Question

I'm trying to make a webapi in ASP.NET MVC 4. The webapi used Entity Framework 5 Spatial types and i have wrote a very simple code.

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

Area contains DbGeometry.

When i run this local it works, but when i publish it to azure it gives me this 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.

Anyone know how to resolve this ? :)

Thanks!

Are you using Azure Web Sites or a web role in Cloud Services? Also, is your database a SQL Azure database? Have you tried running your local code against the SQL Azure database, and does that work? — Joe Capka, Nov 01 '12 at 10:20

score 138 · Accepted Answer · edited Nov 17 '16 at 15:31

138

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

PM> Install-Package Microsoft.SqlServer.Types

Link for more info

edited Nov 17 '16 at 15:31

Community

1
1

answered Nov 01 '12 at 10:23

Thomas Bolander

3,892
3
22
30

4

Thanks. This just happened to me after I published at 2am. – Lee Smith Nov 03 '12 at 02:04
3

Glad I put up that nuget package up! It always gets me, too. – Pure.Krome May 23 '13 at 22:10
OMG! it adds almost 2MB of binary data to the web app just for using DbGeography (no thanks) it's also heavy on CPU when using it in SQL Server... taking it out. – Yovav Jun 21 '16 at 05:59
13

@Yovav, at least you're running in a floppy disk I don't think that 2 MB of binary data have any influence in the performance of your application. I suggest you to run a benchmark and let us know (with real data) the impact in the CPU. – Diomedes Domínguez Sep 06 '16 at 21:50
In which project do you need to install this? Is the project that has entity framework enough? – Nick N. Apr 10 '17 at 20:52
3

That wasn't enough to solve the issue, I had to do [Chris' answer](http://stackoverflow.com/questions/13174197/microsoft-sqlserver-types-version-10-or-higher-could-not-be-found-on-azure/40166192#40166192) as well. – Shimmy Weitzhandler Apr 28 '17 at 13:45
If you have an MVC app that references a separate library that contains a dbContext -- which project do you add the NuGet package to? – RHarris May 29 '18 at 21:00

score 120 · Answer 2 · edited May 09 '19 at 00:25

120

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;

edited May 09 '19 at 00:25

Community

1
1

answered Oct 20 '16 at 23:40

Chris

1,600
1
12
16

3

Great spot - also applies to machines where only SQL 2014 CLR Types is installed. In our case, we just installed the SQL 2012 CLR Types and it fixed the issue; but if you have a specific dependency on the higher versions of the assemblies then this appears to be the best solution. – Andras Zoltan Oct 25 '16 at 14:09
Where/when should that value be set? – Triynko Dec 27 '16 at 20:33
1

It's a static public property. It should be set at application start-up. E.g. I'm setting it in the Application_Start event in the Global.asax.cs of my web application. – Chris Jan 11 '17 at 05:21
3

+1 This is the only thing that has worked for me. I put it in the constructor of my custom `EntityContext` class (that inherits off `DbContext`). – Chris Feb 24 '17 at 10:49
3

Saved my bacon! – Matt Cashatt Mar 22 '17 at 16:32
This is exactly what I needed. Thank you for this; I've been looking for a couple of hours for a fix! – Dominic Bindley Mar 24 '17 at 02:49
1

At first, I couldn't access the `SqlServerTypesAssemblyName` property to set it, but I realized I only had EF 6.1.0 installed. I updated to 6.1.3 and the property lit up. I was able to set it to the correct version. Runtime exception solved! – andrewcbancroft Mar 27 '17 at 15:47
I believe this was the problem but the solution did not work, so to fix it I downgraded the Microsoft.SqlServer.Types NuGet package to 12.0.5, such that SqlServerSpatial120.dll was in the bin folder of my console app. Not ideal, but I was barely using this library. Interestingly the problem only existed on my production machine, even after I installed CLR types as per: https://stackoverflow.com/questions/43221467/assembly-microsoft-sqlserver-types-version-10-or-higher-could-not-be-found – Patrick Borkowicz May 28 '17 at 21:14
Also I tried the proposed solution earlier and played around with the version, which didn't work, but perhaps I neglected to update the PublicKeyToken to reflect the version 14 package?? – Patrick Borkowicz May 28 '17 at 21:17
Your solution is spot on. One additional note: after reverting to SQL LocalDB 2012 to try to resolve the issue, the auto/default connection string no longer worked with the newer EF. Between the choices of requiring an extra step when deploying with LocalDb (creating a named instance) and this fix, I chose the latter. Redirecting via the config file makes it runtime configurable. – Chaz Jun 14 '17 at 16:41
23

To avoid hard-coding the assembly name you can use `SqlProviderServices.SqlServerTypesAssemblyName = typeof(SqlGeography).Assembly.FullName;` – Samuel Jack Oct 17 '17 at 10:24
I try `SqlServerTypes.Utilities.LoadNativeAssemblies(Server.MapPath("~/bin")); ` and `System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName = typeof(SqlGeography).Assembly.FullName; ` but I still have the problem (Could not load file or assembly 'Microsoft.SqlServer.Types) – forX Nov 23 '17 at 19:20
I need to do the answer of R2D2. edit web.config for binding – forX Nov 23 '17 at 19:27
Excellent tip from Samuel Jack; I used it to overcome 'Microsoft. SqlServer.Types' version 10 or higher could not be found. problem: I have LocalDB 2017 installed. – AAsk Jan 14 '18 at 10:27

score 76 · Answer 3 · answered Jun 29 '17 at 08:27

76

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>

answered Jun 29 '17 at 08:27

Lord Darth Vader

1,895
1
17
26

2

In order to find out what the version number is of the Microsoft.SqlServer.Types on your machine, you can use `AppDomain currentDomain = AppDomain.CurrentDomain; Assembly[] assems = currentDomain.GetAssemblies(); foreach (Assembly assembly in assems) { _logger.Info(assembly.GetName().FullName); }` where _logger is a Nlog logger – Daniël Tulp Aug 31 '17 at 10:08
1

This solved my problem (as types was already installed in my case). If anyone still gets the error after installing SQL Server Types, check this answer. – Can Poyrazoğlu Sep 23 '17 at 04:39
1

@R2D2 Thank you, this fixed it for me as well. – Ogglas Mar 12 '18 at 17:24
1

I installed SQLServerTypes and was still facing an issue. Adding this to the web.config fixed it for me. – saurabhj Apr 14 '18 at 01:39

score 27 · Answer 4 · answered Jan 31 '17 at 13:43

There are 2 ways to fix that:

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

Installing SQL Server or NuGet didn't solve anything, those simple CLR Types solved the problem. This should be the accepted solution. — Can Poyrazoğlu, Mar 23 '17 at 00:03
Link to X64 is worked for me on Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 10586: ) — zapoo, Jun 02 '17 at 15:53
I needed to use the ASP.NET style loader but my development path was `~/` instead of `~/bin`. Be sure to check your pathing as well. — jocull, Dec 04 '17 at 17:58
I was able to install the SQL server pack for the version of SQL i wanted but i absolutely had to make sure the binding redirect was pointed at the version in installed as they are all different for each SQL server version. — Chris Rice, Oct 11 '18 at 17:58

Ertuğrul Üngör · Answer 5 · 2019-12-05T20:00:26.373

19

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

edited Dec 05 '19 at 20:00

answered Aug 16 '18 at 14:09

Ertuğrul Üngör

211
2
6

2

This worked for me. My package stopped working on my local machine out of the blue, but was still working on azure. – ATCraiger Feb 24 '21 at 22:26
Thanks for useful tip. Works fine for my problem – Sedat Kumcu Mar 01 '22 at 11:11

score 13 · Answer 6 · answered Jan 08 '13 at 19:25

13

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.

answered Jan 08 '13 at 19:25

Inusable Lumière

597
4
14

1

This option is not available on nuget packages. – Shimmy Weitzhandler Apr 28 '17 at 13:02
It's because, as mentioned, it's in the reference's properties options, not the nuget's options :) Thank for your workaround ! Worked for me – Emixam23 Sep 18 '18 at 15:35

devKoen1 · Answer 7 · 2016-08-26T13:13:08.920

5

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.

edited Aug 26 '16 at 13:13

answered Aug 26 '16 at 12:15

devKoen1

179
1
12

score 3 · Answer 8 · answered Oct 31 '18 at 16:25

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

score 3 · Answer 9 · answered Sep 12 '19 at 17:36

3

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.

answered Sep 12 '19 at 17:36

Dr TJ

3,241
2
35
51

1

this worked for VS 2019 environment, latest fix. thanks for sharing – coder kemp Apr 08 '20 at 20:23

score 1 · Answer 10 · answered Oct 20 '16 at 11:14

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.

score 0 · Answer 11 · edited Jan 08 '17 at 15:54

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.

Raghu Reddy Muttana · Answer 12 · 2017-03-20T00:44:46.567

0

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

edited Mar 20 '17 at 00:44

answered Mar 18 '17 at 07:11

Raghu Reddy Muttana

81
5

score 0 · Answer 13 · answered May 23 '22 at 14:42

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.

'Microsoft.SqlServer.Types' version 10 or higher could not be found on Azure

13 Answers13

Linked