inserting RecID in a stored procedure

(0) Share

Report

Posted on by Community Member

Hi,

I have created a table in AX that is used to cache some values from other tables, and I’m trying to fill it using a stored procedure (SQL).

I’m currently executing the procedure in AX (x++), and inserting its result into the target table, but sometimes it throws errors ( “[Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command” ),

plus it takes a longer time to finish because it’s inserting the records one by one.

So now I’m trying to fill the table through SQL, but the issue I’m facing is filling the ‘RecId’ field.

I know that AX uses the “SystemSequences” table to read the next ‘RecId’ for a given table id, and I can read from it and update it as needed, but I’ve heard that AX does some internal caching of the next RecIds, and that we may need to restart AOS whenever we edit the “SystemSequences” record manually (which is not possible in our scenario, since we’ll run this procedure multiple times a day).

So my question is, If I can guarantee that nobody will create a new record of the cache table through AX (it’s only used for some reports), and that no table will be linked with it using its ‘RecId’,

can I fill this table in SQL, give its ‘RecId’ fields unique values, and update the “SystemSequences” table accordingly, without causing any problem to AX internal system?

Thanks

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report

RE: inserting RecID in a stored procedure

Hi rawna

In order to avoid issue with the standard process, through SQL assign the Recid with a negative value ( like AX 3.0).

In my opinion, at the beginning of the store, you can get the lower value and decrement inside a loop.

In this way you can avoid to read and update the recid from the SystemSequence table.

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

RE: inserting RecID in a stored procedure

Hi rawna,

As Andre mentioned earlier it is not a recommended approach, however I have seen couple of ISV that do the same, so in the end it's up to you to decide.

Also if you will look at budget check in standard AX it uses store procedure to insert records directly in SQL because of same performance reasons as you mentioned.

Please refer to this blog post for SQL example dynamicsaxinsights.com/.../dynamics-ax-record-inserts-using-t-sql

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: inserting RecID in a stored procedure

Hi Andre,

thank you for the quick reply.

I had tried the InsertRecordList, and it wasn't that much better, since the procedure inserts millions of records. When I insert using InsertRecordList it takes about an hour to finish, while if I do it in SQL it finishes in about 4 minutes.

The reason I suggested filling the recid and updating SystemSequences manually is because (as far as I know) AX does the same when using insert_recordset, and that the only use of the cache table is to be cleared and refilled every time we run the procedure. So no one will insert, delete or update any record from it, it'll only be used in reports. Which makes me think that AX wouldn't need to check it's RecID or call any of it's internal systems.

If you still think that it's not recommended to insert in a procedure, then we'll make do with the current solution.

Was this reply helpful? Yes No
André Arnaud de Cal... 298,655 Super User 2025 Season 2 on at

Like (0)

Report

RE: inserting RecID in a stored procedure

Hi rawna,

You can better try to see if you can use the insertRecordList option to collect records to be inserted and then insert them at once.

I would not recommend to use a stored procedure yourself. The AX kernel is maintaining the SystemSequences using a stored procedure, but try to stay outside your current plans.

The recordInsertList can be used to prevent inserting records one by one.

Was this reply helpful? Yes No