New features of Microsoft Dynamics AX 2009 (5.0)

Convergence 2008 is coming quickly, and careful readers of the session catalog can gleam some info on the upcoming release of Dynamics AX 2009 (5.0).

The new AOS will sport some new features: a server based batch framework, multiple time zone capability, 64-bit versions, and advanced clustering support.

AX '09 will also bring a new Expense Management solution for automating the management of travel and entertainment (T&E) expenses. Integrated with Financials and Project Accounting, it is delivered through the web-based Enterprise Portal.

Dynamics AX 2009 (formerly 5.0 née 4.5)

As Convergence 2008 approaches, details are staring to leak out about the next version of Dynamics. It looks like the long-anticipated 5.0 release is going to be chronologically renamed "2009". The good news is that the release date is still 2008 … the bad news is it seems to have slipped from "first half of 2008" to "summer 2008." This of course is a way of saying "3rd quarter" that doesn't sound as bad.

There are a few high-level features that make for great ad copy: better integration with office, role-tailored menus and business dashboards, and support for tfeL-ot-thgiR languages. IT departments, though, are still waiting to see the detailed features list – all the little things that don't sound impressive in a sales pitch, but that can make a business run better.

For those who have not yet made the jump to version 4, you'll be glad to hear this: A Microsoft project manager on the Dynamics AX team has promised that clients will be able to upgrade directly from 3.0 to 5.0. It was posted in Microsoft's public newsgroup for AX programming. I'm sure this does not qualify as an official announcement, but it is a good indication that there will be a path for customers on the earlier release.

I'll keep posting news as I come across it. Heard something interesting about 5.0 / 2009? Post it as a comment!

Get external data by ODBC from Dynamics AX 4.0 and X++

A number of good blog entries have discussed how to pull information from other databases into Dynamics AX using X++.

Connecting to a Database via ODBC – Brandon George
http://dynamics-ax.blogspot.com/2005/11/connecting-to-database-via-odbc.html

Convert Axapta date to SQL datetime when querying SQL server directly – Fred Shen
http://fredshen.spaces.live.com/blog/cns!B32E9346DBBAE4E3!171.entry

Accessing an external database using X++ -- Arijit Basu
http://daxguy.blogspot.com/2006/11/updating-external-database-using-x.html

Access a SQL table that is not an Axapta table
http://dynamicsuser.net/forums/t/17136.aspx

But all of these were written for Axapta 3.0. I did not find a lot of documentation on how to do this in Dynamics 4.0. New security requirements in 4.0 mandate explicitly asserting permissions for calling outside data sources. MS has some source code samples, but they’re more for demonstrating the permissions classes than providing a functional example making calls to another database.
http://msdn2.microsoft.com/en-us/library/aa639808.aspx

For my project I needed to fetch records from a package shipping system to get the weight, tracking number, and delivery date of each box shipped. The shipping system uses a SQL database, but this code uses ODBC, so it may work for other data sources as well.

There was an additional requirement: I wanted this to run on the server, and use the AOS account for database authentication. This way, the code could be launched from any client without requiring every client to have the right SQL driver and ODBC configuration.

There was also a possible issue: The AOS is running on the 64-bit version of Windows Server 2003. The AOS is a 32-bit application, which can cause problems when trying to call a 64-bit ODBC driver.

Here is the finished code. It fetches the shipping records and copies them into a custom Dynamics table called MyShippingTrans. I've provided additional comments below.

============================

