lilfos
asked on
Can't modify stored procedure specs once in use by a Crystal Report
I have a report created in CR10/VS.NET 2005 that receives all of its data from an Oracle 9i stored procedure. I have added a parameter to that stored procedure per a new requirement. Now, the report will not execute, either in designer or in the app.
When I try to preview the report, I get:
Failed to open a rowset.
Details: ADO Error Code: 0x
Source: Microsoft OLE DB Provider for Oracle
Description: One or more errors occurred during processing of the command.
When I try to set the datasource location by selecting the SP in the Current Data Source box and select the SP listed in the Replace with box (which should be the updated version), I am prompted to enter the parameter values. The new parameter is present, so all seems well. When I click ok, I get:
Data object cannot be accessed/opened
&
Unknown Query Engine Error
When I attempt to switch to an ODBC connection and then switch back to OLE DB, it starts off ok. Switching to an ODBC for Oracle System DSN works fine, but I still get the "Failed to open a rowset" error when I attempted to preview the report. In fact, it still says that the source is "Microsoft OLE DB Provider for Oracle" even though I've changed to ODBC. Checking the database expert, I see that it really is set to ODBC.
When I verify the database, I'm told the database is up to date.
When I attempt to switch back to OLE DB, I'm told:
"Some tables could not be replaced, as no match was found in the new data source. Please specify the table required for any unmodified tables." I am given no chance to map columns or anything like that.
When I finally give up and remove the SP (and consequently every field in my report) and then re-add it, the new SP works fine. Great! The only downside is that I have to almost entirely rebuild my report.
Is it really not possible to update a stored procedure without rebuilding the report? Don't people have to modify the input or output of SPs somewhat frequently (especially during development)? I can only find about a half dozen posts on the Web about similar issues, but none of the threads was resolved.
Can anyone out there help?
Thanks!
When I try to preview the report, I get:
Failed to open a rowset.
Details: ADO Error Code: 0x
Source: Microsoft OLE DB Provider for Oracle
Description: One or more errors occurred during processing of the command.
When I try to set the datasource location by selecting the SP in the Current Data Source box and select the SP listed in the Replace with box (which should be the updated version), I am prompted to enter the parameter values. The new parameter is present, so all seems well. When I click ok, I get:
Data object cannot be accessed/opened
&
Unknown Query Engine Error
When I attempt to switch to an ODBC connection and then switch back to OLE DB, it starts off ok. Switching to an ODBC for Oracle System DSN works fine, but I still get the "Failed to open a rowset" error when I attempted to preview the report. In fact, it still says that the source is "Microsoft OLE DB Provider for Oracle" even though I've changed to ODBC. Checking the database expert, I see that it really is set to ODBC.
When I verify the database, I'm told the database is up to date.
When I attempt to switch back to OLE DB, I'm told:
"Some tables could not be replaced, as no match was found in the new data source. Please specify the table required for any unmodified tables." I am given no chance to map columns or anything like that.
When I finally give up and remove the SP (and consequently every field in my report) and then re-add it, the new SP works fine. Great! The only downside is that I have to almost entirely rebuild my report.
Is it really not possible to update a stored procedure without rebuilding the report? Don't people have to modify the input or output of SPs somewhat frequently (especially during development)? I can only find about a half dozen posts on the Web about similar issues, but none of the threads was resolved.
Can anyone out there help?
Thanks!
ASKER
With the ODBC connection, verify told me that the database is up to date. In OLE DB mode, it says:
The database table "[package].[procedure]" cannot be found. proceed to remove this table from the report?
Clicking yes blows away the datasource reference and all of the report fields. Clicking no results in:
The database is up to date.
Unfortunately, the problem doesn't go away after doing this. I know that the procedure is there because I can add it and rebuild the report using it. I would just like to find a way to use it without having to rebuild the report.
The database table "[package].[procedure]" cannot be found. proceed to remove this table from the report?
Clicking yes blows away the datasource reference and all of the report fields. Clicking no results in:
The database is up to date.
Unfortunately, the problem doesn't go away after doing this. I know that the procedure is there because I can add it and rebuild the report using it. I would just like to find a way to use it without having to rebuild the report.
I don't know. When you change a SP does it get a version number?
mlmcc
mlmcc
ASKER
I am not aware of one. Perhaps there is an internal version number that is available through an oracle system table. I'm thinking that Crystal is choking on the procedure signature changing more than anything.
We used some stored procedures and I don't recall having a problem with Verify but then that was 8 years ago.
mlmcc
mlmcc
ASKER
I can use a stored procedure. The problem is that Crystal doesn't like when I change the stored procedure specs once I've build a report with it. This has to be possible. Clients are always coming up with new data to display/filter in a report.
ASKER
Per the businessobjects.com link, please see the part of my original question that explains the errors I received when attempting the approach described there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I saw the errors you were getting. There is nothing related to those errors in the Crystal knowledge base. I thought the solution might transfer to your problem.
Can you build a stored procedure in your database?
If so build a simple one and build a report against it.
Modify the stored procedure
Do the SET DATABASE LOCATION
See if the report runs
It may be that once the report doesn't recognize the change it will always error.
mlmcc
Can you build a stored procedure in your database?
If so build a simple one and build a report against it.
Modify the stored procedure
Do the SET DATABASE LOCATION
See if the report runs
It may be that once the report doesn't recognize the change it will always error.
mlmcc
ASKER
>There is nothing related to those errors in the Crystal knowledge base
Right, hence my post to this forum.
>Can you build a stored procedure in your database?
Yes, of course. That is how I got the report to work with a SP in the first place.
>It may be that once the report doesn't recognize the change it will always error.
Hmmm, this must not be true since I have found a way to make it work and posted that solution above.
Thanks for your attempts to help. It sounds like you are wondering the same things I was before getting it to work.
Right, hence my post to this forum.
>Can you build a stored procedure in your database?
Yes, of course. That is how I got the report to work with a SP in the first place.
>It may be that once the report doesn't recognize the change it will always error.
Hmmm, this must not be true since I have found a way to make it work and posted that solution above.
Thanks for your attempts to help. It sounds like you are wondering the same things I was before getting it to work.
I was. I don't have a database available that I can build stored procedures in. I only have Access and XML files.
Some askers don't bother to check the Crystal KB. I know this because the answer can be found with a quick search.
Some askers don't have permissions to work in the database directly. Personally I don't understand that since report writing quite often requires you to manipulate the data and for the sake of speed it is easier to do it in the database.
The last comment was just because some times Crystal is just that way.
Glad you got this resolved.
mlmcc
Some askers don't bother to check the Crystal KB. I know this because the answer can be found with a quick search.
Some askers don't have permissions to work in the database directly. Personally I don't understand that since report writing quite often requires you to manipulate the data and for the sake of speed it is easier to do it in the database.
The last comment was just because some times Crystal is just that way.
Glad you got this resolved.
mlmcc
In the stand-alone versions it is under the DATABASE menu.
mlmcc