SQL Server Compact
I am trying to use a SQL Server Compact DB for some simple content stuff on my site, I have used Nuget to get all the Efcodefirst.SqlServerCompact packages, added my bin directory to git and uploaded everything but I am still gettting the 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.
This is also in my 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>
As well as
<add name="webContext"
connectionString="Data Source=|DataDirectory|\myWeb.Models.webContext.sdf"
providerName="System.Data.SqlServerCe.4.0" />
I read that article, multiple stack overflow articles, and am still getting it. Is there something else I may be missing?
Discussions are closed to public comments.
If you need help with AppHarbor please
start a new discussion.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
Support Staff 1 Posted by friism on 09 May, 2011 06:07 PM
Using instance-filesystems for persistence is not recommended on AppHarbor. The files are zapped on deploy and are not synchronized if your app runs on multiple instances.
Having said that, unless you've enabled the
Allow write-access to file system, you should stash the sdf file in theApp_Datafolder of your website. This is the only folder writeable by the application.Are you pushing all the SQL Server CE dll's along with your code? Please see this Stackoverflow question. Also check out the AppHarbor sample project that uses SQL Server CE for unit testing.
Let us know if you can't get this working (also, please update this post if you resolve the issue, so others can benefit).
2 Posted by kevin.moore on 27 Jul, 2011 03:11 AM
We are encountering the same issue here, all dll referenced using Nuget and also deployed the dll using the _bin_deployableAssemblies folder, and there nothing we can do to make this work. Is there anything you can do with KB 974247?
3 Posted by kevin.moore on 27 Jul, 2011 03:27 AM
I've made my way through the AppHarbor sample project you are referring to, but it uses SqlServerCe differently, only opening a connection in code to a SDF file, whereas our issue occurs on AppHarbor servers when we use SqlServerCE with System.Web.Providers, as the database for the user and role database. The application fails while reading the application configuration file when it tries to instantiate the SqlCeProviderFactory.
However, such error does not occurs on our machines. Is it possible for you to look at the KB or else simply install the binaries for SqlServerCE http://www.microsoft.com/download/en/details.aspx?id=17876 on your servers, which I do not know if its feasible, but would be great, sometime we just need a very small store (which we know will get erased on each deployement)
Hope this helps
Support Staff 4 Posted by friism on 27 Jul, 2011 08:23 PM
Kevin, thanks for your detailed description. We are wary of installing additional components on appharbor application servers. One of the reasons is to not crud up the GAC since this can cause GAC-dll-overriding problems for people that want to use non-standard versions of software components.
Your problem seems to be caused by the fact that the AppHarbor servers that build your code has some version of SQL Server CE installed (due to some other long forgotten expediency). When the code is compiled, it is linked to the GAC-installed version. The code is then deployed to a different server without SQL Server CE installed, and the version you bin-deploy with your code is incompatible with the one linked to during build. This causes the error.
As a temporary measure, you could try to bin-deploy 32bit versions of SQL Server CE 3.5.0 or 3.5.1 (which is what is currently found on our build servers). We're trying to come up with a general solution (which might end up be, making sure that build and application servers are configured the same) and will keep you posted.
Michael
5 Posted by kevin.moore on 29 Jul, 2011 01:36 PM
Hi Michael,
Thanks for the feedback, however, we are using the new System.Web.Providers and it works only with Sql Server CE 4, which means that we wont be able to deploy to appharbor until this is fixed.
Let me know when you've remove the 3.5 version from your build server
Thanks
Support Staff 6 Posted by friism on 31 Jul, 2011 05:27 AM
OK, this can be made to work, I'm attaching a rough solution demonstrating how.
Here are the approximate steps I took:
1) add SqlServerCE and System.Web.Providers using nuget
2) Make SqlServerCE a deployable dependency (this causes the native dll's to be placed in a
_bin_deployableAssembliesfolder. Normally these are copied to the output dir by msbuild, but not on AppHarbor for various reasons)3) Add the following to your project file to cause the native dll's to be copied to the build output (replace
WebProvidersTestwith your project name):Note that I also upgraded the SqlServerCE.dll (and the entity one) to 4.0.0.1 (found in the
privatefolder of the SqlServerCE installation dir). This is useful to avoid getting overwritten by GAC-installed 4.0.0.0 version, but I don't actually think it's necessary here, because Sql Server CE is not installed on AppHarbor application servers.You may also need the assemblybinding-stuff at the bottom of the web.config.
Let us know if you have other questions.
Michael
friism closed this discussion on 31 Jul, 2011 05:27 AM.
kevin.moore re-opened this discussion on 01 Aug, 2011 02:13 PM
7 Posted by kevin.moore on 01 Aug, 2011 02:13 PM
Hi Michael,
I've tried all the step in you last post but now there is still an error
while loading the application*
*Server Error in '/' Application.
------------------------------
*Failed to find or load the registered .Net Framework Data Provider.*
*Description:
*An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error
and where it originated in the code.
* Exception Details: *System.Configuration.ConfigurationErrorsException:
Failed to find or load the registered .Net Framework Data Provider.
*Source Error:*
An unhandled exception was generated during the execution of the current
web request. Information regarding the origin and location of the exception
can be identified using the exception stack trace below.
*Stack Trace:*
[ConfigurationErrorsException: Failed to find or load the registered
.Net Framework Data Provider.]
System.Data.Common.DbProviderFactories.GetFactory(DataRow
providerRow) +1415042
System.Data.Common.DbProviderFactories.GetFactory(String
providerInvariantName) +88
System.Web.Providers.Entities.ModelHelper.CreateEntityConnection(ConnectionStringSettings
setting, String csdl, String ssdl, String msl) +295
System.Web.Providers.Entities.ModelHelper.CreateSessionEntities(ConnectionStringSettings
setting) +82
System.Web.Providers.DefaultSessionStateProvider.PurgeExpiredSessions() +95
System.Web.Providers.DefaultSessionStateProvider.PurgeIfNeeded() +30
System.Web.Providers.DefaultSessionStateProvider.InitializeRequest(HttpContext
context) +35
System.Web.SessionState.SessionStateModule.BeginAcquireState(Object
source, EventArgs e, AsyncCallback cb, Object extraData) +258
System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+96
System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +184
From what I can see on the build server, the DLL are copied :
*
CopyNatives:
if not exist
"D:\temp\qhvoudwh.2e4\output\_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\x86
" md
"D:\temp\qhvoudwh.2e4\output\_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\x86"
xcopy /s /y
"D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\*.*"
"D:\temp\qhvoudwh.2e4\output\_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\x86"
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlcecompact40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlceer40EN.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlceme40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlceqp40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlcese40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\Microsoft.VC90.CRT\msvcr90.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\Microsoft.VC90.CRT\README_ENU.txt
8 File(s) copied
if not exist
"D:\temp\qhvoudwh.2e4\output\_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\amd64"
md
"D:\temp\qhvoudwh.2e4\output\_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\amd64"
xcopy /s /y
"D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\*.*"
"D:\temp\qhvoudwh.2e4\output\_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\amd64
"
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlcecompact40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlceer40EN.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlceme40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlceqp40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlcese40.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\Microsoft.VC90.CRT\msvcr90.dll
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\Microsoft.VC90.CRT\README_ENU.txt
8 File(s) copied*
SqlServerCE has been added as a deployable dependency
(_bin_deployableAssemblies) and I've also added the binding redirect stuff.
It's hard to tell what the error really is, as I don't have access to the
server's event log. Is there something I'm missing here?
Thanks,
Kevin
D:\temp\qhvoudwh.2e4\input\Vooban.Beez.Gaulois.Website\Vooban.Beez.Gaulois.Website.csproj(279,5):
warning : Natives copied
On Sun, Jul 31, 2011 at 1:26 AM, Michael Friis <
[email blocked]> wrote:
Support Staff 8 Posted by friism on 01 Aug, 2011 08:53 PM
For the assembly-binding, are you sure you're using 4.0.0.0 and not 4.0.0.1 (that is, if you use the straight-up nuget dll's). If that's not it, you might want to try with the 4.0.0.1 dlls found in the "private" folder of you SqlServerCE installation.
Michael
9 Posted by kevin.moore on 01 Aug, 2011 11:59 PM
Yes I'm using version 4.0. It is working on my machine but still not
on AppHarbor servers...
Sent from my iPad
On 2011-08-01, at 16:53, Michael Friis
<[email blocked]>
wrote:
>
Support Staff 10 Posted by friism on 02 Aug, 2011 12:03 AM
And you specify 4.0.0.0 in the assembly-binding directive? (I'm asking because the directive in my sample says 4.0.0.1)
11 Posted by kevin.moore on 02 Aug, 2011 12:47 AM
Yes 4.0.0.0, and I can also send you my project as a zip file if you
want? Or even give you an access to our bitbucket repository?
Sent from my iPad
On 2011-08-01, at 20:04, "Michael Friis"
<[email blocked]>
wrote:
>
Support Staff 12 Posted by friism on 02 Aug, 2011 01:08 AM
I'll take a look.
13 Posted by kevin.moore on 02 Aug, 2011 11:53 PM
can I have your bitbucket user id?
If you use the apphb one, the application on bitbucket is
Vooban.Beez.Gaulois
Kevin
Support Staff 14 Posted by friism on 02 Aug, 2011 11:56 PM
Yeah, using apphb -- will take a look.
Michael
Support Staff 15 Posted by friism on 03 Aug, 2011 03:17 AM
I'm attaching a working copy of your code (since deleted for security reasons). I think the problem was due to bad references to the sqlserverce and sqlserverce.entity dlls. I've included the git versioning info in the archive, so that you can see what it took to get it running. Not all steps may be necessary.
Let me know if you have other questions.
Michael
friism closed this discussion on 03 Aug, 2011 03:17 AM.