server static void GetShippingTrans(SalesTable salesTable)
{
LoginProperty loginProperty;
OdbcConnection connection;
Statement statement;
ResultSet results;
Str sqlStatement;
str dataSourceName = "MyODBCDataSource";
str databaseName = "MyDataBaseName";
MyShippingTrans trans;
SqlStatementExecutePermission sqlPermission;
;
loginProperty = new LoginProperty();
loginProperty.setDSN(dataSourceName);
LoginProperty.setDatabase(databaseName);

try
{

connection = new OdbcConnection(loginProperty);
statement = connection.createStatement();
sqlStatement = StrFmt(
"SELECT %1, %2, %3 "+
"FROM Shipment_detail WHERE " +
"Shipment_detail.packageid = '%4'"
, "tracking_number" //1
, "pkg_weight" //2
, "duedate" //3
, SalesTable.SalesId //4
);
sqlPermission = new SQLStatementExecutePermission(sqlStatement);
sqlPermission.assert();
results = statement.executeQuery(sQLStatement);
while (results.next())
{
setprefix(strfmt("Writing %1 to TLCShippingTrans"
,SalesTable.SalesId));
trans.clear();
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);
trans.DueDate = results.getDate(3);
trans.SalesId = salesTable.SalesId;
trans.insert();

}
CodeAccessPermission::revertAssert();
}
catch (Exception::Error)
{
error(strFmt("Error accessing shipping database "
+ "or writing to TLCShippingTrans"
+ " on sales order %1", salesTable.SalesId));
}

}

==========================

Here's the same code again, with some comments.

When declaring the method, make sure it runs on the server. Otherwise every client and batch server that runs it will need to have the SQL client and ODBC connection set up. Also, every user would need permission on the external database.

server static void GetShippingTrans(SalesTable salesTable)
{

When declaring the method, make sure it runs on the server. If it does not run on the server, then every client machine and batch server that runs it will need to have the SQL client and ODBC connection set up. Also, every user would need permission on the external database.

LoginProperty loginProperty;
OdbcConnection connection;
Statement statement;
ResultSet results;
Str sqlStatement;
str dataSourceName = "MyODBCDataSource";
str databaseName = "MyDataBaseName";

These are the variables that we'll use to create our connection to the SQL database via ODBC. For simplicity in this example, the data source and database names are hard-coded. In our production system, these are stored in a parameter table so that administrators can easily change them when necessary.

MyShippingTrans trans;

This is the Dynamics table where we'll store the retrieved data

SqlStatementExecutePermission sqlPermission;

Here's the permission object that was introduced in version 4.0 as part of the trustworthy computing initiative. It is now necessary to grant explicit permission to make a call to an external database.
;
loginProperty = new LoginProperty();
loginProperty.setDSN(dataSourceName);
LoginProperty.setDatabase(databaseName);

These lines initialize the login property with the data source name and database name established earlier. Some bloggers have reported that due to requirements of the trustworthy computing initiative, one can no longer make calls from Dynamics AX to MS SQL server using SQL authentication, even if the database server is configured too allow it. It must be done using windows authentication, which happens automatically if the .setUsername() and .setPassword() methods are not invoked. This may not hold true for access to other ODBC data sources, in which case these lines may be helpful:

// loginProperty.setUsername("UserID");
// loginProperty.setPassword("Password");


If there are problems accessing the ODBC data, I want to catch the error:

try
{

connection = new OdbcConnection(loginProperty);
statement = connection.createStatement();
sqlStatement = StrFmt(
"SELECT %1, %2, %3 "+
"FROM Shipment_detail WHERE " +
"Shipment_detail.packageid = '%4'"
, "tracking_number" //1
, "pkg_weight" //2
, "duedate" //3
, SalesTable.SalesId //4
);
This prepares our connection and defines the SQL statement that we'll send to the external data source. You can see that there are three fields we want to retrieve, and we'll select the records that match the SalesId that was passed to the method.

sqlPermission = new SQLStatementExecutePermission(sqlStatement);
sqlPermission.assert();
results = statement.executeQuery(sQLStatement);

Now we use the new permission classes. We create a permission specific to the SQL select statement that we want to use, and explicitly assert the permission. Then we fetch the ResultSet by passing the same SQL statement to the executeQuery() method.

while (results.next())
{
setprefix(strfmt("Writing %1 to TLCShippingTrans"
,SalesTable.SalesId));

trans.clear();
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);
trans.DueDate = results.getDate(3);
trans.SalesId = salesTable.SalesId;
trans.insert();
}

A while loop takes us through all the records, and assigns their values to the local table's fields. Note that the results.getType(x) must be in numerical order, or a database error results (more on this later).

CodeAccessPermission::revertAssert();

As you can guess from the name, this step removes the SQL permission granted earlier. This step is optional.


}
catch (Exception::Error)
{
error(strFmt("Error accessing shipping database or "
+ "writing to MyShippingTrans"
+ " on sales order %1", salesTable.SalesId));
}
}

