Unable to load the native components of SQL Server

问题:

I've installed SQL Server Compact Edition 4.0 on Win7 x64 and it runs both for Asp.Net and Desktop Applications. This PC also have Visual Studio 2010 SP1 installed. But my Server 2008 R2 produces following error for Asp.Net applications, although it can run Desktop Applications:

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.

I've tried both with a SqlDataSource and SqlCeConnection. Same error. My web.config is like below:

<?xml version="1.0"?>
<configuration>
    <connectionStrings>
       <add name="SQLCE" connectionString="Data Source=|DataDirectory|\a.sdf"
        providerName="System.Data.SqlServerCe.4.0" />
    </connectionStrings>
    <system.web>
        <compilation debug="true" targetFramework="4.0">
            <assemblies>
                <add assembly="System.Data.SqlServerCe, Version=4.0.0.0,
                Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
            </assemblies>
        </compilation>
    </system.web>
</configuration>

Also tried to copy dll's as sugested here but no effect.

回答1:

Finally got SQL Server Compact Edition 4.0 working under IIS 7.5. The problem was permission issue. Current Application Pool's identity IWAM_plesk(default) didn't have access to SQL Server Compact 4.0 folders:

C:\Program Files\Microsoft SQL Server Compact Edition\v4.0

C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0

I just granted Read & Execute and List Folder Contents permissions and now it works like a charm.

回答2:

There are two ways to deploy SQL Server CE 4.0 in ASP.net.

1. Medium trust, or

2. private deployments.

Your configuration file uses version 4.0.0.0 -

<add assembly="System.Data.SqlServerCe, Version=4.0.0.0,
            Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

this requires Medium Trust, and requires the App Pool user having permission to access the SQL Server Compact COM dlls in C:\Program Files\Microsoft SQL Server Compact Edition.

Where medium trust fails, you could attempt private deployment. This is my preference and there's less chance something will go wrong with your system sometime in the future.

If you private deploy the SQL Server CE DLLs, by copying all the files in v4.0\Private to bin\, make sure your Web.config declare a dependency on version 4.0.0.1. This version is not deployed in the GAC and this will ensure your private copy gets loaded.

<system.web>
 <compilation>
  <assemblies>
    <add assembly="System.Data.SqlServerCe, Version=4.0.0.1,
            Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
  </assemblies>
 </compilation>
</system.web>

You will also require a corresponding change in the DbProvider section in Web.config

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

回答3:

Microsoft Knowledge Base here http://support.microsoft.com/kb/974247/en-us says this:

At any given point of time, the correct state of a 64-bit computer, for SQL Server Compact, would be the following: Both x86 and amd64 MSIs are installed. Both x86 and amd64 MSIs are of the same version.

Therefore, if you face any similar issues, make sure that the computer has both x86 and amd64 MSIs of Compact are installed and they are of the same version. In case, they are not, then install Compact SP2 x86 and x64 MSIs from following link as needed: http://www.microsoft.com/downloads/details.aspx?familyid=E497988A-C93A-404C-B161-3A0B323DCE24&displaylang=en

回答4:

I discovered a completely unrelated issue that would cause this error message, so I'm posting a link here for anyone who arrived here from Google. This ate up about three or four hours of last night, and I eventually had to step through the decompiled SqlServerCe binaries, so hopefully this will help you: Certain symbols in application path prevent SQL Server Compact from loading.

回答5:

Does you server have a beta version of SQL CE 4.0 installed? Check Add/Remove Programs for version info.

回答6:

I just upgraded from SQL CE 3.5 to 4.0, by downloading a new version from Microsoft. After doing this, I received the error message which brought me here. For me the solution was to install SQL CE 4.0 for EntityFramework as well, by using the NuGet package manager. So only installing the binaries from the website didn't do the trick for me!

回答7:

I had the same error 'Unable to load the native components of SQL Server Compact'

I have used the Try Catch inbuilt objects and solved the problem. The sdf file what i tried to open had a password.