New to SQL Reporting/BIDS

[szed]

SQL Server Business Intelligence Studio 8
Oracle 10
Informix

I am a current Crystal user who is getting moved to Sql server reporting. I am testing a few things and having issues connecting to my existing stored procedures that are behind my current Crystal reports. I have some stored procedures in informix and oracle databases and most have parameters.
Can anyone offer some guidance? The Microsoft help files are not helping me, I've tried what is listed. I am looking for specific syntax on how to get the data sources on the reports set up. I have tried numerous ways on the data tab where the stored procedure option is in the drop down but none of my syntax will work. I've also tried using the text option and calling the stored procedures, but I am getting errors there too.
Any help at this point would be greatly appreciated!!!

 

[RiverGuy]

Can you post a sample error message?

 

[szed]

I am getting the following error:
An error occured while retrieving the parameters in the query.
ORA-00911:invalid character
ORA-06512: at "SYS.DBMS_UTILITY",line 114
ORA-06512: at line 1

I have selected stored procedure and typed the following in the command query:
STOREDPROCNAME('01-NOV-08')

This stored procedure takes a start date parameter.
My current crystal report sends this back to the database:

BEGIN "SCHEMA"."PACKAGE"."STOREDPROCNAME"CUR, TO_DATE ('01-11-2008 08:56:52', 'DD-MM-YYYY HH24:MI:SS')); END ;

But when I try to put this in it errors out the same.
I am sure it's a syntax issue but I haven't been able to find any examples of syntax to use for either Oracle or informix.
Thanks!!

 

[RiverGuy]

Interesting. I don't use Oracle or Informix, and this is a long shot, but try a different date format, such as:

STOREDPROCNAME('20081101')

 

[szed]

Thanks but it didn't work, same error. All the microsoft documentation says that this is supported but I have yet to see an example.

 

[szed]

I have finally found a solution after many trials and errors, at least for the oracle connection, I wanted to post it in case it would benefit anyone else.
I set the data source as Oracle, put in my connection, then on the datasheet window I put the following in the query window:
SCHEMA.PACKAGENAME.STOREDPROCEDURENAME
Then I went to the parameter tab and entered the date parameter with the same name as it is in the stored procedure and set it equal to =Parameters!PARAMETERNAME.Value
and it finally worked, I got a record set back.
I am working on the informix side, I will post it to this thread if I find a solution.