Synchronisation Error: Cannot execute a data definition language command on ().

It is really useful job to cure this senseless error. Taken from russian Axapta forum.

How to add a custom report from scratch in OnTime2008 with behaviour of standard reports

This is about OnTime2008, the system used for development management. A small issue occurred during design a Customer report: we could not pass the selected project in the project tree as a parameter for SQL statement.

1. Create your stored procedure in MS SQLServer. You can easily use existing SP as a base, i.e. spS_Projects_Summary.Otherwise, you can just add an SQL-statement later in design time inOnTime2008.

2. Create and execute a command like thefollowing. Pay attention to the group of reports where you want to add your report to.
You can either add your xml-defined report design here or define it later in design time in OntIme2008.

HINT: The easiest way is just to create a copy of a standard report looking similar to one you want to create and copy-paste its xml code in MS SQL Server. It will take everything including the C# script.

USE [OnTime2008_Test]
GO

DECLARE @return_value int,
@ReportId int

EXEC @return_value= [dbo].[spI_Reports]
@ReportId= @ReportId OUTPUT,
@Name =N'LBA Deliveries', -- report name
@ReportClass= N'',
@ReportXML= N'', -- can be copied-pasted or designed later
@SqlCommand= N'LBA_spS_DeliveryReport @projectid = {CURRENT_PROJECT}', -- your storedprocedure or SQL statement
@ReportType= 4, -- group for the report: 1- defects; 2- features; 3 - tasks; 4-summaries;5 - workslog; 6 - incidents; 7 - dashboard;
@IsActive= true,
@CreatedById= 1,
@CreatedDateTime= '2008-12-12 15:23:54', -- not important
@LastUpdatedById= 1,
@LastUpdatedDateTime='2008-12-12 15:23:54' -- not important

SELECT @ReportIdas N'@ReportId'

SELECT 'ReturnValue' = @return_value

GO

3. At this stage you may launch OnTime2008 (or re-launch it in order to re-index) and open your newly created report in the approriate group of reports (Reports\Manage Reports...).
Now, just create a shortcut to the report and you can start to use it.

NOTE: Using filters depends on the report group.

The sample of xml code for the report design and the history of this issue you can find on the Axosoft forum.

Connection from AX to an External Database

There are a few options. We can create an ODBC connection on a local machine or just to connect directly without creating ODBC record.

For exmaple, we want to check whether some records exist in an external table. We should create a OdbcConnection with appropriate LoginProperty and permit to execute a SQL statement by means of SqlStatementExecutePermission class. 

