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)

Suggested answer

Ishu.caddengg@hotma... 505 on at

Like (0)

Report

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

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
Martin Dráb 236,275 Most Valuable Professional on at

Like (0)

Report

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

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
Sohaib Cheema 48,656 User Group Leader on at

Like (0)

Report

RE: SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'RECID', table 'TEST'; column does not allow nulls. INSERT fails.

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
Ishu.caddengg@hotma... 505 on at

Like (0)

Report

RE: SQL error description: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into column 'RECID', table 'TEST'; column does not allow nulls. INSERT fails.

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