Crystal Reports and stored procedures with parameters

Question

Hello Everyone!

I work with Crystal Report 2008 sp2, and during creation of a new report template I've encountered a problem. I created a stored procedure, which prepares and pivots needed data on the server side, and filters it using a parameter.

When I try to add this stored procedure to the report template in Crystal Editor, I receive SQL error 102 - incorrect syntax near ')'. Although I can easily add user defined function to my report with exactly the same parameter.

What could be the source of the problem?

P.S. Stored procedure does run and return correct data if I run it in the SSMS.

Answer 1

As long as you are returning a record set from the store procedure try using a command object

in the command object:

EXEC mystoredProc ({?variable1}, {?variable2})

Answer 2

The "exec mystoredProc()" was a good try, but didn't work for me.

Crystal to/from Oracle 10g SPs is kicking MY butt... I've worked every example from BOXI/SAP, a couple from the web, and from "Crystal Reports 9 on Oracle", and all I get are Crystal's/Oracle's error messages.

The latest is "Object Specified is incompatible with the flag specified." ORA-04047, with an ORA-20004 and an Ora-06512 thrown in for good measure.

I'm trying to get the simplest SP return for a beginning... Just give me the Select * results from a table.

Any hints?

I've tried the Oracle native (client) drivers, The Oracle ODBC drivers... I haven't gotten the Crystal Oracle drivers to work, tho'. Depending upon the driver I'm using, I can get PLS-00302 - Component must be declared.

I've written and re-written these things over and over. Right now, I'm not a happy Oracle camper. MS SQL was sooo much easier.