SQL Server Compact

matrixhasyou2k4's Avatar

matrixhasyou2k4

09 May, 2011 05:15 PM

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?

  1. Support Staff 2 Posted by friism on 09 May, 2011 06:07 PM

    friism's Avatar

    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 the App_Data folder 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. 3 Posted by kevin.moore on 27 Jul, 2011 03:11 AM

    kevin.moore's Avatar

    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. 4 Posted by kevin.moore on 27 Jul, 2011 03:27 AM

    kevin.moore's Avatar

    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

  4. Support Staff 5 Posted by friism on 27 Jul, 2011 08:23 PM

    friism's Avatar

    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. 6 Posted by kevin.moore on 29 Jul, 2011 01:36 PM

    kevin.moore's Avatar

    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

  6. Support Staff 7 Posted by friism on 31 Jul, 2011 05:27 AM

    friism's Avatar

    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_deployableAssemblies folder. 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 WebProvidersTest with your project name):

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

    Note that I also upgraded the SqlServerCE.dll (and the entity one) to 4.0.0.1 (found in the private 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.

    You may also need the assemblybinding-stuff at the bottom of the web.config.

    Let us know if you have other questions.
    Michael

  7. friism closed this discussion on 31 Jul, 2011 05:27 AM.

  8. kevin.moore re-opened this discussion on 01 Aug, 2011 02:13 PM

  9. 8 Posted by kevin.moore on 01 Aug, 2011 02:13 PM

    kevin.moore's Avatar

    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:

  10. Support Staff 9 Posted by friism on 01 Aug, 2011 08:53 PM

    friism's Avatar

    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

  11. 10 Posted by kevin.moore on 01 Aug, 2011 11:59 PM

    kevin.moore's Avatar

    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:

    >

  12. Support Staff 11 Posted by friism on 02 Aug, 2011 12:03 AM

    friism's Avatar

    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)

  13. 12 Posted by kevin.moore on 02 Aug, 2011 12:47 AM

    kevin.moore's Avatar

    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:

    >

  14. Support Staff 13 Posted by friism on 02 Aug, 2011 01:08 AM

    friism's Avatar

    I'll take a look.

  15. 14 Posted by kevin.moore on 02 Aug, 2011 11:53 PM

    kevin.moore's Avatar

    can I have your bitbucket user id?

    If you use the apphb one, the application on bitbucket is
    Vooban.Beez.Gaulois

    Kevin

  16. Support Staff 15 Posted by friism on 02 Aug, 2011 11:56 PM

    friism's Avatar

    Yeah, using apphb -- will take a look.

    Michael

  17. Support Staff 16 Posted by friism on 03 Aug, 2011 03:17 AM

    friism's Avatar

    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

  18. friism closed this discussion on 03 Aug, 2011 03:17 AM.

Comments are currently closed for this discussion. You can start a new one.

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