server boolean checkExternalDB()
    {
    //connection parameters
        #define.ExternalTableName("CustTable")
        #define.ExternalFieldName("AccountNum")
        #define.ExternalSQLServerName("SRVAXSQL2005")
        #define.ExternalSQLDBName("DAXdb401_Standard_DEV")
        LoginProperty LP = new LoginProperty();
        OdbcConnection myConnection;
        SqlStatementExecutePermission permission;
        Statement myStatement;
        str sqlStmt = "";
        ResultSet myResult;
        boolean ret = true;
        ;

        LP.setServer(#ExternalSQLServerName);
        LP.setDatabase(#ExternalSQLDBName);
        try
        {
            myConnection = new OdbcConnection(LP);
        }
        catch
        {
            info("Check connection parameters. "+funcName());
            ret = checkFailed(strfmt("External DB Connection error in: %1"), #ExternalSQLDBName);
        }

        myStatement = myConnection.createStatement();
    //anything you want to get from the external table
        sqlStmt = "SELECT count (RecId) FROM "+#ExternalTableName+ " where "+#ExternalFieldName + " = '" + this.AccountNum+"'";

        permission = new SqlStatementExecutePermission(sqlStmt);
        permission.assert();

        myResult = myStatement.executeQuery(sqlStmt);
        while (MyResult.next())
        {
            if (MyResult.getInt(1) > 0)
            {
                //yes, records exist in the external table
                ret = checkFailed(strfmt("@LBA53"+"\n"+funcName(), strfmt("[%1].[%2].[%3]", #ExternalSQLServerName, #ExternalSQLDBName, #ExternalTableName)));
                break;
            }
        }

        CodeAccessPermission::revertAssert();

        return ret;
    } 

MS Outlook and CRM Tasks Synchronization Issue

Recently I found out an issue with synchronization of tasks between CRM module and MS Outlook in AX4.0SP2.

It looks like MS Outlook does not understand the command and causes the following error:

Method 'sort' in COM object of class '_Items' returned error code 0x80020009 (DISP_E_EXCEPTION) which means: Propriété « Start » inconnue.

I changed the synchronizeTasksOutlookToAxapta method of SmmOutlookSync_Task class in order to fix the problem like it was done in AX2009:

taskItemsCollection.sort('[Start]', false);

to

#define.startDateProperty('StartDate')
// Turn sort and include recurrences ON to get recurring tasks
taskItemsCollection.sort(#startDateProperty, false);

Debugging code on Business Connector

As MSDN our suggests, first, we should enable Debugger on the client, (do not be confused with versions! my previous link is correct for AX 4.0 and AX 2009 too, but this is only for AX 3.0)

Then, we must to enable the same on the Business Connector with Configuration Utility: (in my case I had to check both in order to debug my web service)

Now, you can add a breakpoint in your code.

Finally, you should run an instance of Debugger manually from the client's main menu because it does not start automatically as usually.

In conclusion I need to say that you should log in on your debugged application with the same user account which was used for the client and debugger sessions.

Lucky hunt for bugs!

Using Web Service With Business Connector Illustration

Images on Form and Thumbnails in Grid

It is not a very complicated topic when it comes to show pictures on a form with Window control, but showing them in a grid caused some difficulties to me.

Here is the very simple project Images&ThumbnailsInGrid that will show you the way if are a little bit lost.

You can construct Image objects from the following file types:

• Raster (bitmap) formats - .bmp, .gif, .jpg, .png, .tiff, and .exif
• Vector formats - .emf and .wmf

By the way, do not forget about MSDN:

In Microsoft Dynamics AX, the Image class is bound to the client. The class can no longer be run from the server because of the security risks that are associated with file handling.

WCF and AIF in Dynamics 2009: Chatting with Michael Merz

WCF and AIF in Dynamics 2009: Chatting with Michael Merz

initParmDefault()

Just to remember: if you want to set up some value to a field on a dialog by default you should override standard method initParmDefault().

First time, AX takes it as a value, then it saves and restores the values the user input in the dialog form.

To see how it works just override this method for the class TestDemoRunBaseBacth as the following:

public void initParmDefault()
{
 super();

 i=11;
}

All values saved by Pack()method can be seen in Usage Data form: Tools\Options\Usage Data [Jobs - for classes]

Connected Systems Conference 2008 (Montreal)

Through Leonid Ganeline's help I got an invitation from a BizTalk User Group to this event. I to attend Montreal: November 6, 2008 Thursday

What it is about:

We will share the latest on Microsoft’s SOA offering - as well as details of the strategies and technologies that Microsoft is delivering today, over the next year, and into the future. At this event, you will learn:

• BizTalk today and through FY’09
  
• Microsoft’s Roadmap for the future
  
• Microsoft’s “Real world” SOA vision, positioning and messaging
• Service Virtualization & ESB guidance 2.0
  
• RFID & mobility of BizTalk
  

Connected Systems Conference 2008 (Montreal) (more detail)

Run Class Method Pattern

just to keep in mind a pattern for Run method recommended by Best Practices for classes

public void run()
{
    #OCCRetryCount
    ;
    try
    {
        ttsbegin;

            this.adjustSetupTime();

        ttscommit;

        this.prodCostEstimation();
    }
    catch (Exception::Deadlock)
    {
            retry;
    }
    catch (Exception::UpdateConflict)
    {
        if (appl.ttsLevel() == 0)
        {
            if (xSession::currentRetryCount() >= #RetryNum)
            {
                throw Exception::UpdateConflictNotRecovered;
            }
            else
            {
                retry;
            }
        }
        else
        {
            throw Exception::UpdateConflict;
        }
    }
}

Method Overloading

Sometimes we need to change the standard behaviour of form controls. It is not a big deal when when it concerns overriding form controls' methods in design time in AOT. But when it comes to doing that dynamically it may not be as simple as a piece of cake.

From the beginning the goal was to make sorting functionality on the grid created by SysTableLookup class impossible for the user. It might have been a good solution to change the code like the following

formRun         = classfactory.formRunClass(args);
form            = formRun.form();
// allow the class to react on the form's events
formRun.controlMethodOverload(true);
formRun.controlMethodOverloadObject(this);
// here override Sorting on all datafields added on the grid
this.overrideSortMethodOnDatafields(form);
//<--
formRun.init();

but this class creates Datafields on the grid and names for these FormBuildControls are assigned by Axapta in an unpredictable way. So it was not possible to create methods like FormNameControlName_Sort().

formBuildControl = _formBuildGridControl.addDataField(_formBuildDataSource.id(), fieldId);

There is another way to do that. With the big help of AxForum I created the class SysTableLookupWithoutSort which extends the system class with the new key method:

protected void turnSortingOff(Form _form)
{
 int                             i;
 TreeNode                        gridNode, controlNode, methodsNode;
 MemberFunction                  newMethod;
 str                             source = 'public int sort(SortOrder _sortDirection){; return 0;}';
 TreeNodeIterator                iterator;
 ;
 gridNode = _form.AOTfindChild('Designs');
 gridNode = gridNode.AOTfindChild('Design');
 gridNode = gridNode.AOTfirstChild();
 iterator = gridNode.AOTiterator();
 controlNode = iterator.next();
 //take the first datafield
 controlNode = iterator.next();
 this.setCompilerWarningsOff();
 for (i = 1; i <= conlen(lookupItems); i++)
 {
         //override sort method
         methodsNode = controlNode.AOTfindChild('Methods');
         newMethod   = methodsNode.AOTadd('sort');
         newMethod.AOTsetSource(source, false);
         //compile the method
         newMethod.AOTcompile();
         controlNode = iterator.next();
 }
 this.setCompilerWarningsOn();
}

Just to show how this class works in comparison with the standard class (without the sorting functionality) I also added DemoMethodOverloadingClass class.

Moreover this class shows how various FormControls can be added on a form from scratch and their methods can be overridden from code as well: Button, StringEdit, Datafields.

You can import this DemoMethodOverloadingClass project to make your experiments with dynamic programming.

memory leak in Axapta 3.0

Recently having got an error message after using AOS for batch processing I started digging this problem and finally found out that was described in Fixlist of Service Pack 5.
(Problem #1748 Processing some X++ code constructions resulted in a memory leak.)


You may see the same error by creating a job like the following one as it was suggested in Fixlist in order to reproduce the message. I just increased the number of iteration and an info to see the progress.

static void MemoryLeakTest(Args _args)
{
   container custcont;
   int loop;
   Custtable custtable;
   ;
   select custtable;
   custcont = conins(custcont,1,custtable.data());
   for(loop = 1; loop <= 100000; loop++)
   {
   infolog.add(exception::Info, strfmt('loop %1', loop));
   [custtable] = conpeek(custcont,1); //memory leak
   }
}

Class Universal Field Changer (xpo project)

As I promised before I realized this project as a class. It was full of fun and incredible adventurous.

But anyway here is the Class_UniversalFieldChanger which provides you with the same functionality but it does not require importing something else like tables.

Also I am going to add my comments regarding using temporary tables, creating them from code, adding them methods dynamically and other tricks.

DAX Help Creation Quick Information

It may be a very good idea to provide your customer with a full documentation about customisations made during the project in .chm file - standard format of Microsoft Dynamics AX help. It can be distributed and maintained easier and more flexible.

By virtue of Arijit Basu (DAX Guy) blog I created this very simple help project DAXHelpCreation.chm which can be decompiled and analysed in order to create your own help project.

It includes DAX Guy posting itself as the first article, then my short notes about .html files preparation and, finally, the entire project from Microsoft Help Kit.

Create the f. manual!

AppDoc Bug: HTMLHelpFile property is unavailable in DAX2009

As far as I see neither this property nor HTMHelpTopic are available for newly created objects in Application Documentation branch of AOT even if I set HTMLFileEnabled to Yes.

The only cure is to restart the client.

Moreover, after that if you first choose anything but your newly created object in Application Documentation branch (if you e.g. search for it by typing its first letters of the name) or the current element you are working with in the branch loses the focus that is the case - you now cannot change these properties.


Correct me, please, if I missed something.

Universal Field Changer (xpo project)

Sometimes we need to change the values of a group of records in a table at once. I hope this project will be useful in this meaning.

It allows you to choose a table with excluding some of them. After that you can browse the table and open it directly in AOT.

You then can choose a field from the table excluding or not system fields and look through existing values to pick up or just input a new value.

Change function works only with a result of the query which text you can see as well. No checks nor validatings!

Use it at your own risk and do not forget how it can be dangerous for the system to change their fields directly!

The project is moved to. Tested on version 4.0.

Next, I am going to implement it as a class.

Go to the main table option

It was something new for me.

To have Go to the main table option working properly you should create a form and appropriate Display Menu Item for the table with the same name.

Otherwise you will get a message like this:

No object specified on menu item XXXX_TableName

Uninstall SQL 2005 Embedded Edition SSEE

Looking for how to uninstall SQL 2005 Embedded Edition SSEE? Me too. There are a lot of links in the web but I think the best one is the following from Microsoft TechNet.

Uninstalling WSUS 3.0 does not uninstall the database instance

If WSUS 3.0 is uninstalled, the database instance will not be uninstalled. The instance may be shared by more than one application, and will cause other applications to fail if it is removed.

If it is necessary to uninstall Windows Internal Database, the following commands will uninstall the application:

(on 32-bit platforms)

msiexec /x {CEB5780F-1A70-44A9-850F-DE6C4F6AA8FB} callerid=ocsetup.exe

(on 64-bit platforms)

msiexec /x {BDD79957-5801-4A2D-B09E-852E7FA64D01} callerid=ocsetup.exe

If you wish to uninstall Windows Internal Database Service Pack 2 from Windows Server 2008, you may do so by means of Server Manager.

However, the removal of the application may not remove the default .mdf and .ldf files, which will cause a subsequent WSUS 3.0 installation to fail. These files can be deleted from the %windir%\SYSMSI\SSEE directory.

Creating additional environment

If you have chosen to create an additional environment by means of copy-paste and backup-restore commands do not forget about one little thing.

1. In MS SQL management utility do backup of the database of the environment that you have chosen as an 'original' one.

2. Create another database and restore your backup to it (restore transactions logs backup too if presented).

2.1. (A litle important thing!) In the database you should find SysServerSessions table and delete all rows there.


3. Copy-paste C:\Program Files\Microsoft Dynamics AX\40\Application\Appl\Standard or other folder where your original application was installed with other appropriate name. E.g.:

C:\Program Files\Microsoft Dynamics AX\40\Application\Appl\Standard_Test

4. From DAX Setup install additional Object Server having pointed this instance to the recently created database and unchecked Start the AOS Windows service as part of installation option.

5. With Microsoft Dynamics AX Server Configuration Utility choose the new instance of AOS, create a new configuration based on the active configuration and set Application instance to the new one.

Language in user options

Accidentally changed Language in User options can cause an issue with logging in to the configuration if you have not the appropriate licence for the language. In this case you might see something alike the following.


oror even


The simplest way to fix it is to change the language in User options but if you have no administrator rights or the administrator is yourself... In this case you should use Microsoft Dynamics AX Configuration Utility

Set Configuration command to run at kernel startup parameter to the value, i.g.,

-language=en-us

or another correctly licenced language.


It will override the value chosen in the user option and the configuration then works.
Now, you are allowed to say to the system - Speak My Language!

How to delete modifications on few layers

As it is well known:

Modifications are saved in an application layer which is also specified in the configuration. All modifications to application objects in a layer are saved in a file named ax.aod.

If you need to brush off you configuration the simplest way is to do that is just to delete all those files on the file server. The only issue may be if the system is running and, therefore, those are locked.

If that case you have the other way. For example you need to dispose everything that was made on VAR and USR layers.

Open the AOT and create a new project, e.g. LayersVarUsr


Open it and use Advanced Filter/Sort tool on the toolbar or Ctrl-F3


Check AOT and open Inquiry form by clicking Select button


Here you should set the rule for utilLevel field, in our case VAR..USP to include all modifications made on these layers with patch ones as well.


Be patient, it can take much time. When the DAX finishes you will get everything to accomplish your seek-and-destroy mission.


By the way, you can take advantage of using Compare tool to decide what will next happen.

Refreshing DataSources on Form

If you have added new fields or made any other changes to the table you can reflect all that on the related DataSource using by Restore command on the appropriate Form


Quoted from the manual:

Notice that after accessing the form in the AOT the definition of the data source
is cached locally in the definition of the form. If changes are made when you
create a new field in the underlying table, you must restore the form to access the
new field in the form. Remember to save the form before you click Restore from
the shortcut menu.

To find objects in AOT with particular properties

Sometimes I need to find objects in AOT with particular properties. Find tool (Ctrl-F) works great when you set Containing text field correctly.

In the following case I tried to find all tables in AOT with Temporary property set to Yes.

debug mode from code

run debug mode when expression is false

Debug::assert(expression);

SysInfoAction and InfoLog

To do an action from InfoLog dialog you need to create SysInfoAction using one of SysInfoAction_xx classes or create your own descendant class.

For example, to allow the current user open E-mail parameters form by appropriate menu item:

Info('Check e-mail parameters,'',SysInfoAction_MenuFunction::newMenuItem('SysEmailParameters',MenuItemType::Display));

a little bug in MainMenu creation

Any time you add a submenu in MainMenu and try to delete it DAX crahses.

true for:

Kernel version: 4.0.2163.0
Application version: 4.0.2500.424

Send alerts to online users in AX 4.0

When I started looking for how to send an alert to online users I found very few links on this topic. Here they are:

• Managing alerts on MSDN
• How to Setup Alerts in Dynamics Ax 4.0, on Venkat Raghavan Padmanabhan blog
• Send message to online user in Dynamics AX 4.0 (quick & dirty) on Helmut blog
• and Sending alerts through code without error in Ax, Kamal's response to Helmut

I compiled everything and created the little project AlertFromCode.xpo that can be useful in daily administrator work. A few words about that:

- Send Alerts button on Online Users form

- Send Alert dialog where a user can choose various options


for example, to send the alert by:

• popup window inside AX environment (it can be seen on EventAlertInbox form)
• e-mail to the mailboxes of recipients
• net send command to the computer which the user connected from

moreover, it is possible to log results of e-mail and net sending attempts in Infolog (they are not stored in EventAlertInbox)


Notes:

Name of sender which placed in Subject of the message is taken from appropriate E-mail Templates which chosen in Alert Parameters.

Keep in mind that Alerter Service should be started on the client side in order to receive messages of net send command. If not the class will show the warning but it takes much time to wait for a response from the command.

Two Tables with Two Relations

Just to clarify to myself and maybe someone else how relations on tables work I created this demo project Demo2TablesRelations.xpo.

There are two tables Orders2 and Collections with two relations defined on Orders2 that work as dynalinks for Collections.


When an user chooses an item from Collection all records in the last are filtered in regards with CollectionType previously chosen on Orders form.


To change or add new records to Collection one can open the appropriate form of this table by pressing Collection button on Orders form but the relation should be cleared in Init method of Collection form.

if (element.args() && element.args().record().TableId == TableNum(Orders2))
{
Collection_ds.query().dataSourceNo(1).clearDynalinks();
}


Finally, to stand up once, I added the filter option by CollectionType.

Intercompany and direct delivery question (from Microsoft Axapta Discussion Community)

There was a question about Packing Slip Update original customer Sales Order in Intercompany Direct Delivery.

Looked like a bug but actually is a feature. Do not forget about settings in AIF!

Basic\Setup\Application Integration Framework\Endpoints\Action policies\Setup

Transferring New Items Between Business Partners In SCM

Continuing the previous topic about sending outbounds documents from one base to another I found out an interesting point concerning the transferring information on new items between two or more bases.

Say, we have two business partners which are called Customer and Vendor. The SCM between them function very simply.

When Customer wants to get some needed goods purchase orders (Purchase Requisitions) with appropriate positions (Items) are sent to Vendor.

But if Vendor start producing some new goods how can these positions (Items) be sent to Customer in order to synchronize their common space of Items table?

Microsoft Dynamics AX AIF: Sending Outbound Documents Automatically

there are questions only but I hope there will be answers soon.

Preamble

Having set up AIF based on the SCM scenario from Microsoft site I got a few questions regarding the following scheme of SCM process:

Customer GTM (Global Trading Company) sends Purchase Requisition to vendor DUO (DUO Company) and after having it posted gets Purchase Invoice.

gtm->PurchaseOrder->AIF->PurchaseRequisition->duo->SalesOrder
duo->SalesInvoice->AIF->SalesInvoice->gtm->PurchaseInvoice

1. Sending Outbound Documents Automatically

MSDN Library How to send and receive documents electronically tells us there are two ways of sending documents either automatically or manually by using Send Electronically button on appropriate forms, e.g. for Purchase Order it is

Purchase requisition

1. Click Accounts payable > Inquiries > Journals > Purchase order.
2. Click Send electronically, and then click Original or Copy.

and it really works, and in Queue Manager we can see the message ready for outbound sending.

What really intrigues me it is the other option which allows to send it automatically.

As far as I can see everything is set up correctly since the same document after posting was sent electronically. I mean all settings of Endpoints and others are correct. Using batch gives the same result - Purchase Order is printed but not sent to Queue Manager.

What should I do in order to initiate the process automatically?

Upt.: Solution

I guess many questions happen to arise to me due to lack of experience in the system and patience to read the manual attentively. But anyway, the way to do this is the following.

First of all, read it:

The record must be updated with the print option, and there is no method for sending the document in the posting form.

If you want to send a document electronically, the Print check box on the corresponding print form must be selected, and the option to print to the screen must not be selected. Otherwise, the record will be posted and the document will be displayed on the screen, but it will not be sent electronically to the endpoint.

In other words we have at least three options to send our outbound documents:

• manually, after posting (see the above);
• automatically, during the posting choosing Print requisition and Use print management destination if those were set up in advance or just pressing Printer setup to choose any destination but Screen;
• it is also possible to send that later by means of batch processing.

What regards Print management destination we can set the parameters for receiving company DUO in order to send purchase orders to this vendor always automatically.

Accounts Payable>Vendors>Duo Engineering and Trade>Setup>Print Management



In the example we just to keep all purchase orders sent automatically in Print Archive (Basics>Setup>Inquiries>Print Archive).

Finally, I do not understand why it is important not to send automatically outbound documents when Screen option is chosen but just in case this is the place in the code that should be changed to pass by this restriction:

\Classes\PurchFormLetter_PurchOrder\sendAsXML

protected void sendAsXML()
{
 boolean             newLine;

 if (new PrintJobSettings(printerSettingsFormletter, true).getTarget() != PrintMedium::Screen && !this.proforma())
 {
     newLine = journalList.first(vendPurchOrderJour);
     while (newLine)
     {
         vendPurchOrderJour.sendElectronically(XMLDocPurpose::Original);
         newLine = journalList.next(vendPurchOrderJour);
     }
 }
}

the goal

Since I began learning MS Dynamics AX and more particularly AIF (Application Integration Framework) module which was firstly provided in version 4.0 I decided to create this blog to keep in memory most interesting points, issues and maybe successful solutions or workarounds.

Hope it will be useful for myself and others who are going to step in this path as well.