Microsoft Dynamics AX (Archived)

ODBC statement: Cannot insert the value NULL into column 'RECID'

(0) Share

Report

Posted on by Ishu.caddengg@hotma...

505

Hi All,

I am creating ODBC connection and want to insert record in table from one DB to another.

I am using following job for this.

Statement                       statement;
    str                             query, criteria;
    LoginProperty                   loginProperty;
    OdbcConnection                  odbcConnection;

    loginProperty = new LoginProperty();
    loginProperty.setDSN('AX_ETL');

    odbcConnection = new OdbcConnection(loginProperty);

    statement = odbcConnection.createStatement();

    query = "INSERT into Target.dbo.Test (EXECUTIONID) select ExecutionId from Source.Budget where TransactionCurrencyAmount = 300";//

    new SqlStatementExecutePermission(query).assert();

    statement.executeUpdate(query);

    CodeAccessPermission::revertAssert();

Above query will return more than one record.

where Target database is AX database and Source database is another DB in same SQL Server.

When I run this job I am getting error

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Ishu.caddengg@hotma... 505 on at

Like (0)

Report

Error screen shot

If single record is also inserted in Ax table, the Job gave same error.

For now there is no primary index in the table. As far I understand this RECID should automatically generated by AX itself. Please correct me if I am wrong.

Thanks in Advance

Was this reply helpful? Yes No
Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

each AX table need a recid to save as mandatory. your ODBC SQL query is trying to insert record without a recid. Destination(SQ Server) is complaining about this.

although there are ways to consume recid(s) by getting next recid and sending that as part of your insert BUT what is the reason to bypass AX framework? what business needs has forced you avoid X++ and use direct SQL

Was this reply helpful? Yes No
Martin Dráb 237,844 Most Valuable Professional on at

Like (0)

Report

Your design is horribly wrong. Please don't try again to bypass AX business logic when creating records.

The recommended way for integration is the Application Integration Framework (document exchange, web services) and you may use other techniques as well, such as DIXF or Master Data Management. All of them go through AOS, therefore they can handle Record IDs, respect security, don't corrupt cache and so on...

Was this reply helpful? Yes No
Suggested answer

Ishu.caddengg@hotma... 505 on at

Like (0)

Report

Thanks for the reply martin.

I managed the same thing by selecting the record from source table with sql query and then inserting the resultset from AX only.

Was this reply helpful? Yes No