Change the Crystal Reports Set Datasource Location for Ripplestone

One of the more common questions I get from Ripplestone users is how to change where the Crystal Report is getting the data from for the report.  Or a related question is that after a report is published to Ripplestone it gets an error stating that the report can no longer find the data source.  This is very common when the data source is a file based data source such as Microsoft Access, Excel or a dBase database.

First, let me step back an explain why Ripplestone will display the error when the report is published to Ripplestone.  Then we can fix the report to make it work within Ripplestone.

When creating a report that uses a file base database or a file like Excel, the report will use a hard coded path to the file.  In the screen shot below, the report is getting the Access database from a network share.  This report will work great as long as its run from the local network and the user running the report has permissions to the network share.  This is the location used to develop the report.

The problem is that when this report is published to Ripplestone, the Ripplestone server will also need to be able to access the database to refresh the report with current data.  The Ripplestone web application also runs under the Network Service local account and that account will need permission granted to access the database.

In the following example, the report was published to a external web server that did not have access to the network share, so it would display an error within Ripplestone stating that the database could not be found.

To solve the problem the database location needed to be changed within the Crystal Report using the Set Database Location dialog box.  The dialog box allows the report to be updated to a different database at a different location.  In the above example the database was moved to the report server at D:DataTMSApplications2009.mdb and the TMS folder was given permission to the Network Service user to read from the folder.

As you can see in the about screen shot I create a new database connection in the bottom half with the new database location and then pressed the Update button to update the main report and the sub-reports with the new database.  I was then able to publish the updated Crystal Report to Ripplestone and the report will be able to location and report from the database in the D:DataTMS folder.

Change the Crystal Reports Set Datasource Location for Ripplestone

One of the more common questions I get from Ripplestone users is how to change where the Crystal Report is getting the data from for the report.  Or a related question is that after a report is published to Ripplestone it gets an error stating that the report can no longer find the data source.  This is very common when the data source is a file based data source such as Microsoft Access, Excel or a dBase database.

First, let me step back an explain why Ripplestone will display the error when the report is published to Ripplestone.  Then we can fix the report to make it work within Ripplestone.

When creating a report that uses a file base database or a file like Excel, the report will use a hard coded path to the file.  In the screen shot below, the report is getting the Access database from a network share.  This report will work great as long as it’s run from the local network and the user running the report has permissions to the network share.  This is the location used to develop the report.

The problem is that when this report is published to Ripplestone, the Ripplestone server will also need to be able to access the database to refresh the report with current data.  The Ripplestone web application also runs under the Network Service local account and that account will need permission granted to access the database.

In the following example, the report was published to a external web server that did not have access to the network share, so it would display an error within Ripplestone stating that the database could not be found.

To solve the problem the database location needed to be changed within the Crystal Report using the Set Database Location dialog box.  The dialog box allows the report to be updated to a different database at a different location.  In the above example the database was moved to the report server at D:\Data\TMS\Applications2009.mdb and the TMS folder was given permission to the Network Service user to read from the folder.

As you can see in the about screen shot I create a new database connection in the bottom half with the new database location and then pressed the Update button to update the main report and the sub-reports with the new database.  I was then able to publish the updated Crystal Report to Ripplestone and the report will be able to location and report from the database in the D:\Data\TMS folder.

Wrapping up a successful and fun trip to Denver. Good to see everyone.

SQL Server Case Sensitivity

I just completed the long task of making the Ripplestone application work with SQL Server that has the case sensitive feature turned on.  It was not a tough task, but it did take looking at each chuck of SQL code either in the code or in stored procedures and making sure that everything matched the table and field names with the proper case.  It’s odd that this had not been discovered in the 5 year life of Ripplestone, but there is always something that needs to be done in a large application.

At least this will be one less thing to think about going forward.

Running Ripplestone on Vista

I was working with a Ripplestone client to get the software installed on a Vista PC and had a do a few things different than if this was XP Pro or Windows Server 2003.  The main differences were that Vista uses IIS 7 and the .NET framework 1.1 is not installed by default.

I started with the following article on setting up Vista and Windows 2008 to run an ASP.NET 1.1 application.

Install ASP.NET 1.1 with IIS7

Once I got ASP.NET 1.1 installed and configured, I need to make sure that Crystal Reports was installed correctly with the Ripplestone installer.  This was just a matter of checking the permissions and testing the running of a report within Ripplestone.

All together it took an extra 30 minutes to get everything installed and configured correctly.

How the Ripplestone Database Connections Work

I had a question from one of our clients today about the logs in their SQL Server database.  The SQL Server logs would show that there was a failed login attempt each time a report was run within Ripplestone.

This is caused by the way that Ripplestone runs a report that is getting it’s data from a database.  When a report is run in Ripplestone, it first allows the Crystal Report engine to try and connect to the database without using the Ripplestone Database Connection. If it fails to connect, it then gets the Ripplestone Database Connection information from the Ripplestone database and uses that login information to try and connect to the database.  The first attempt will get logged in SQL Server as a failed login.  The user running the report does not see this failed attempt and the report will run as expected.

For more information on why Ripplestone uses Database Connections and how to create them go the following document Setup A Database Connection.

Table Differences in aspnet Membership table for Access and SQL Server

I’m working on a project for a new version of Ripplestone that uses the aspnet membership tables and this project needs to support both SQL Server and Access databases.

I created the code for SQL Server first and used a field called LoweredUserName from the aspnet_Users table in the SQL Server database.  This code worked great, but when I coded the Access Provider the code would not work.  The problem ending up being that the Access table for aspnet_Users does not have a field called LoweredUserName.

To fix the problem I need to add a check to determine which provider I was using and skip the LoweredUserName field when using the Access data provider.