Table Item in SQL Server

jasr23
jasr23 Member Posts: 14
in SQL General
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....

Best Answer

Answers

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    It does. But it is called [your company$Item]...
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • jasr23
    jasr23 Member Posts: 14
    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
    ara3n Member Posts: 9,258
    It might be a permission issue on sql. Make sure you have at least a db_reader role on sql.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • jasr23
    jasr23 Member Posts: 14
    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
    Slawek_Guzek Member Posts: 1,690
    Do you have a multi-tenant environment?
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • jasr23
    jasr23 Member Posts: 14
    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
    jasr23 Member Posts: 14
    working with user SA with all permissions
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    edited 2017-11-02
    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • jasr23
    jasr23 Member Posts: 14
    edited 2017-11-02
    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
    Slawek_Guzek Member Posts: 1,690
    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.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • jasr23
    jasr23 Member Posts: 14
    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.
  • jasr23
    jasr23 Member Posts: 14
    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!

Categories