none
editing powerpivot source sql error

    Question

  • Whenever I edit the sql statement an attempt to refresh the data which i use to pull data into powerpivot i receive the following error

    OLE DB or ODBC error: Login failed for user 'USER'.; 42000.

    A connection could not be made to the data source with the DataSourceID of '6a53c6f5-7688-44a3-958f-756c20782ba4',

    Name of 'connection name'.An error occurred while processing table 'Query'.

    The current operation was cancelled because another operation in the transaction failed.


    Any suggestions on what might be causing this problem or how to troubleshoot, I do have a workaround which involves recreating the dataset from scratch but id rather just edit the sql statement.

    THANKS


    Wednesday, September 18, 2013 12:32 PM

Answers

  • Hi jameslester78,

    When we try to modify the T-SQL query, Excel will use your SQL Login credential reconnect SQL database to refresh data. If we don't save my SQL Login password in the data source connection string, the connection failed.

    To solve this issue, please refer to the following steps:

    1. Open the Excel workbook, and then lanuch PowerPivot window.
    2. Click the "Home" tab -> Existing Connection button.
    3. Please select corresponding data connection in your scenario, and then select "Edit".
    4. Select "Save my password" checkbox.

    After that, we can directly update T-SQL queries to refresh data for the PowerPivot model.

    Best Regards,


    Elvis Long
    TechNet Community Support

    Friday, September 20, 2013 2:45 AM
    Moderator