locked
Why OLEDB provider says no column detected ? is it connection issue or query issue please help on this? RRS feed

Answers

  • It's a query issue. You must have some sort of variable logic or branching or multiple result sets or print statements inside your stored proc which is preventing SQL Server from supplying PowerPivot with a deterministic list of columns.  

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by MarkSubedi Wednesday, March 29, 2017 5:52 PM
    Tuesday, March 28, 2017 9:27 PM
  • PowerPivot needs to know in advance the column names and their types. Extract the contents of this stored procedure and paste them into this window and click validate.
    Tuesday, March 28, 2017 9:27 PM
  • Hi MarkSubedi,

    When you import data in Pivot window, select Advanced in Table Import Wizard. And select OLEDB Providor highlighted red line.

    >>sometime OLEDB provider also recognize column name but fetch 0 records.....

    Do you the first or second option import the data? Please test using the first option, and verify there is data in table. Then you can use the Query, your query has issue if there is no records while it has using the first.


    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MarkSubedi Wednesday, March 29, 2017 5:52 PM
    Wednesday, March 29, 2017 3:15 AM
  • Hello,

    As the others already wrote, there are some limitations & requirements on stored procedures to be used as data source for PowerPivot, for example you must use SET NOCOUNT ON flag, the SP must return only one result set, column names must be unique, etc.

    See https://social.technet.microsoft.com/wiki/contents/articles/15542.powerpivot-troubleshooting-sql-statement-is-not-valid-there-are-no-columns-detected-in-the-statement.aspx


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by MarkSubedi Wednesday, March 29, 2017 5:52 PM
    Wednesday, March 29, 2017 5:39 AM

All replies

  • It's a query issue. You must have some sort of variable logic or branching or multiple result sets or print statements inside your stored proc which is preventing SQL Server from supplying PowerPivot with a deterministic list of columns.  

    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by MarkSubedi Wednesday, March 29, 2017 5:52 PM
    Tuesday, March 28, 2017 9:27 PM
  • PowerPivot needs to know in advance the column names and their types. Extract the contents of this stored procedure and paste them into this window and click validate.
    Tuesday, March 28, 2017 9:27 PM
  • It works perfect for sql server native client 11.o but business user need reports through OLEDB provider. Not sure when to use native client 10,11,.net framework and oledb provider

    sometime OLEDB provider also recognize column name but fetch 0 records.....

    Tuesday, March 28, 2017 10:10 PM
  • Hi MarkSubedi,

    When you import data in Pivot window, select Advanced in Table Import Wizard. And select OLEDB Providor highlighted red line.

    >>sometime OLEDB provider also recognize column name but fetch 0 records.....

    Do you the first or second option import the data? Please test using the first option, and verify there is data in table. Then you can use the Query, your query has issue if there is no records while it has using the first.


    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by MarkSubedi Wednesday, March 29, 2017 5:52 PM
    Wednesday, March 29, 2017 3:15 AM
  • Angeliza, i did exactly like you mentioned and choose  write a query that will specify the data to import  and put store procedure. but it saying could not detect column name or fetch 0 records...(SQL OLEDB provider), SSMS i can execute same store procedure and returns all the required records...,But when i use native client 10.0 in my machine it works perfect. the problem is multiple business users they  want to go by sql OLEDB provider.... 
    Wednesday, March 29, 2017 4:31 AM
  • Hello,

    As the others already wrote, there are some limitations & requirements on stored procedures to be used as data source for PowerPivot, for example you must use SET NOCOUNT ON flag, the SP must return only one result set, column names must be unique, etc.

    See https://social.technet.microsoft.com/wiki/contents/articles/15542.powerpivot-troubleshooting-sql-statement-is-not-valid-there-are-no-columns-detected-in-the-statement.aspx


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by MarkSubedi Wednesday, March 29, 2017 5:52 PM
    Wednesday, March 29, 2017 5:39 AM