locked
ExecuteReader requires an open and available Connection. The connection's current state is closed RRS feed

  • Question

  • With PowerPivot, we can import data from Physical SQL Tables in SANDBOX database.

    With PowerPivot, we can import data from Physical SQL Tables in SNAPSHOT database.

    Those databases are hosted separately in different SQL Servers.

    With PowerPivot, we cannot import data from SQL Views in Sandbox; we have observed that PowerPivot cannot execute queries with SQL Views Connecting to other database. (This is the case of SQL Views in Sandbox database that actually looks up to a SNAPSHOT database from another server.). And the error we are getting while specifying a query in the Table Import Wizard is: ExecuteReader requires an open and available Connection. The connection's current state is closed.

    Tuesday, February 19, 2013 12:18 PM

Answers

  • Hi roel2000,

    I tried to reproduce this issue on my test environment. However, everything is OK when I import data from SQL views. In your case, could you please post the SQL query to us for further investigation?

    In the "Tabel Import Wizard" dialog box, you can try to select "Select from a list of tables and views to choose the data to import" option, and then select the SQL Views.

    For more information, please see:
    Importing SQL Server data into PowerPivot for Excel: http://searchsqlserver.techtarget.com/feature/Importing-SQL-Server-data-into-PowerPivot-for-Excel

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Friday, March 1, 2013 1:52 AM
    Tuesday, February 26, 2013 5:59 AM

All replies

  • Hi roel2000,

    I tried to reproduce this issue on my test environment. However, everything is OK when I import data from SQL views. In your case, could you please post the SQL query to us for further investigation?

    In the "Tabel Import Wizard" dialog box, you can try to select "Select from a list of tables and views to choose the data to import" option, and then select the SQL Views.

    For more information, please see:
    Importing SQL Server data into PowerPivot for Excel: http://searchsqlserver.techtarget.com/feature/Importing-SQL-Server-data-into-PowerPivot-for-Excel

    Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Friday, March 1, 2013 1:52 AM
    Tuesday, February 26, 2013 5:59 AM
  • Hello, this still happens. The first time you try to write a SQL, it works fine. But after you saved the model, say you want to change the query, now you go to table properties under design tab. But the dialogue box freezes and says not responding. When you click the close button on the dialogue box, it gives you 3 options saying Microsoft Excel is not Responding - 1. Try to restore the program 2. Close the program 3. Wait for the program to respond. Now if you select the first option, the Sql statement is now editable. However, this is when you see the message "ExecuteReader requires an open and available Connection. The connection's current state is closed."

    This is with Excel 2016, and source being an Oracle database. This happens every single time and you can't move forward. Only option is to delete the source and create a new one with the changed query. Obviously that's quite a problem because you then have to recreate all the relationships and will lose all the pivot tables you created based on the source.

    Sunday, June 11, 2017 3:58 AM