XL2000: Error Loading PivotTable Containing Two Fields with Same Name

• Microsoft Excel 2000
• Microsoft Office PivotTable Component 9.0, run time

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.

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'."

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.

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.

Additional query words: pivotlist XL2000

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb