Connect SQL Server Compact Database to Report

[Amy]

Hi,

I have managed to get all report functionality working with a SQL Express database (in web application), but when I try to connect to a SQL Server Compact database, I get the following error: 

An error has occurred while processing Report 'OutstandingPayments':
Unable to establish a connection to the database. Please, verify that your connection string is valid. In case you use a named connection string from the application configuration file, make sure the name is correct and the connection string settings are present in the configuration file of your application.
------------- InnerException -------------
Unable to find the requested .Net Framework Data Provider. It may not be installed.

The code behind the report designer is this:

this.sqlDataSource1.ConnectionString = "Data Source=\"C:\\Users\\Amecily\\Desktop\\Wits\\DFP v2.1\\Latest\\DFPProductions_Default" +
   "\\DFPProductions_Default\\App_Data\\DFP.sdf\"";
           this.sqlDataSource1.Name = "sqlDataSource1";
           this.sqlDataSource1.ProviderName = "Microsoft.SqlServerCe.Client.4.0";
           this.sqlDataSource1.SelectCommand = resources.GetString("sqlDataSource1.SelectCommand");

Is there something I need to do to the connection string?

Thanks,
Amy

[IvanDT]

Hello Amy,

In order SQL Server Compact 4.0 to work properly you should have installed both the SQL Server Compact 4.0 and the SQL Server Compact 4.0 Visual Studio Tools. The tools are requiring Visual Studio 2010 SP1 to be installed.

All the best,
IvanDT
the Telerik team

[Park]

Hi IvanDT

i installed SQL Server Compact 4.0, SQL Server Compact 4.0 Visual Studio Tools.
but, still error message

Unable to find the requested .Net Framework Data Provider. It may not be installed

-report version 5.1.11.928
-design time good work
app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Northwindsdf"
            connectionString="Data Source=C:\Silverlight\SL4_RIA_Sample\SL4_RIA_Sample.Web\App_Data\Northwind.sdf"
            providerName="Microsoft.SqlServerCe.Client.4.0" />
    </connectionStrings>
</configuration>


Web.config

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <connectionStrings>
    <add name="Northwindsdf"
        connectionString="Data Source=C:\Silverlight\SL4_RIA_Sample\SL4_RIA_Sample.Web\App_Data\Northwind.sdf"
        providerName="Microsoft.SqlServerCe.Client.4.0" />
  </connectionStrings>
  <system.serviceModel>
    <serviceHostingEnvironment aspNetCompatibilityEnabled="true" />
    <services>
      <service
              name="Telerik.Reporting.Service.ReportService"
              behaviorConfiguration="ReportServiceBehavior">
        <endpoint
               address=""
               binding="basicHttpBinding"
               contract="Telerik.Reporting.Service.IReportService">
          <identity>
            <dns value="localhost" />
          </identity>
        </endpoint>
        <endpoint
                address="resources"
                binding="webHttpBinding"
                behaviorConfiguration="WebBehavior"
                contract="Telerik.Reporting.Service.IResourceService"/>
        <endpoint
                address="mex"
                binding="mexHttpBinding"
                contract="IMetadataExchange" />
      </service>
    </services>
    <behaviors>
      <serviceBehaviors>
        <behavior name="ReportServiceBehavior">
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="false" />
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="WebBehavior">
          <webHttp />
        </behavior>
      </endpointBehaviors>
    </behaviors>
  </system.serviceModel>

</configuration>

how can i do?

[IvanDT]

Hello Amy,

the correct provider name should be as shown in the example below:

<add name="sqlServerCompact"
            connectionString="Data Source="C:\Program Files (x86)\Telerik\Reporting Q3 2011\Examples\CSharp\WebDemo\App_Data\Northwind.sdf""
            providerName="System.SqlServerCe.Client.4.0" />

You could observe that when you made a SQL Server Compact connection in a ASP.NET application it will set this particular provider name.

All the best,
IvanDT
the Telerik team

[ajpetersen]

Is the above connection string still valid?

When I use the connection string above, I receive: Unable to find the requested .Net Framework Data Provider. It may not be installed. If I use a connection string with a provider name of System.SqlServerCe.Client.4.0, I get an error saying SqlCeCommand.CommandTimeout does not support non-zero values. This sounds like a data related issue, but I am doing a simple select all query on a table with data in it.

I currently have a report working with a SQL Server Express database and would like to get it working with a SQL Compact 4.0 database.

[Nasko]

Hello Andrew,

As of the current version of Telerik Reporting the connection string:

connectionString="Data Source="C:\Program Files (x86)\Telerik\Reporting Q3 2011\Examples\CSharp\WebDemo\App_Data\Northwind.sdf""

is not valid, since there is no App_Data\Northwind.sdf in the examples.

About the "SqlCeCommand.CommandTimeout does not support non-zero values" on a 64-bit computer, you must run packages that connect to SQL Server Compact data sources in 32-bit mode. The SQL Server Compact provider that Integration Services uses to connect to SQL Server Compact data sources is available only in a 32-bit version. Please, check this http://msdn.microsoft.com/en-us/library/ms141253.aspx and see if the problem still persists.

Regards,
Nasko
Telerik

[ajpetersen]

Nasko,

Okay, I see that some connection manager is needed to communicate with the sql compact database, but I don't see how to implement the connection to the database. 

I tried to install sql compact 32 bit, but quickly found out that the 32 bit can't be installed on a 64 bit machine. Can you help point me in the right direction?

[Nasko]

Hello Andrew,

It appears that the SQL CE 4.0 generic provider has a check whether CommandTimeout is always 0, and if not, throws the exception you posted. To workaround this problem try to manually set the CommandTimeout, ConnectionString, SelectCommand and ProviderName from the property grid of the SqlDataSource component i.e.:

CommandTimeout: 0
ConnectionString: <your_connection>
SelectCommand: <your_select>
ProviderName: System.Data.SqlServerCe.4.0

Regards,
Nasko
Telerik

[ajpetersen]

Thank you! That fixed my issue!

[Ankur]

Hi I am having similar problem.
I have a sql server compact edition 4.0 as data source and i need an example of the connection string.
My platform is windows 7 (x64) and visual studio 2010 sp1.

my path for the sql server database is:
"C:\Users\asharma\AppData\Abc\TesterDatabase.sdf"

I tried the following in the connection dialog box.
Data Source="C:\Users\asharma\AppData\Abc\TesterDatabase.sdf" (see attachment: telerik reporting database connection string)
but after clicking next when i try to build open query builder then i get error (see attachment: telerik reporting database error).
 
what are the things i need to add more in the connection string dialog box?
I would be nice if you could provide a proper example of the connection string as i am new to the database and reporting.

Thank you.

[Hinata]

Hi Ankur

Take a look at http://www.connectionstrings.com/sql-server-compact/. It helped me get my conn string right.