TTSBegin Error in AX 2009 when retrieving SQL data and placing it into an AX table

Question

In AX 2009, I have some code that runs a stored procedure on the same database, and loops round the results. This works without any problems (I can use the InfoLog or the debugger to see the values). This code is being run in a class called from a Form.

What I would like to do is add the results from the stored procedure into an AX table. When I use TTSBegin inside the loop, or even outside the loop, but still in the same method, I get these error messages:

Cannot execute the required database operation. The SQL database has issued an error.

One or more critical STOP errors have occurred. Use the error messages below to guide you or call your administrator.

Stack trace: An error occurred while starting TTS transaction.

Does anyone know how I could fix it?

Thanks so much!

Edit: Here is the code that is causing the problem. As soon as the code execution reaches TTSBegin, I get a stack trace error.

public void run()
{
    Connection              connection;
    Statement               statement;
    str                     query;
    Resultset               resultSet;
    str                     storedProcedureName, parameterValue;
    str                     QtyStr;
    SalesQty                salesQty;
    int                     qty;
    ItemId                  itemID;
    int                     sumItemCount;           // Item summary variable
    EventInbox              inbox;
    int                     counter;
    ;
    
    // create connection object
    connection = new Connection();

    // create statement
    statement = connection.createStatement();

    storedProcedureName = "usp_AX_GetBuildPlanDetails";
    parameterValue = "030623";

    // Define the SQL statement
    Query = strFmt("EXEC %1 %2", storedProcedureName, parameterValue);

    // assert SQL statement execute permission
    new SqlStatementExecutePermission(query).assert();

    // when the query returns result,
    // loop all results for processing
    //BP Deviation documented
    resultSet = statement.executeQuery(query);

    try
    {
        while (resultSet.next())
        {
            // (1) Read in the ItemID from SQL.
            itemID = resultset.getString(2);
            infolog.add(Exception::Info, strfmt("Item ID: %1", itemId));

            // (4) Gather the QTY for that week.
            QtyStr = resultset.getString(11);
            qty = str2int(QtyStr);
            salesQty = abs(qty);  //Use absolute QTY value to account for accidental negative values.

            // Increase the item counter.
            sumItemCount++;
        }

        // This EventInbox code (for the alerts) causes a Stack Trace error with the TTSBegin code.

        // Create EventInbox event to track summary of operation
        ttsbegin;
        inbox.initValue();
        inbox.ShowPopup                         = NoYes::Yes;
        inbox.Subject                           = "Weekly Forecast Import";
        inbox.Message                           = strfmt("%1 forecast items processed.", sumItemCount);
        inbox.AlertedFor                        = "Weekly Forecast Import Results";
        inbox.SendEmail                         = NoYes::Yes;
        inbox.UserId                            = curuserid();
        inbox.TypeId                            = Exception::Info;
        inbox.AlertTableId                      = tablenum(ForecastSales);
        inbox.AlertFieldId                      = fieldnum(ForecastSales, ItemId);
        inbox.TypeTrigger                       = EventTypeTrigger::RecordInsert;
        inbox.CompanyId                         = curext();
        inbox.InboxId                           = EventInbox::nextEventId();
        inbox.AlertCreatedDateTime              = DateTimeUtil::getSystemDateTime();
        inbox.insert();
        ttscommit;
        
        infolog.add(Exception::Info, strfmt("Forecast import complete for %1 items.  Run the forecast scheduling process to replan.", progressCounter));

    }
    catch (Exception::Deadlock)
    {
        retry;
    }
    catch (Exception::Error)
    {
        ttsabort;
        ttsbegin;
        inbox.initValue();
        inbox.ShowPopup                         = NoYes::Yes;
        inbox.Subject                           = "Weekly Forecast Import";
        inbox.Message                           = "Error importing Weekly Forecast.";
        inbox.AlertedFor                        = "Weekly Forecast Import Failure";
        inbox.SendEmail                         = NoYes::Yes;
        inbox.UserId                            = curuserid();
        inbox.TypeId                            = Exception::Error;
        inbox.AlertTableId                      = tablenum(ForecastSales);
        inbox.AlertFieldId                      = fieldnum(ForecastSales, ItemId);
        inbox.TypeTrigger                       = EventTypeTrigger::RecordInsert;
        inbox.CompanyId                         = curext();
        inbox.InboxId                           = EventInbox::nextEventId();
        inbox.AlertCreatedDateTime              = DateTimeUtil::getSystemDateTime();
        inbox.insert();
        ttscommit;
    }

    endlengthyoperation();

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

Answer 1

Direct SQL activity can be tricky, so you sometimes have to play around to get it right. Below are 3 things you can try.

It looks like you should use UserConnection instead of Connection, where the former is used for operations that should stay in the same transaction scope and the latter is more for direct SQL operations irrespective of user. This might explain your transaction error. If you move your CodeAccessPermission::revertAssert(); before your ttsbegin, I'd be curious if that solves it.

1

I suspect something like this in your catch block could solve too:

// At end of TRY block
if (connection)
{
    connection.finalize();
}

// At beginning of CATCH block
if (statement)
{
    statement.close();
}

if (connection)
{
    connection.finalize();
}

The ttsAbort isn't necessary in the catch block as the system aborts the transaction automatically. It is needed for Connection/UserConnection though I think.

2

With your existing code, some things you can try in various places in your code:

connection.ttsbegin(); // Open/close a connection
connection.ttscommit();
connection.ttsabort(); // Abort a connection, in your catch block
statement.close(); // Close the statement
connection.finalize(); // Finalize the connection

3

I think changing to a UserConnection makes more sense and sharing the connection. I rewrote this without your actual environment, so there may be minor mistakes that you need to tweak. Try this method:

// Connection              connection;
UserConnection          connection; // CHANGE
Statement               statement;
str                     query;
Resultset               resultSet;
str                     storedProcedureName, parameterValue;
str                     QtyStr;
SalesQty                salesQty;
int                     qty;
ItemId                  itemID;
int                     sumItemCount;           // Item summary variable
EventInbox              inbox;
int                     counter;
;


try
{
    connection              = new UserConnection();
    statement               = connection.createStatement();
    storedProcedureName     = "usp_AX_GetBuildPlanDetails";
    parameterValue          = "030623";
    query                   = strFmt("EXEC %1 %2", storedProcedureName, parameterValue);
    
    inbox.setConnection(connection);

    new SqlStatementExecutePermission(query).assert();
    
    connection.ttsbegin();
    
    resultSet = statement.executeQuery(query);
    
    while (resultSet.next())
    {
        // (1) Read in the ItemID from SQL.
        itemID = resultset.getString(2);
        infolog.add(Exception::Info, strfmt("Item ID: %1", itemId));

        // (4) Gather the QTY for that week.
        QtyStr = resultset.getString(11);
        qty = str2int(QtyStr);
        salesQty = abs(qty);  //Use absolute QTY value to account for accidental negative values.

        // Increase the item counter.
        sumItemCount++;
    }

    inbox.initValue();
    inbox.ShowPopup                         = NoYes::Yes;
    inbox.Subject                           = "Weekly Forecast Import";
    inbox.Message                           = strfmt("%1 forecast items processed.", sumItemCount);
    inbox.AlertedFor                        = "Weekly Forecast Import Results";
    inbox.SendEmail                         = NoYes::Yes;
    inbox.UserId                            = curuserid();
    inbox.TypeId                            = Exception::Info;
    inbox.AlertTableId                      = tablenum(ForecastSales);
    inbox.AlertFieldId                      = fieldnum(ForecastSales, ItemId);
    inbox.TypeTrigger                       = EventTypeTrigger::RecordInsert;
    inbox.CompanyId                         = curext();
    inbox.InboxId                           = EventInbox::nextEventId();
    inbox.AlertCreatedDateTime              = DateTimeUtil::getSystemDateTime();
    inbox.insert();
    
    connection.ttscommit();
    
    // I believe in AX 2009 these two statements are equivalent; See `\Classes\Global\info`
    info(strfmt("Forecast import complete for %1 items.  Run the forecast scheduling process to replan.", progressCounter));
    // infolog.add(Exception::Info, strfmt("Forecast import complete for %1 items.  Run the forecast scheduling process to replan.", progressCounter));

}
catch (Exception::Deadlock)
{
    retry;
}
catch (Exception::Error)
{
    connection.ttsabort();
    
    connection.ttsbegin();
    inbox.initValue();
    inbox.ShowPopup                         = NoYes::Yes;
    inbox.Subject                           = "Weekly Forecast Import";
    inbox.Message                           = "Error importing Weekly Forecast.";
    inbox.AlertedFor                        = "Weekly Forecast Import Failure";
    inbox.SendEmail                         = NoYes::Yes;
    inbox.UserId                            = curuserid();
    inbox.TypeId                            = Exception::Error;
    inbox.AlertTableId                      = tablenum(ForecastSales);
    inbox.AlertFieldId                      = fieldnum(ForecastSales, ItemId);
    inbox.TypeTrigger                       = EventTypeTrigger::RecordInsert;
    inbox.CompanyId                         = curext();
    inbox.InboxId                           = EventInbox::nextEventId();
    inbox.AlertCreatedDateTime              = DateTimeUtil::getSystemDateTime();
    inbox.insert();
    connection.ttscommit();
}

if (statement)
    statement.close();

if (connection)
    connection.finalize();

endlengthyoperation();

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