locked
PowerPivot - Oracle Stored Procedure err- The SQL statement is not valid. There are no columns detected in the statement.” RRS feed

  • Question

  • The issue I’m having is with the power pivot application using data that is encapsulated into an oracle stored package.
    I find it very curious that within the query builder wizard tool, I am able to execute a stored procedure, but before finishing the wizard, I get an error that “The SQL statement is not valid. There are no columns detected in the statement.”

    • Connection tried with providers

      :MSDAORA , OraOLEDB.Oracle, OracleClient Data Provide & Oracle Data Provided for .Net

    DESIGN MODE :

    FINAL VALIDATION before FINISH ERROR:

    Monday, March 4, 2013 9:49 PM

Answers

  • PowerPivot does not support Oracle stored procedures.  You need special logic for binding the refcursor, and PowerPivot doesn't have it.  Reporting Services does, so you can create an SSRS report that just runs the stored procedure and outputs the results.  Then use the report as a DataSource for PowerPivot.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, August 22, 2013 8:19 PM
  • I finally had a ticket with Microsoft but sorry currently this feature is not supported.
    Friday, August 23, 2013 1:32 AM

All replies

  • Hi Akxaya,

    Welcome to the MSDN Forum.

    I'll move your thread to SQL Server PowerPivot for Excel forum for better support.

    Thank you for your understanding.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 5, 2013 8:17 AM
  • Hi Akxaya,

    Generally, if we want to retrieve data from SQL Stored Procedure, we can use the following command:
    execute dbo.uspGetEmployeeManagers @EmployeeID = N'1'
    (Note: This is a sample Stored Procedure for Adventures database)

    To be honest, I'm not familar with Oracle and can't perfrom a test on my test environment. But, I think you can try to use the followong command to see if this help:
    execute STORED_PROCEDURE_TEST

    Furthermore, here is an article about calling a Stored procedure in PowerPivot, maybe it will give some hints on this issue.
    Calling a Stored procedure in PowerPivot: http://www.powerpivotpro.com/2010/06/calling-a-stored-procedure-in-powerpivot/

    Regards,


    Elvis Long
    TechNet Community Support

    Wednesday, March 6, 2013 2:59 AM
  • Elvis, 

    Tried your suggestion but SORRY didn't worked. I tried giving as I use in SQl editor to execute SP but no luck!:( 

    Wednesday, March 6, 2013 7:39 PM
  • Is this still an issue?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, August 22, 2013 7:35 PM
  • PowerPivot does not support Oracle stored procedures.  You need special logic for binding the refcursor, and PowerPivot doesn't have it.  Reporting Services does, so you can create an SSRS report that just runs the stored procedure and outputs the results.  Then use the report as a DataSource for PowerPivot.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, August 22, 2013 8:19 PM
  • I finally had a ticket with Microsoft but sorry currently this feature is not supported.
    Friday, August 23, 2013 1:32 AM
  • I finally had a ticket with Microsoft but sorry currently this feature is not supported.

    Did you submit it as a feature request on Connect?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, September 13, 2013 6:52 PM