29

I just upgraded my host to MVC 3 but I'm trying to do a "hello world" using SQLCE 4.0 but I just got:

Sorry, an error occurred while processing your request.

I see my layout and everything but instead of data I see that.

On localhost is working as expected

I have:

<add name="PruebaContext" connectionString="Data Source=|DataDirectory|db.sdf" providerName="System.Data.SqlServerCE.4.0"/>

as connectionString.

I put the sdf as part of the project, I added the System.Data.SqlServerCE.dll to the project with the local copy to true.

I have no more ideas. The connString, the dll is in /bin and is working in localhost.

Any ideas?

Thanks.

EDIT

I have some logs now:

Without the "System.Data.SqlServerCE.dll" on /bin:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

System.ArgumentException: Unable to find the requested .Net Framework Data Provider. It may not be installed.

Fine enough. They don't have SqlServerCE4.

With the Dll on local copy, AKA /bin:

Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8482. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

Exception Details: System.Data.SqlServerCe.SqlCeException: Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8482. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

The KB says that I need the dll from x86 and amd64. I read somewhere that if I copy two directories to /bin, like: /bin/x86 and /bin/amd64 . I copied that folders from the private folder of SqlServerCE 4 installation folder. Now I got:

Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality. This could be due to the presence of multiple instances of SQL Server Compact of different versions or due to wrong binaries with same name as SQL Server Compact binaries. Please install SQL Server Compact binaries of matching version.

Ok. On the root folder of SQLServerCE4 I have some dlls too, so I deleted that two folders and copied that dll to /bin:

Could not load file or assembly 'file:///C:\HostingSpaces\jesusrod\foxandxss.net\wwwroot\mvc3\bin\sqlceca40.dll' or one of its dependencies. The module was expected to contain an assembly manifest.

I think that I tried all things. Scottgu said that SqlServerCE 4 should work on any server without installation.

Community
  • 1
  • 1
Jesus Rodriguez
  • 11,918
  • 9
  • 64
  • 88
  • Look at the event log. The error should be logged there. – Darin Dimitrov Jan 22 '11 at 14:13
  • I don't know if Im looking at the right place, but my host doesnt seems to have a log system. – Jesus Rodriguez Jan 22 '11 at 14:18
  • Scott Gu is wrong, I've had a similar issues in a number of places in a completely non-cloud environment. Just straight up Win2008. :( Hopefully I can figure it out soon. – Adron Oct 15 '11 at 20:47
  • Linking http://stackoverflow.com/questions/10739845/how-to-deploy-sql-server-compact-edition-4-0 in case it is also useful for others – CAD bloke Jun 18 '13 at 01:21

6 Answers6

31

I don't like to respond to my own answer, but after hours of work I have the answer!

We need:

