obrienj
asked on
Updating stored proc result set in Crystal Reports
I added a stored procedure to a crystal report.
After adding to the CR it appeared the same as a view/table with the fields (result set) on the left hand side under Database Fields.
But, unlike a view or table, I don't seem to be able to update changes.
i.e. I have added a number of columns to the result set that is being returned. I then went into Set Datasourse Location, found the SP and clicked update. New table fields were not added to the CR.
I can remove the SP and add it again, but this will not be an option in the future when the report is more developed. I will need to be able to update it on the fly.
Any ideas?
After adding to the CR it appeared the same as a view/table with the fields (result set) on the left hand side under Database Fields.
But, unlike a view or table, I don't seem to be able to update changes.
i.e. I have added a number of columns to the result set that is being returned. I then went into Set Datasourse Location, found the SP and clicked update. New table fields were not added to the CR.
I can remove the SP and add it again, but this will not be an option in the future when the report is more developed. I will need to be able to update it on the fly.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have tried the Verify Database option on the Database menu?
ASKER
Okay, I tried to Verify Database.... got a load of error messages, so something is wrong here.
Query Engine Error: 'ORA-24372: invalid object for describe'
Query Engine Error: 'Fail to describe a database object. OCI Call: OCIDescribeAny'
Query Engine Error: "
Then repeat of error message again.
Query Engine Error: 'ORA-24372: invalid object for describe'
Query Engine Error: 'Fail to describe a database object. OCI Call: OCIDescribeAny'
Query Engine Error: "
Then repeat of error message again.
ASKER
btw, since then I can't preview the report either... get the same error messages (wasn't getting this prior to the verify database)
ASKER
okay, it appears my SP wasn't compiled somehow.
ASKER
Okay, still trying to verify the database.
When I click it, it comes up with the Parameter box. I leave these set to null and click okay.
It then gives me the following error message:
Query Engine Error: 'ORA-24338: statement handle not executed'
Query Engine Error: ''
When I click it, it comes up with the Parameter box. I leave these set to null and click okay.
It then gives me the following error message:
Query Engine Error: 'ORA-24338: statement handle not executed'
Query Engine Error: ''
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, that last bit was the key, had to have parameters for some reason. (Actually thinking about it it makes perfect sense as it has to get the result set for the SP).
Cheers for the help.
Cheers for the help.
go to database>Database Expert> right click in the left hand panel, > what options are checked?
Verify stored procedures on first refresh? THere are a few options in here you may need to adjust.
Then you may need to go DAtabase> log off server and log back on.
Then Database>verify Database, you should see your new columns added.
Kj