Finally, we catch any errors that occurred and note the sales order on which it happened.

If you found this code helpful, take a minute and leave me a message about your current project.


======================


I mentioned above that the fields in the result set must be accessed in numerical order. If you try to access them out of order, or you reference them more than once, you'll get a database error. For example, you cannot do this:

// not in order
trans.DueDate = results.getDate(3);
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);

You also cannot do this:

if (results.getString(1))
{
// read index 1 a second time
trans.trackingNumber = results.getString(1);
{

Making these mistakes will cause one or more of these errors:

Cannot execute the required database operation. The operation failed.
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
Cannot read a record in ().
The SQL database has issued an error.

======================

A note on using ODBC when running AOS on a 64-bit version of Windows:

The Dynamics AX 4.0 AOS is a 32-bit (x86) application. A 32-bit application is not permitted to call 64-bit (x64) code on Windows Server 2003. This means that you'll need to set up a 32-bit ODBC definition on your 64-bit server if you want Dynamics to use it.

The 64-bit ODBC connections are "invisible" to 32 bit apps, so if the Dynamics AOS tries to call a 64-bit ODBC connection, you'll get an error that the DSN is not found:

ODBC operation failed.
Unable to logon to the database.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Object 'OdbcConnection' could not be created

Fortunately, it is easy to set up the 32-bit ODBC connection. The default ODBC console on 64 bit Windows Server is, of course, 64-bit itself. MS did provide a 32-bit console, though. It can be found in
%SystemRoot%\SysWOW64\odbcad32.exe

Use this to set up and configure your ODBC connections, and the Dynamics AX AOS will see them.

I've only done this for the AOS on a Windows Server, but I would guess that the same considerations apply if you're trying to run the Dynamics AX client on an x64 desktop and your ODBC calls are initiated from the client.

Perhaps Dynamics AX 5.0 will have a 64-bit AOS as an option!

==============================

Some developers claim that using CCADO is a better way than ODBC to access other databases from Dynamics AX and X++. Here are some links in case you want to do more research:

Using CCADO to interact with other databases. (translated from Spanish)
http://translate.google.com/translate?hl=en&sl=es&u=http://www.trucosax.com/phpnuke/modules.php%3Fname%3DNews%26file%3Darticle%26sid%3D73&sa=X&oi=translate&resnum=4&ct=result&prev=/search%3Fq%3DCCADO%26hl%3Den%26safe%3Doff%26rls%3Dcom.microsoft:en-us%26pwst%3D1

Data Migration in Axapta Programming
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.axapta.programming&tid=962cb419-bdc4-45c4-8faa-5c4876c18c5f&p=1

Leave me a note if you think the CCADO classes are better than ODBC.

Axapta 3.0 to Dynamics 4.0: upgrade errors and issues

The documentation that Microsoft provides for upgrading from Dynamics AX version 3 to version 4 (the 676-page implementation guide) is surprisingly complete. During our conversion process, though, we ran into a couple of unexpected error messages. Here are some of the issues we ran into, and how we worked through them.

1. =======================================
Extra tables in the database. The Dynamics AX development environment takes care of managing the SQL tables and data structure. Any tables that you want added should be done through the AOT. But this is a best practice that is sometimes ignored. In our case we had a few tables in the database for our Axapta 3.0 installation that were created through SQL server itself. (They aided communication with another application.) When running the DB Upgrade Preparation Tool (which transfers each table in the 3.0 database to a new, empty version 4.0 database) tables that were created outside the Axapta development environment cause an error. You'll want to remove these tables before the beginning the conversion process. If you get the error in the middle of the DB upgrade prep process you can delete the offending table using SQL Server management tools, and re-start the process.

2. =======================================
Errors caused by compiling before entering license keys. When you start the Dynamics 4.0 client for the first time after converting your data from version 3.0, you are presented with an upgrade checklist. The first 2 steps are compiling the application and entering your license keys. Here is where we get to a catch-22: you have to compile the application before you enter your license keys, but the compiler needs your license keys to know if you have rights to the application objects.

If you are doing a fresh installation (not migrating data from version 3.0), this is not a problem, because there will be no license keys in the database, and Dynamics allows full access in demo mode. But if you have data from Axapta version 3.0, your license keys are stored in the database. Dynamics 4.0 uses different license keys than version 3.0 and it interprets the existing keys as invalid. This will cause a number of errors while compiling, and will cause errors in later steps, like the pre- and post-synchronize data upgrade cockpit. You'll get errors like these:

• Insufficient rights on object
• Error executing code: JMGParameters (table) has no valid runable code in method numberSeqModule

The fix is to compile the application again after entering your license keys for version 4.0, and before starting the pre-synchronize data upgrade cockpit. If you already started the pre-sync data upgrade process and got an error, don't worry, you won't lose anything. Close the data upgrade form, re-compile the application, and re-start the data upgrade process. (If asked, you don't need to reset the dependent steps in the checklist.)

==================================

Hope you find these tips useful. If you are in the middle of a conversion, post a comment here letting me know how it's going!

TextBuffer.Encrypt() changes from version 3 to 4

In version 3.0 there is a basic method for encryption: TextBuffer.Encrypt(key). It is easy to use, but not very secure; so in version 4.0 it has been phased out. To make sure it is not used any more Microsoft eliminated it, but left in a decryption method to help customers migrate encrypted data. All this sounds good and reasonable, but I ran into a problem.

We had been using the TextBuffer.encrypt method to encrypt credit card numbers in v 3.0. When I tried to use the TextBuffer.decryptOld(key) in v 4, though, I found the DecryptOld method was not returning our data intact.

I've created a job to demonstrate the problem. If you run it in version 3, it works. If you run it in verion 4, the data gets corrupted.

Version 3 job =============

static void DecodeTest1(Args _args)
{
TextBuffer txt = new TextBuffer();
;
txt.setText("4444555566667777");
info(strfmt("Clear text: %1",txt.getText()));
txt.encrypt(123456);
info(strfmt("Encrypted: %1",txt.getText()));

txt.setText("ÔÑ2G¡.Ÿ¤®‹LAûH9þ");
info(strfmt("Encryped text: %1",txt.getText()));
txt.decrypt(123456);
info(strfmt("Decrypted: %1",txt.getText()));
}

===========
The output shows the text is encrypted to "ÔÑ2G¡.Ÿ¤®‹LAûH9þ" and then decrypted to plain text.

Now run just the decryption in version 4:
===========

static void DecodeTest1(Args _args)
{

TextBuffer txt = new TextBuffer();
;
txt.setText("ÔÑ2G¡.Ÿ¤®‹LAûH9þ");
info(strfmt("Encryped text: %1",txt.getText()));
txt.decryptold(123456);
info(strfmt("Decrypted: %1",txt.getText()));
}

The result is
Decrypted: 444455僮56鷄667777
instead of
Decrypted: 4444555566667777
(In case the unicode characters don't display, in the version 4 result the 7th and 10th digit are replaced by Asian characters.)

I did not see any fixes from Redmond and could not find anyone else reporting problems with the DecryptOld method.

We had to work around this issue by decrypting the data in version 3 to a temporary field. After the data conversion, the data was re-encrypted and the temporary field deleted.

Even though our conversion is done, I'd love to hear from anyone who ran into this same problem or knows about this issue.