Summary: We are having an issue executing a report in Crystal Reports Server, yet the same report can be run in the Report Designer without issues. Furthermore, depending on which ODBC driver we use, the report will not work.
Client Environment: Crystal Reports Developer XI R2 SP2 running on Windows XP Pro SP3. On this machine I have the ODBC SQL Server native client (SQL Server 2005), ODBC SQL Server Native Client 10.0 (SQLServer 2008), and ODBC SQL Server (SQL Server 2000). Each ODBC driver has its own User DSN that connects to a SQL Server 2005 standard installation using trusted (Windows) authentication
Server Environment (2 machines): SQL Server 2005 Standard SP2 running on its own box. An instllation of a third party accounting software that uses Crystal Reports Server XI to generate reports, it is installed on its own box.
Issue: We are modifying an existing Crystal Report that has an embedded subreport. The current report works fine. The subreport feeds off a stored procedure from SQL Server and accepts parameters from the main report, which are in turn passed as arguments to the stored procedure.
We now want to add a second stored procedure to the subreport in order to present additional information that is not available via the first SP. We add the second SP using the existing DB connection, and link the tables on 1 field. Once the setup is done we drag fields from the second SP onto the report, preview it, and everything works. Everything is OK so long that we use the SQL 2000/2005 ODBC connections. If we switch it to the 2008 ODBC connection, we receive an error message: "Failed to retrieve data from the database" immediately followed by another error message: "Invalid argument provided."
We have tried creating a new report and recreating the subreport. The same errors pop up when using the 2008 ODBC connection. Furthermore if we try to Verify Database, we get an error message stating that the "Database cannot be found (the name of the SP), proceed to remove the table from the report". Again, this does not happen when using the 2005/2000 ODBC connections -- everything works as it should.
We are fine using the 2005 connections in the Report Designer to avoid the errors -- but we have an even bigger issue: Once we upload the report to the Crystal Reports Server for use with our 3rd party application, the subreport will not show when the report is executed by a user. The main report will show without problems, but the subreport will be blank.
The fact that we are having issues with the 2008 ODBC connection in the Report Designer as well as the issues when executing the report on the Report Server leads us to believe that we have an underlying issue in our report, and is not related to connections, security, etc. And specifically has to do with adding the second SP to the subreport.
Also, can someone explain how to specifiy the arguments to a stored procedure in a report when there are 2+ SPs in a report?
Last note: The SPs are part of the 3rd party program we are using them. We do not have access to modify them -- unfortunately we cannot JOIN the tables we need and use a single SP in the report.
This is a pretty urgent matter (aren't most things that go on here?). All help is appreciated.