tag:support.appharbor.com,2010-11-23:/discussions/problems/519-sql-server-compactAppHarbor: Discussion 2011-12-28T11:01:36Ztag:support.appharbor.com,2010-11-23:Comment/71101522011-05-09T17:15:32Z2011-05-09T17:47:37ZSQL Server Compact<div><p>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.</p>
<p>This is also in my web.config</p>
<pre>
<code><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></code>
</pre>
<p>As well as</p>
<pre>
<code><add name="webContext"
connectionString="Data Source=|DataDirectory|\myWeb.Models.webContext.sdf"
providerName="System.Data.SqlServerCe.4.0" /></code>
</pre>
<p>I read that article, multiple stack overflow articles, and am
still getting it. Is there something else I may be missing?</p></div>matrixhasyou2k4tag:support.appharbor.com,2010-11-23:Comment/71101522011-05-09T18:07:40Z2011-05-09T18:07:40ZSQL Server Compact<div><p>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.</p>
<p>Having said that, unless you've enabled the <code>Allow
write-access to file system</code>, you should stash the sdf file
in the <code>App_Data</code> folder of your website. This is the
only folder writeable by the application.</p>
<p>Are you pushing all the SQL Server CE dll's along with your
code? Please see <a href=
"http://stackoverflow.com/questions/4762857/using-sql-server-ce-4-on-a-remote-host-with-mvc-3">
this Stackoverflow question</a>. Also check out the <a href=
"https://github.com/appharbor/AppHarbor-SQLServerCEUnitTesting-Sample">
AppHarbor sample project</a> that uses SQL Server CE for unit
testing.</p>
<p>Let us know if you can't get this working (also, please update
this post if you resolve the issue, so others can benefit).</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-07-27T03:11:56Z2011-09-29T18:02:40ZSQL Server Compact<div><p>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?</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-07-27T03:27:08Z2011-09-29T18:02:40ZSQL Server Compact<div><p>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.</p>
<p>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 <a href=
"http://www.microsoft.com/download/en/details.aspx?id=17876">http://www.microsoft.com/download/en/details.aspx?id=17876</a>
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)</p>
<p>Hope this helps</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-07-27T20:23:12Z2011-07-27T20:23:12ZSQL Server Compact<div><p>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.</p>
<p>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.</p>
<p>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.</p>
<p>Michael</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-07-29T13:36:14Z2011-09-29T18:02:41ZSQL Server Compact<div><p>Hi Michael,</p>
<p>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.</p>
<p>Let me know when you've remove the 3.5 version from your build
server</p>
<p>Thanks</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-07-31T05:27:28Z2011-07-31T05:27:28ZSQL Server Compact<div><p>OK, this can be made to work, I'm attaching a rough solution
demonstrating how.</p>
<p>Here are the approximate steps I took:<br>
1) add SqlServerCE and System.Web.Providers using nuget<br>
2) Make SqlServerCE a deployable dependency (this causes the native
dll's to be placed in a <code>_bin_deployableAssemblies</code>
folder. Normally these are copied to the output dir by msbuild, but
not on AppHarbor for various reasons)<br>
3) Add the following to your project file to cause the native dll's
to be copied to the build output (replace
<code>WebProvidersTest</code> with your project name):</p>
<pre>
<code><Target Name="AfterBuild">
<CallTarget Targets="CopyNatives" />
</Target>
<Target Name="CopyNatives">
<Exec Command="if not exist &quot;$(TargetDir)_PublishedWebsites\WebProvidersTest\bin\x86 &quot; md &quot;$(TargetDir)_PublishedWebsites\WebProvidersTest\bin\x86&quot;" />
<Exec Command="xcopy /s /y &quot;$(SolutionDir)packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\*.*&quot; &quot;$(TargetDir)_PublishedWebsites\WebProvidersTest\bin\x86&quot;" />
<Exec Command="if not exist &quot;$(TargetDir)_PublishedWebsites\WebProvidersTest\bin\amd64&quot; md &quot;$(TargetDir)_PublishedWebsites\WebProvidersTest\bin\amd64&quot;" />
<Exec Command="xcopy /s /y &quot;$(SolutionDir)packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\*.*&quot; &quot;$(TargetDir)_PublishedWebsites\WebProvidersTest\bin\amd64 &quot;" />
<Warning Text="Natives copied" />
</Target></code>
</pre>
<p>Note that I also upgraded the SqlServerCE.dll (and the entity
one) to 4.0.0.1 (found in the <code>private</code> folder 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.</p>
<p>You may also need the assemblybinding-stuff at the bottom of the
web.config.</p>
<p>Let us know if you have other questions.<br>
Michael</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-08-01T14:13:53Z2011-09-29T18:02:41ZSQL Server Compact<div><p>Hi Michael,</p>
<p>I've tried all the step in you last post but now there is still
an error<br>
while loading the application*</p>
<h2>*Server Error in '/' Application.</h2>
<p><em>Failed to find or load the registered .Net Framework Data
Provider.</em> <em>Description:</em> An unhandled exception
occurred during the execution of the current web request. Please
review the stack trace for more information about the error<br>
and where it originated in the code.</p>
<ul>
<li>Exception Details:
*System.Configuration.ConfigurationErrorsException: Failed to find
or load the registered .Net Framework Data Provider.</li>
</ul>
<p><em>Source Error:</em></p>
<p>An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location
of the exception<br>
can be identified using the exception stack trace below.<br>
<em>Stack Trace:</em></p>
<p>[ConfigurationErrorsException: Failed to find or load the
registered .Net Framework Data Provider.]
System.Data.Common.DbProviderFactories.GetFactory(DataRow
providerRow) +1415042<br>
System.Data.Common.DbProviderFactories.GetFactory(String
providerInvariantName) +88<br>
System.Web.Providers.Entities.ModelHelper.CreateEntityConnection(ConnectionStringSettings
setting, String csdl, String ssdl, String msl) +295<br>
System.Web.Providers.Entities.ModelHelper.CreateSessionEntities(ConnectionStringSettings
setting) +82<br>
System.Web.Providers.DefaultSessionStateProvider.PurgeExpiredSessions()
+95
System.Web.Providers.DefaultSessionStateProvider.PurgeIfNeeded()
+30
System.Web.Providers.DefaultSessionStateProvider.InitializeRequest(HttpContext
context) +35<br>
System.Web.SessionState.SessionStateModule.BeginAcquireState(Object
source, EventArgs e, AsyncCallback cb, Object extraData) +258<br>
System.Web.AsyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
+96 System.Web.HttpApplication.ExecuteStep(IExecutionStep step,
Boolean& completedSynchronously) +184</p>
<p>From what I can see on the build server, the DLL are copied
:<br>
* CopyNatives:<br>
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"</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlcecompact40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlceer40EN.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlceme40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlceqp40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\sqlcese40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\Microsoft.VC90.CRT\msvcr90.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\x86\Microsoft.VC90.CRT\README_ENU.txt<br>
8 File(s) copied if not exist
"D:\temp\qhvoudwh.2e4\output_PublishedWebsites\Vooban.Beez.Gaulois.Website\bin\amd64"
md<br>
"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
"</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlcecompact40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlceer40EN.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlceme40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlceqp40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\sqlcese40.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\Microsoft.VC90.CRT\Microsoft.VC90.CRT.manifest</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\Microsoft.VC90.CRT\msvcr90.dll</p>
<p>
D:\temp\qhvoudwh.2e4\input\packages\SqlServerCompact.4.0.8482.1\NativeBinaries\amd64\Microsoft.VC90.CRT\README_ENU.txt<br>
8 File(s) copied*</p>
<p>SqlServerCE has been added as a deployable dependency<br>
(_bin_deployableAssemblies) and I've also added the binding
redirect stuff.</p>
<p>It's hard to tell what the error really is, as I don't have
access to the<br>
server's event log. Is there something I'm missing here?</p>
<p>Thanks,<br>
Kevin</p>
<p>
D:\temp\qhvoudwh.2e4\input\Vooban.Beez.Gaulois.Website\Vooban.Beez.Gaulois.Website.csproj(279,5):<br>
warning : Natives copied</p>
<p>On Sun, Jul 31, 2011 at 1:26 AM, Michael Friis <<br>
<a href=
"mailto:tender+dd37555c1704aaa6a6ad64ece39e3b563e1d6e2fe@tenderapp.com">
tender+dd37555c1704aaa6a6ad64ece39e3b563e1d6e2fe@tenderapp.com</a>>
wrote:</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-08-01T20:53:18Z2011-08-01T20:53:18ZSQL Server Compact<div><p>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.</p>
<p>Michael</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-08-01T23:59:36Z2011-09-29T18:02:42ZSQL Server Compact<div><p>Yes I'm using version 4.0. It is working on my machine but still
not<br>
on AppHarbor servers...</p>
<p>Sent from my iPad</p>
<p>On 2011-08-01, at 16:53, Michael Friis<br>
<a>tender+dd37555c1704aaa6a6ad64ece39e3b563e1d6e2fe@tenderapp.com</a><br>
wrote:</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-08-02T00:03:37Z2011-08-02T00:03:37ZSQL Server Compact<div><p>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)</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-08-02T00:47:28Z2011-09-29T18:02:42ZSQL Server Compact<div><p>Yes 4.0.0.0, and I can also send you my project as a zip file if
you<br>
want? Or even give you an access to our bitbucket repository?</p>
<p>Sent from my iPad</p>
<p>On 2011-08-01, at 20:04, "Michael Friis"<br>
<a href=
"mailto:tender+dd37555c1704aaa6a6ad64ece39e3b563e1d6e2fe@tenderapp.com">
tender+dd37555c1704aaa6a6ad64ece39e3b563e1d6e2fe@tenderapp.com</a><br>
wrote:</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-08-02T01:08:43Z2011-08-02T01:08:43ZSQL Server Compact<div><p>I'll take a look.</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-08-02T23:53:27Z2011-09-29T18:02:42ZSQL Server Compact<div><p>can I have your bitbucket user id?</p>
<p>If you use the apphb one, the application on bitbucket is<br>
Vooban.Beez.Gaulois</p>
<p>Kevin</p></div>kevin.mooretag:support.appharbor.com,2010-11-23:Comment/71101522011-08-02T23:56:15Z2011-08-02T23:56:15ZSQL Server Compact<div><p>Yeah, using apphb -- will take a look.</p>
<p>Michael</p></div>friismtag:support.appharbor.com,2010-11-23:Comment/71101522011-08-03T03:17:26Z2011-08-11T23:55:17ZSQL Server Compact<div><p>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.</p>
<p>Let me know if you have other questions.<br>
Michael</p></div>friism