Yesterday I thought it would be a quick addition to add a new column to my Fact table. Then add the dimension to Analysis Services. And finally add the dimension to the cube. but I ran into the error below and after struggling for quite a while I found the issue.

NOTE: This is possibly one of many solutions.

  1. I had added a new column to my Fact Table.
  2. I had then created and processed the new dimension and that was all working fine.
  3. I then added the Dimension to the cube, and when I tried to process the cube I got the following error:

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

Errors in the OLAP storage engine: An error occurred while processing the ‘TF Alert SCOM’ partition of the ‘TF Alert SCOM’ measure group for the ‘SCOM Alerts’ cube from the SCOM database.

OLE DB error: OLE DB or ODBC error: Invalid column name ‘AlertClosedID’.; 42S22.

Internal error: The operation terminated unsuccessfully.

  1. It too me ages to figure it out and I found that in the Partitions tab of the cube I had created aNamed Query for the binding Type

 

  1. The reason for the cube failing to process was because in the above query it would not include my new column. Which now makes sense with the error message saying that it cannot find or invalid column name.
  2. So to fix this you could do one of two things:
    1. Add the new column name to your query
    2. Or change your Query Binding query to Select * from TableName

i.      This way if you ever added new columns it would automatically be included.

  1. I then went and processed the cube after adding the new column and it processed successfully.

0 thoughts on “OLE DB error: OLE DB or ODBC error: Invalid column name ‘xxx’.; 42S2”

  1. Thanks Mate. This saved a lot of my time as well.
    For some release, I created another copy of the production cube (for UAT) and edited the DSV to point to new version of tables and views (diferent names). Everything was fine, but still processing failed with ODBC error.
    In the end your post saved the day !!

Leave a Reply

Your email address will not be published. Required fields are marked *