Data Commit in NAV

kenl

Hello,

I am using NAV4.0 with SQL option.
I have a problem for data commit in NAV.

Currently I need to import a text file. For each line in the text file, I need to create process a series of action:

1) Create a sales order
2) Create a drop shipment purchase order
3) Post Receipt for purchase order
4) Post the sales order invoice
5) Post the purchase order invoice

Now the problem comes when there is any problem within any step here. For example, if it comes error at step 3 (Posting Receipt), then the process will stop there. It would result that the system finish step 1 and 2. But stop at 3.

Is there anyway we can make sure the whole process can be done together (All or Nothing) for each line?

eg. if Line 1 can be done, then process, if Line 2 can not be process together (step 1 - 5), then skip and send to log. Then continue Line 3....


Thanks.

ara3n

Hello Kenl
Yes this can be done, but you need to change/add an additional step.


The step involved creating a new table. Bring the data into the this new staging table first. commit the transaction, then loop though the table and process one record at a time and commit if successful.

nunomaia

You will suffer a major performance decrease if you make a commit for each line :-)

ara3n

I disagree nunomia.

after every line means
) Create a sales order
2) Create a drop shipment purchase order
3) Post Receipt for purchase order
4) Post the sales order invoice
5) Post the purchase order invoice
commit;

Posting sales Invoice already commits.
Posting purchase Order already commits.



I've done hundreds of integration over the years and haven't heard anybody complaining about performance on these integrations.

nunomaia

During posting process there is a commit depending source document.

Commit it's a heavy process to a DB. Try to import one million records in one transcation, and importing again the same information committing every 10 records. Performance will be different.


I have noticed that SQL Server it’s more sensible many commit’s than SQL Server.

bbrown

nunomaia wrote:

You will suffer a major performance decrease if you make a commit for each line :-)

I also disagree. Consider processing 1000 lines with an error on line 999. If posted as a single transaction, the system would need to rollback the changes from 998 lines. This will have a much larger performance impact, then 1000 smaller transactions.

Large transactions can also impact the performance of SQL features like log shipping. Large transactions result in large log backups.

Also consider the concurrency issues. Unless you are the only user, do you want to keep locks that long?

DenSter

it's always a trade off. In this case though I tend to agree that you need a commit after each completed transaction.

kenl

Hello,

Thanks for all your reply and idea.

Actually what I like to archive would be:

For each line on the file, if any error at any step, system should RollBack what have be done for the current line. Then, continue for the next line.

eg.

1) Create a sales order
2) Create a drop shipment purchase order
3) Post Receipt for purchase order
4) Post the sales order invoice
5) Post the purchase order invoice

If the file contains 10 lines. System read the file and finish successfully for line 1, then commit. Then for next line (line 2), if posting error at step (4), then system Should rollback step 1-4 AND continue process for line 3 and so on.
The perfect case would be, if case there is any error during the process, I can write log to a table or a file.

Is it possible to do in NAV? I am considering the Rollback and catch error in CSIDE. As I know, there is no TTSBegin/TTSCommit block, as well as Try/Catch block in CSIDE. How could we handle data integration from external file?


Thanks in advance.

bbrown

You can use the return value from Codeunit.RUN to control the transactions. If return is TRUE then function succeeded.

azwierzchowski

The perfect case would be, if case there is any error during the process, I can write log to a table or a file.

Is it possible to do in NAV? I am considering the Rollback and catch error in CSIDE

You can write simple ActiveX control in C++ (better performance) or C# with RunCode and GetLastErrorMessage methods and OnRunCode event.
It's very easy in C#. If you invoke OnRun event inside try/catch block, you will be able to handle navision errors (as in Navision Timer).

In Navision you can put your code inside OnRun event handler, and invoke RunCode method.