Exception setting SetSqlServerCompatibilityLevel

Posts   
 
    
Angus
User
Posts: 44
Joined: 08-Jul-2005
# Posted on: 11-Jul-2008 00:26:14   

Hi All,

Here is my setup

• LLBLGen Pro Version = 2.6 Final ( June 6th 2008 ) • RunTime File version = 2.6.08.0624 • Adapter Templates .NET 2.0 • SQL Server 2005 and • SQL CE 3.1 (9.0.242.0)

We have just updated from 2.5 to 2.6 and some of our code is not working correctly with respect to SQL CE Desktop. We are accessing two databases, one SQL Server 2005 and a local cache db that is using CE Desktop 3.1. With LLBLGEN 2.5 the CE 3.1 adapter worked fine.
Now that we have updated, we have changed the code in our AdapterFactory class that creates the DataAccessAdapters to call “SetSqlServerCompatibilityLevel”. Previously we set this in the app.config file to be specific to SQL 2005 as the CE adapter was created in it's own SD.XXX.dll

Here is the code that is creating the two different adapters.

Methods in AdapterFactory



      public static IDataAccessAdapter CreateLocalAdapter(bool keepConnectionOpen)
      {   COMPANY_NS.LocalDB.DAL.DatabaseSpecific.DataAccessAdapter.SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel.SqlServerCE3x);
         string conString = FactoryHelpers.ConnectionStrings.Local_ConnectionString;
         return new COMPANY_NS.LocalDB.DAL.DatabaseSpecific.DataAccessAdapter(conString, keepConnectionOpen);    //}
      }


      private static IDataAccessAdapter GetServerAdapter(bool keepConnectionOpen)
      {
         COMPANY_NS.ServerDB.DAL.DatabaseSpecific.DataAccessAdapter.SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2005);
        
         string connString;
         connString = FactoryHelpers.ConnectionStrings.Server_ConnectionString;     
         return new COMPANY_NS.ServerDB.DAL.DatabaseSpecific.DataAccessAdapter(connString, keepConnectionOpen);
      }



Upon Calling the this line


COMPANY_NS.LocalDB.DAL.DatabaseSpecific.DataAccessAdapter.SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel.SqlServerCE3x);

Some of the developers are getting this exception


System.TypeInitializationException: The type initializer for 'SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine' threw an exception. ---> SD.LLBLGen.Pro.ORMSupportClasses.ORMGeneralOperationException: There's no ADO.NET provider defined / found for the compatibility level 'SqlServerCE3x'. Please make sure either the machine.config or this application's .config file contains a definition of the provider.
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.SetFactoryToUse()
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.set_CompatibilityLevel(SqlServerCompatibilityLevel value)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine..cctor()
   --- End of inner exception stack trace ---

even though they were able to use the LLBLGen 2.5 adapter to access CE 3.1

I'm sure it is some setting that is different between our machines but we are wondering if anyone has seen this and what the resolutions would be?

Thanks for the help.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 11-Jul-2008 07:55:21   

looking into this (what is new section):

.NET 2.0+: SqlServer CE Desktop is now easier to use. One can generate normal SqlServer code when SqlServer CE Desktop (CE 3.1 or higher) is used, as the DQE and templates work with the DbDataProviderFactory and have two new compatibility modes: SqlServerCE3x and SqlServerCE3.5. When deploying a CE Desktop application, be sure to add the DbDataProvider factory registration to your application's .config file. See CE Desktop's documentation for details about that. It's now possible to simply switch between CE Desktop and SqlServer server due to the change of a connection string and the compatibility mode setting (of course the schemas have to match).

It might be that some developers are missing this at their machine.config:


<system.data>
     <DbProviderFactories>
          <add name="SQL Server Everywhere Edition Data Provider" 
               invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Everywhere Edition"  
               type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, 
               Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
     </DbProviderFactories>
</system.data>
David Elizondo | LLBLGen Support Team
Angus
User
Posts: 44
Joined: 08-Jul-2005
# Posted on: 11-Jul-2008 18:10:58   

David,

Thank you very much for your quick reply. I should have looked further into the docs.

We did veryify that the developer(s) in question did not have the correct settings in their machine.config files. This raised another question though. With SQL CE we should be able to do an xcopy / ClickOnce install of the application without having to touch the users machine.config file, in fact we chose SQL CE for that very reason as we did not want to require a complicated install. So we figured that we would put the necessary code into the app.config file.

The following is what we added to the app.config file.


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

This fixed the issue with the developers who did not have the correct machine.config setup, but caused new exceptions for the developers who did have the correct machine.config file setup.

Here is the new exception

{"An error occurred creating the configuration section handler for system.data: Column 'InvariantName' is constrained to be unique. Value 'System.Data.SqlClient' is already present.

So basically to fix this we updated the app.config file to this...


<system.data> 
 <DbProviderFactories> 
   THIS WAS ADDED ---> <remove invariant="System.Data.SqlServerCe" /> 
   <add name="SQL Server Everywhere Edition Data Provider" invariant="System.Data.SqlServerCe" description=".NET Framework Data Provider for Microsoft SQL Server Everywhere Edition" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> 
 </DbProviderFactories> 
</system.data>

Notice the line to remove the invariant "System.Data.SqlServerCe" before adding it back in. This works but I'm not sure if this is actually the best way to do this? Does this sound right to you?

Thanks for you input.

Chris

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 11-Jul-2008 20:47:24   

It's a good solution if you cannot modify machine.config wink I would do that too

David Elizondo | LLBLGen Support Team