After the data migration from AX 2009 I wanted to check if all the dimension values where right in my new system. To check that I decided to use an ODBC connection to read from my previous system and check if the new values were the same.

This is the Job I used for that:

static void Checking_PODimensionValues(Args _args)
{
    OdbcConnection  odbcConnection;
    Statement       statement;

    //Variables to connection
    #define.ServerDefault("MY_SERVER")
    #define.DatabaseDefault("MY_OLD_DATABASE")

    //Sentence to execute in SQLServer-database
    str             sql;

    //Result of excecution
    ResultSet       resultSet;

    LoginProperty   loginProperty;
    SqlStatementExecutePermission perm;

    PurchTable      purchTable;
    PurchId         purchId;
    Dimension       dimension;
    ;


    sql = "select PurchId,Dimension,Dimension2_,Dimension3_ from PurchTable where dataareaid = 'MY_COMPANY'";
    
    //Get ODBC connection
    loginProperty = new LoginProperty();
    loginProperty.setServer(#ServerDefault);
    loginProperty.setDatabase(#DatabaseDefault);

    //Create a connection to external database
    try
    {
        odbcConnection = new OdbcConnection(loginProperty);
    }
    catch
    {
        throw error("Error creating ODBC Connection");
    }

    //Exec sentence
    if(odbcConnection)
    {
        if(sql == "")
        {
            throw error("No sql sentence to execute");
        }

        //Assert permission for executing the sql string
        perm = new SqlStatementExecutePermission(sql);
        
        //Check for permission to use the statement
        perm.assert();

        //Prepare the sql statement
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);
    }

    ttsBegin;
    while (resultSet.next())
    {
        purchId         = resultSet.getString(1);
        dimension[1]    = resultSet.getString(2); 
        dimension[2]    = resultSet.getString(3);
        dimension[3]    = resultSet.getString(3);
        
        select purchTable where purchTable.PurchId == purchId;   
        if(purchTable.RecId)
        {
            //Compare dimensions
        }        
    }
    ttsCommit;

    //Close the connection
    if(resultSet)
    {
        resultSet.close();
    }
    if(statement)
    {
        statement.close();
    }

    //End the scope of the assert call
    CodeAccessPermission::revertAssert();
}

 

Executing that code, I got the following error message:

DynamicsAX2012_ODBCConnectionErrorMessage_thumb_07130592

Cannot execute the required database operation.
The operation failed.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed language setting to us_english.

Searching on the Internet I found the following post in Microsoft Support:

http://support.microsoft.com/kb/197459

 

However, after read that I thought that my problem should be due to my code because I used the same connection few minutes ago to check dimensions in another table (SalesTable). Even trying to execute the job to check again the SalesTable dimensions I found that it still works.

Finally, I check again my code and I found that I forget to change the index of the last resultSet.getString function (copy-paste, you know :)…):

    purchId         = resultSet.getString(1);
    dimension[1]    = resultSet.getString(2); 
    dimension[2]    = resultSet.getString(3);
    dimension[3]    = resultSet.getString(3);

As you can see it’s an stupid mistake but the error message has nothing to do with the index of that function so I didn’t check the code until I decided that the origin of my problem was not the same explained in the Microsoft Support website.

So, in conclusion: This error is shown if you try to get an index that you previously obtained before.

Otherwise, if you try to get an index that doesn’t exist (for example in our case index 5 having just 4 columns), the error message shown is the correct one. It says: “Invalid Descriptor Index”.

    purchId         = resultSet.getString(1);
    dimension[1]    = resultSet.getString(2); 
    dimension[2]    = resultSet.getString(3);
    dimension[3]    = resultSet.getString(5);

DynamicsAX2012_ODBCConnectionErrorIndex_Message_thumb_07130592

 

 

Hope this helps!
Cheers!