Table Item in SQL Server

jasr23

Does anyone know why the NAV 2015 table 27-Item is not appear in the SQL SERVER using Management Studio??? I am not able to find it....

Slawek_Guzek

I can think of few possible problems: Any operation which updates an Item will have effect in all companies. Just a few examples: Simple updating Global Dimension 1 or 2, or Blocked affects all companies. Costing must be the same for all the companies. Think about posting a Purchase Order in one of the companies. This updates Last Direct Cost which then will go into ledgers in other companies.

Having said that - I have very little experience with NAV multi-tenant environments. I may well be wrong as I am just extrapolating problems occurring in scenarios with many companies kept within one database.

Slawek

Slawek_Guzek

It does. But it is called [your company$Item]...

jasr23

Thanks Slawek_Guzek for responding, but I can ensure you this table is not here. I don't know if it could be hidden or something else.... Any more idea?

ara3n

It might be a permission issue on sql. Make sure you have at least a db_reader role on sql.

jasr23

Do you know where is typed the relation betweeen NAV and SQL SERVER tables? Which one in NAV corresponds to which one in SQL SERVER?

Slawek_Guzek

Do you have a multi-tenant environment?

jasr23

Hello Slawek_Guzek!
Yes, it is a multi-tenant environment because only one instance of SQL works with multiple clients. About these databases and companies, in none of them can you access the table company_name$Item, or in a new query "SELECT * FROM ..." or open the table manually.... Any other suggestion?
Thanks so much for your interest.

jasr23

working with user SA with all permissions

Slawek_Guzek

The [company name$table name] naming convention certainly applies to a single tenant environment, I haven't checked in multitenant environment; I guess it should be the same, but I cannot be sure.

If you look in NAV the database properties from DEV Env, you will see a field there called "Convert Identifiers" (File -> Database -> Alter, Integration tab). When NAV creates a table or a field all characters in table/field named which can be found in Convert Identifiers field are replaced with an underscore.

Default setup includes ."\/'%[] characters

So for exampe if your company is called My Company Ltd. then the Item table at the SQL level is called My Company Ltd_$Item

Slawek

jasr23

Thanks for your feedback Slawek, but these settings are standard. I looked for as you say with no results yet. On the other hand, I realise that this specific table Item has checked on its properties "Data per Company" as "No" and "TableData Reservation Entry=d". Could this configuration being causing this incident?
Thanks in advance.

Slawek_Guzek

TableData Reservation Entry=d is just indirect permission for the code to delete entries from Reservation Entry table.

But the "Data per Company" = No settings on table Item is certainly weird. Are we talking here about standard table 27 Item? If someone invented this to share items among companies it is a very bad idea. This is indeed why you can't find Item table in your tenant databases - but this is the smallest problem you will have.

jasr23

I knew the permission to delete entries, but I am really concerned because I have never checked the option "Data per company" before and I do not certainly know what can it cause. In fact, I found this settings prepared for an installation that is working for few years ago. Yes, it is the table 27-Item with some added fields, but the standard one in the end. But, what do you want to say with "this is the smallest problem you will have"? I am afraid.

Slawek_Guzek

I can think of few possible problems: Any operation which updates an Item will have effect in all companies. Just a few examples: Simple updating Global Dimension 1 or 2, or Blocked affects all companies. Costing must be the same for all the companies. Think about posting a Purchase Order in one of the companies. This updates Last Direct Cost which then will go into ledgers in other companies.

Having said that - I have very little experience with NAV multi-tenant environments. I may well be wrong as I am just extrapolating problems occurring in scenarios with many companies kept within one database.

Slawek

jasr23

Thanks so much for your comments and interest Slawek! I will try to save this situation restoring the value "Data per company" as Yes. Thanks for your help and feedback!