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.