We were running lately into an issue with Dynamics AX 2009 and Microsoft SQL Server 2008 Analysis Services after switching the data source provider from SQLNCLI.1 (Microsoft SQL Server 2005 Native Client) to SQLNCLI10.1 (Microsoft SQL Server 2008 Native Client).

The switch was necessary as the provider SQLNCLI.1 (Microsoft SQL Server 2005 Native Client) was not installed locally. This is described in the Microsoft Dynamics™ AX 2009 Installation Guide in the section Configure Analysis Services to connect to your SQL Server database (page 79).

Continuing the guide in section Set up the default cubes the White Paper Microsoft Dynamics AX 2009 White Paper: Configure the Default OLAP Cubes is referenced when you don't have a full license or have turned off configuration keys. In this White Paper you are asked to edit named queries, and here the problem starts.

When you try to edit a named query (e. g. CustTable or VendTable) you might get the following error message:

SQL Execution Error.

Executed SQL statement: SELECT ...
Error Source: Microsoft SQL Server Native Client 10.0
Error Message: The multi-part identifier "dbo.CUSTTABLE.CUSTGROUP.PAYMIDTYPE" could not be bound. ...

The last part of the error message starting with The multi-part identifier... is repeated for several other fields as well.

If you compare the query created by SQLNCLI.1 with the query created by SQLNCLI10.1 you can see some differences, and those cause the issue.

With provider SQLNCLI.1
SELECT CUSTTABLE.ACCOUNTNUM, ..., ADDRESSCOUNTRYREGION.GIROACCOUNTVALIDATIONMETHOD AS ADDRESSCOUNTRYREGION_GIROACCOUNTVALIDATIONMETHOD, CUSTGROUP.CUSTGROUP AS CUSTGROUP_CUSTGROUP, CUSTGROUP.NAME AS CUSTGROUP_NAME ...
With provider SQLNCLI10.1

 

 

SELECT CUSTTABLE.ACCOUNTNUM, ..., ADDRESSCOUNTRYREGION.GIROACCOUNTVALIDATIONMETHOD AS ADDRESSCOUNTRYREGION_GIROACCOUNTVALIDATIONMETHOD, dbo.CUSTTABLE.CUSTGROUP.CUSTGROUP AS CUSTGROUP_CUSTGROUP, dbo.CUSTTABLE.CUSTGROUP.NAME AS CUSTGROUP_NAME ...

The important thing to know is that this issue does only occur on the form Edit named queries in Visual Studio BIDS 2008, it does not affect the processing of cubes.

You can of course manually recreate the named queries that result in the above error message. However be aware that all named queries that involve at least two joined tables are affected.

An easier approach would be the following one:

  1. Install the Microsoft SQL Server 2005 Native Client, e. g. by downloading the Feature Pack for Microsoft SQL Server 2005 - December 2008
  2. Switch back to the original provider SQLNCLI.1
  3. Follow the White Paper to correct your cubes (the error should not occur any more)
  4. When you are done editing switch over to the provider SQLNCLI10.1