Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: Error Loading PivotTable Containing Two Fields with Same Name

XL2000: Error Loading PivotTable Containing Two Fields with Same Name

View products that this article applies to.

This article was previously published under Q207522

↑ Back to the top

Symptoms

When you open a Web page that contains a Microsoft Office PivotTable Component, you may receive the following error message:
An error occurred while trying to use a query published from Excel. Consult the creator of the Web page.

0x800a6986: Cannot use a stored procedure, query or SQL command that does not have unique names or aliases for all output fields.
When you click OK, you receive an error message similar to the following:
The PivotTable list "PivotTable2" could not connect to the data source "XLDataSource". For more information about the data source, consult the creator of the file.

0x80040e14: "[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'XLDataSource'."

↑ Back to the top

Cause

This problem may occur when the following conditions are true:
  • You published the Office PivotTable using a PivotTable stored on a Microsoft Excel worksheet.

    -and-

  • You created the Excel PivotTable from data stored in an external SQL database.

    -and-

  • You added one or more of the database fields to the Excel PivotTable twice.

↑ Back to the top

Workaround

To work around this problem, change the column heading used for one of the duplicate fields in Microsoft Query. To do this, follow these steps:
  1. Open the Excel workbook containing the PivotTable that was used to publish the Web page.
  2. Select a cell within the PivotTable, and then click PivotTable and PivotChart Report on the Data menu.

    The PivotTable and PivotChart Wizard opens in step 3 of the wizard.
  3. Click Back.
  4. Click Get Data. Click OK if you receive the error message: "This query cannot be edited by the Query Wizard."

    Microsoft Query opens with the query that was used to create the PivotTable.
  5. Click to select one of the duplicate columns in the Data pane.
  6. Click Edit Column on the Records menu. Type a unique name for the column in the Column Heading box, and then click OK.
  7. Click Return Data to Microsoft Excel on the File menu.
  8. Click Finish in the PivotTable and PivotChart Wizard.
  9. Because one of the column names has changed, one or more fields are now missing from your PivotTable. Drag the newly renamed field from the PivotTable toolbar to the appropriate area on the PivotTable.
  10. Republish the PivotTable.

↑ Back to the top

Keywords: KB207522, kbprb

↑ Back to the top

Article Info
Article ID : 207522
Revision : 1
Created on : 11/5/2003
Published on : 11/5/2003
Exists online : False
Views : 349