NuGet (Better than copying the dll's from program file)

With NuGet we install:

EFCodeFirst

SqlServerCompact

EFCodeFirst.SqlServerCompact

The problem was that EF need another dll for SQL CE 4 (System.Data.SqlServerCe.Entity.dll) and we need to put some configuration on web.config:

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
    </DbProviderFactories>
  </system.data>

With that, all is working. I have seen like 5 different errors, so we need:

The 2 dll from SQL CE 4, the EF dll, the config in web.config and the native dll (from the package directory where NuGet downloads the library).

It seems that the web.config config is pointing to a concrete version of the SQL CE .dll and the version of the RTM package is different. I can't find the concrete version so I use the .dll from NuGet.

That's all, SQL CE 4 + EF on a remote host.

Jesus Rodriguez
  • 11,918
  • 9
  • 64
  • 88
  • Hi, I had the same issues, and followed the way you solved it, but now I am getting a Security Exception, did you get it too? [I made a question here](http://stackoverflow.com/questions/5086201/getting-security-exception-sqlce-4-shared-hosting) – Francisco Noriega Feb 23 '11 at 02:19
  • This prob worked before, but it didn't for me. What worked was http://stackoverflow.com/questions/4762857/using-sql-server-ce-4-on-a-remote-host-with-mvc-3/5430976#5430976 – CVertex May 12 '11 at 08:34
  • tried this and it worked. but I get a dependency on Entity Framework CodeFirst CTP5 which is not something I want in my project. – Alexandre Brisebois Jan 26 '12 at 20:06
  • 3
    @AlexandreBrisebois This answer is quite old, if you have VS2010 SP1, just go to Project/Add deployable dependencies. That will add the necessary stuff. – Jesus Rodriguez Jan 27 '12 at 14:54
  • Project/Add deployable dependencies only works for a web project – Alexandre Brisebois Jan 27 '12 at 18:27
  • @JesusRodriguez OH MY GOD THIS ANSWER JUST MADE MY DAY – Alexander Corwin Feb 25 '12 at 00:12
  • Thanks :) Referencing System.Data.SqlServerCe.Entity.dll fixed the error – Saber Aug 08 '12 at 18:16
21

Just use menu "Project/Add Deployable Dependencies..." I'm not sure if it is Visual Studio 2010 SP 1 only.

Skorunka František
  • 5,102
  • 7
  • 44
  • 69
4

You need to make sure you have the native SQL CE 4.0 dlls in your private application directory (e.g. \bin) in addition to the managed one.

Copy them from "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\amd64" (or x86 if you have a 32-bit app).

It may also be wise to deploy the MSVCR90.dll and it's manifest too. Have a look at the documentation in C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0 for more information about the required DLLs and redistribution.

WillG
  • 41
  • 1
  • Thank you. I tried with both amd64 or x86 (not at the same time). I got the mismatch error explained on my question. Tried with the managed .dll from Desktop (with the xml), tried with the managed dlls from private and I Got a "Could not load file or assembly 'System.Data.SqlServerCe, Version=4.0.0.0...". I think that I need the right combination of managed, and native. The folder doesn't have installation docs. About MSVCR90.dll I found like 20 dll on my C drive, which one? (No one on any VS directory). Thanks! – Jesus Rodriguez Jan 31 '11 at 14:27
  • 1
    4.0 RTM includes all required files in the Private folder, including the VC++ runtime file and manifest. See this reply also: http://stackoverflow.com/questions/3223359/cant-get-sql-server-compact-3-5-4-to-work-with-asp-net-mvc-2/3223450#3223450 – ErikEJ Jan 31 '11 at 16:52
  • Really good answer ErikEJ but nothing new for me... It can't find the data provider. Using another dll it just cannot load the managed.dll because the manifest is incorrect. Using SqlServerCompact from nuget (native and managed) I get the cannot load the dll v4.0.0.0. And of course tried exactly as your response. CAnnot find the data provider framework. I desist I think :P – Jesus Rodriguez Jan 31 '11 at 21:51
  • I narrowed down the problem. Using SQLServerCE directly works perfect. If I use it with EntityFramework (CodeFirst or creating a .edmx) it is unable to find the requested .Net Framework Data Provider. Same code, same all, just changing direct access for EF (I have EntityFramework.dll on /bin). Some ideas? – Jesus Rodriguez Feb 01 '11 at 22:52
2

This looks like a problem that's caused by having multiple intermediate bits of data left in a project, where you've replaced included files without cleaning up.

I'd recommend doing a Build->Clean all, followed by making sure that all of your references are to the new, correct DLLs, and then making sure that none of the old files or references are still around anywhere in your project. Attempt to rebuild once that is done.

Since this is a hello world project, it might be easier to just start from scratch with a new project, and include the correct dlls in there.

blueberryfields
  • 45,910
  • 28
  • 89
  • 168
1

I was having the same problem. Trying to put a code-first SQL CE 4.0 app into play on my goDaddy account.

I started messing around with various DLL files based upon the thread above and other tutorials, but nothing seemed to work. Something was missing. Then I read about the Add Deployable Dependencies response.

I did that, which created _bin_deployableAssemblies folder. Then I deleted everything from my bin folder except my core project's DLL and copied everything from _bin_deployableAssemblies into the bin folder.

I cleaned out everything in the bin folder on my goDaddy app and resynced the files. That worked. The app loads and if I make codefirst changes to the classes, the dbcontext recreates the database for me. Of course, I don't want that in production, but I wanted to know that everything was working.

I did try using the ASP tutorial about deploying an MVC app to a hosting environment (they use Cytanium hosting as their example), but it didn't work for me on GoDaddy. I had to copy files manually.

Thanks all. This was a huge step for me being able to feel comfortable continuing in MVC and EF.

Mark G
  • 11
  • 1
1

try installing SQL CE manually.

http://www.microsoft.com/download/en/details.aspx?id=17876

Good Luck.

Gabriel Guimarães
  • 2,724
  • 3
  • 27
  • 41