This is a migrated thread and some comments may be shown as answers.
Connect SQL Server Compact Database to Report
10 Answers 248 Views
This is a migrated thread and some comments may be shown as answers.
Amy
Top achievements
Rank 1
Amy asked on 29 Aug 2011, 05:01 PM
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

10 Answers, 1 is accepted

Sort by
0
IvanDT
Telerik team
answered on 02 Sep 2011, 10:15 AM
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

Thank you for being the most amazing .NET community! Your unfailing support is what helps us charge forward! We'd appreciate your vote for Telerik in this year's DevProConnections Awards. We are competing in mind-blowing 20 categories and every vote counts! VOTE for Telerik NOW >>

0
Park
Top achievements
Rank 1
answered on 15 Nov 2011, 09:47 AM

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?

0
IvanDT
Telerik team
answered on 18 Nov 2011, 11:04 AM
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

Q2’11 SP1 of Telerik Reporting is available for download (see what's new). Get it today.

0
ajpetersen
Top achievements
Rank 1
answered on 26 Nov 2013, 07:44 PM
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.

0
Nasko
Telerik team
answered on 29 Nov 2013, 02:36 PM
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

New HTML5/JS REPORT VIEWER with MOBILE AND TOUCH SUPPORT available in Telerik Reporting Q3 2013! Get the new Reporting version from your account or download a trial.

0
ajpetersen
Top achievements
Rank 1
answered on 02 Dec 2013, 08:55 PM
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?
0
Nasko
Telerik team
answered on 05 Dec 2013, 05:44 PM
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, ConnectionStringSelectCommand 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

New HTML5/JS REPORT VIEWER with MOBILE AND TOUCH SUPPORT available in Telerik Reporting Q3 2013! Get the new Reporting version from your account or download a trial.

0
ajpetersen
Top achievements
Rank 1
answered on 06 Dec 2013, 09:20 PM
Thank you! That fixed my issue!
0
Ankur
Top achievements
Rank 1
answered on 09 Dec 2013, 11:06 AM
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.
0
Hinata
Top achievements
Rank 1
answered on 11 Dec 2013, 05:11 PM
Hi Ankur

Take a look at http://www.connectionstrings.com/sql-server-compact/. It helped me get my conn string right.
Asked by
Amy
Top achievements
Rank 1
Answers by
IvanDT
Telerik team
Park
Top achievements
Rank 1
ajpetersen
Top achievements
Rank 1
Nasko
Telerik team
Ankur
Top achievements
Rank 1
Hinata
Top achievements
Rank 1
Share this question
or