Oracle SP vs. MS SQL Reporting Services

• rata tata

  SSC Enthusiast

  Points: 147

  More actions

  July 24, 2005 at 9:11 am

  #110154

  Hi all,

  I am trying to use an Oracle stored procedure to return a data set for my report in SQL Server Reporting Services.

  First, is that doable?

  If yes, my stored procedure has one output parameter which is the REFCURSOR for the returned data set. How can I define the parameter in the reporting services? And how can I handle the data set?

  Any help? And useful links?

  Thanks,

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  July 25, 2005 at 1:29 pm

  #576943

  I don't think SQL Server is going to have any idea what to do with a REFCURSOR object.  I'm guessing because I've never worked with Oracle, but it sounds impossible.

  I think you'll need a version of the stored procedure (on the Oracle server) that returns the actual results, not a cursor.  Then you can use OPENQUERY or OPENRESULTSET to execute the procedure.

• Antoon Vansina

  Valued Member

  Points: 64

  More actions

  July 26, 2005 at 6:14 am

  #577124

  It's supposed to work.

  Look for info on

  http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs

  I have to add that I've tried it several times, but I can't get it to work. I now write the SQL directly

• rata tata

  SSC Enthusiast

  Points: 147

  More actions

  July 26, 2005 at 6:24 am

  #577129

  Hi Antoon Vansina,

  I found the solution just a minutes before you post the link which includes the solution!

  It now works like a charm.

  if you want any help let me know.

   

  Thanks,

• Pune Boy

  Newbie

  Points: 3

  More actions

  August 1, 2005 at 10:32 am

  #578615

  Hi there,

  Could you post solution to use Oracle Stored procedure in Reporting Services..

  Here is my post on MS board.

  http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?pg=2&guid=&sloc=en-us&dg=microsoft.public.sqlserver.reportingsvcs&fltr=

• rata tata

  SSC Enthusiast

  Points: 147

  More actions

  August 3, 2005 at 12:51 am

  #579026

  Hi Pune Boy,

  Follow the following steps for a clean new report.

   

  0. Make sure that you can access oracle DB by writing a query not SP.

  1. Add a new report in the solution

  2. Created a new Data Set as follows:

  - Command type = Storedprocedure

  - The only text to be added is : MyOracleSchema.MySP

  3. The DataSource is pointing to MS OLE DB Provider for Oracle and the Type of the data source is set to Oracle, so the connection string should only have

  "data source={your DS}"

  4. you stored procedure in Oracle should have 1 and only 1 out parameter of type REF Cursor which is the only supported data type in Oracle.

  5. If this works, then you may add some IN parameters to your SP and update your report to pass them.

  - Add the parameters by clicking the "..." next to the Dataset, and set

  the parameters in the parameters tab(the out param is not required).

   

  Let me know if this works.

   

  Thanks,