SQL Statement insert error when post sales order shippment.

Business Central/NAV Technical Forum
1

Hey guys,

I need some help! The environment is NAV 5.0 + SQL 2005 SP2. I got an error message when I tried to post the sales order. Navision prompted me an Item Ledger Entry sql insert statement error from SQL Server saying that the values dont match the column definitions.

Well, I need to tell you a little bit more about what I have done. Eventually, I had an requirement that the customer need to see the item inventory per locations in another system which synchronizes data from Navision. So what I have done is to create a key “Item No.” + "Location Code"on Item Ledger Entry table and assign “Quantity” as SumIndexQuantity, after that I found out the SumIndexTable on SQL server which maintains the Item inventory per location and add this sumindex table into a SQL replication(off course, there are lots of others replicated tables, but those are fine).

Now everything is fine, the third part application can takes data from replication and show the right inventory per location there. But when I try to post sales order in navision it complains about the insert sql statement for Item Ledger Entry does not match the column definitions etc… So what I did is to remove the sumindex table from the replication. Then I tried to post the sales order again, everything was fine. And the strange thing is sometimes when I make the sum fields on the sumindex table allow nulll I can post the sales orders, but sometimes I get that error. If i dont replicate it everything is fine.

The problems is when I copy the error message and compare the values with the columns one by one, that took me a whole lot of time, I found there is no mismatch, even I repeated this process twice they are matched. And from the beginning to the end I did not add new fields or change the column types of item ledger entry table on SQL server and no replication for this table either.

I have also taken an indeep look at the Item Ledger Entry sql table, there is a trigger called “Item Ledger Entry_TG”, it contains the logic for SQL to maintain all the item ledger entry sumindex tables. But I really cant find what is wrong here.

Now I am running out of idea.

If you guys have any idea about this please do let me know,
APPRECIATED!

Best Regards,

2

Whenever you make a change to NAV table, you have to rebuild the replication - the publication and the subscription.

This is one of the big downfalls of SQL Server replication – it’s very brittle and makes NAV harder to modify. There are other tools that do a better job – in particular LS Retail’s Data Director.

3

Hi girish,

In fact, I did regenerate it everytime when I made changes on the replication. But the problem is still there. Thanks for your information and I will check the LS Retail’s Data Director, but basically, I have realised all the functions and requirements by going with sql replication. If I change the concept at this moment it probably means strating from the beginning, and that will take me a whole lot of time. so I prefer to solve it by fixing this problem.

Thanks for your reply mate [:D]

Best Regards,

Jaska

4

Hey guys,

Thank you very much. I have given up replicating the sumindex table finally and replaced it with a normal table which works in the same way sumindex table does. By being replicating that table I have realized the same idea. So what I did is to create a normal table with the exactly same structure as the previous sumindex table and maintain this table in the codeunit “Item Jnl.-Post Line” where Navision handles Item Ledger Entry which is the key of Item inventory per location. By modifying this codeunit I can manage to maintain this table and keep it same as the sumindex table.

So now it’s working fine [:D] !

Thanks for your great ideas

Best Regards,