SQL Statement insert error when post SO shippment.

cunny

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 sumindextable 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 navision 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 sumindextable from the replication. Then I tried to post the sales order, 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 sumindextables. 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,

bbrown

Could you post the exact error message?

Marije_Brummel

Stupid maybe, but what if you insert a record from within the Management studio? You get the same error message?

Your 3rd party might also be able to get the data from the normal table instead of the sumindextable...

cunny

Hi bbrown,

Thanks for your attention, here you are the detailed error message.

---

The following SQL Server errors(s) occurred while accessing the Item Ledger Entry table:

213,"21S01",[Microsoft][ODBC SQL Server Driver][SQL Server]Insert error: Column name or number of supplied values does not match table definition.

SQL:
INSERT INTO "CRONUS"."dbo"."CRONUS$Item Ledger Entry" WITH (REPEATABLEREAD) ("Entry No_","Item No_","Posting Date","Entry Type","Source No_","Document No_","Description","Location Code","Quantity","Remaining Quantity","Invoiced Quantity","Applies-to Entry","Open","Global Dimension 1 Code","Global Dimension 2 Code","Positive","Source Type","Drop Shipment","Transaction Type","Transport Method","Country_Region Code","Entry_Exit Point","Document Date","External Document No_","Area","Transaction Specification","No_ Series","Document Type","Document Line No_","Job No_","Job Task No_","Job Purchase","Prod_Order No_","Variant Code","Qty_per Unit of Measure","Unit of Measure Code","Derived from Blanket Order","Cross-Reference No_","Original Ordered No_","Original Ordered Var_Code","Out-of-Stock Substitution","Item Category Code","Nonstock","Purchasing Code","Product Group Code","Transfer Order No_","Completely Invoiced","Last Invoice Date","Applied Entry to Adjust","Correction","Shipped Qty_Not Returned","Prod_Order Line No_","Prod_Order Comp_Line No_","Service Oder No_","Serial No_","Lot No_","Warranty Date","Expiration Date","Item Tracking","Return Reason Code","Bin Code","weight","Comment","Item Family","Item Department","Store","Unique Barcode No_") VALUES(7021,'40-000 000 001 548',{2008-08-14 00:00:00.000},1,'ALTAI','SS-0056','','J-J08',-19.0,-19.0,-19.0,0,1,'BU2200','BR42-002',0,1,0,'','','MN','',{ts '2008-08-14 00:00:00.000'},'08/14/08','','','SI+',1,10,'','',0,'','',1.0,'PCS',0,'','','',0,'SP-C',0,'','40990','',1,{ts '2008-08-14 00:00:00.000'},1,0,-19.0,0,0,'','','',{ts '1753-01-01 00:00:00.000'},{ts '1753-01-01 00:00:00.000'},0,'','',0.0,'','','','','')

---

cunny

Hi Mark,

Actually, I was thinking about using another normal table instead of the stupid sumindex table, that gives me a lot of problems, until now even I have realized the functions and requirements I am still not confident about the way that replicating the sumindex table. I just dont have a better idea, because the inventory should be managed by navision whenever the Item Ledger Entry changes, i could not find a similar table or logic which can handle this requirement. That is why I choose the sum index table.

Do you any better idea. If I have a way to maintain a normal table like the sumindex table I definitely will go with it.



Thanks for your reply mate

Regards,

Marije_Brummel

The sumindextables are redundant information. You can do the same by creating your own SELECT SUM statements on the sourcetable. If performance becomes an issue you can always create a simple covering index.

cunny

Hi Mark,

As I understood, the SELECT SUM(flowfield) only retrieves the data when you run the table or the correspondent form in C/SIDE, and the table will not contain the data when i run the table directly from SQL Query which means when the third part application takes data from that table, it will get nothing. I have thought about creating another sql table or view directly on SQL server out of C/SIDE with SELECT SUM statement to get the information from item ledger entry table directly. But unfortunately, sql view can not be replicated if the replication is for SQL Compact Edition. If i decide to use table then I dont really know how to maintain this table to make it work like sumindex table. As the inventory changes will be managed by C/SIDE.

any idea,

Thank you very much for your reply

Regards,
cunny

Marije_Brummel

If you for example calculate a flowfield on the item table, NAV C/SIDE retrieves the value from the SIFT table. However you can also calculate the same value from the corresponding source table. The SIFT tables only exist for performance reason.

Try to find your sourcetable (ledger table) and do a SELECT SUM on that.

wic

Mark Brummel wrote:

Stupid maybe, but what if you insert a record from within the Management studio? You get the same error message?

Your 3rd party might also be able to get the data from the normal table instead of the sumindextable...

Yes, good idea- copy-paste the error message in a SQL command window. does it work? (I know you could have an error with the TimeStamp that SQL see but not NAV...

ara3n

It could be also that that somebody modified the ILE while you were posting and you don't have the latest objects. I would log out and in.

cunny

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.

I've not tried wic's suggestion, not quite sure that can address the problem or not.
This development environment is stand alone on my laptop, so ara3n's concern probably is not the reason.

Anyway, thank you guys, thank you very much for your great ideas

Best Regards,