Unable to rename company in Navision.

David_SingletonDavid_Singleton Member Posts: 5,479
edited 2013-01-31 in SQL General

Microsoft Dynamics NAV
The following SQL Server error(s) occurred while accessing the G/L Entry table:

15336,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Object '"Playground"."dbo"."CompanyName$G_L Entry"' cannot be renamed because the object participates in enforced dependencies.

SQL:
{CALL [sp_rename](?, ?, ?)}

OK


I get the above error.

Does this mean someone has set up some constraint directly in SQL.

I am trying to rename "CompanyName" to Playground.
David Singleton

Comments

  • rhpntrhpnt Member Posts: 688
    Sure looks like it!

    Check the table in Management Studio and please let us know.
  • DenSterDenSter Member Posts: 8,304
    Check Alter Database, Integration tab, is 'Maintain Relationships' turned on? By default, only 'convert identifiers' is turned on on that tab.
  • David_SingletonDavid_Singleton Member Posts: 5,479
    DenSter wrote:
    Check Alter Database, Integration tab, is 'Maintain Relationships' turned on? By default, only 'convert identifiers' is turned on on that tab.

    Thanks Daniel,

    just looking in detail, I can see that someone seems to have "accidentally" imported the job objects from 4.0 into a 5.00 database. My guess is they were trying to bring in the old Job Scheduler and brought in too many objects. So there are lots of links now to non existent objects. So who ever did this probably just did a quick fix to make it work and mysteriously forgot to tell anyone what they did.

    ](*,)

    Can't see any way to delete the old objects, so I will need to go in one by one and fix them to switch on maintain relationships.
    David Singleton
  • bbrownbbrown Member Posts: 3,268
    Try this:

    o Save the NAV table object mentioned in the error message

    o Open the mentioned table object in Object Designer and disable SIFT Maintence flags

    o Save and compile

    o In SSMS open the Views section for the database and delete any views that remain for this table

    o Re-import the original NAV table object
    There are no bugs - only undocumented features.
  • pdjpdj Member Posts: 643
    I know this an old thread, but I'll just say thanks a lot. :thumbsup:
    Your suggestion solved my issues with a 2009R2 database, which apparently had an orphan VSIFT index in the Value Entry table in 3 out of 4 companies.
    Regards
    Peter
  • rcverbeekrcverbeek Member Posts: 32
    Same here. Two orphaned visfts. We were able to pinpoint them in SQL Server and deleted them via SSMS.
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    bbrown wrote: »
    Try this:

    o Save the NAV table object mentioned in the error message

    o Open the mentioned table object in Object Designer and disable SIFT Maintence flags

    o Save and compile

    o In SSMS open the Views section for the database and delete any views that remain for this table

    o Re-import the original NAV table object
    I had the same error coming from a restore (navdata) of one company that was interrupted in the middle. I did what you suggested and it helped to clean the database again. But unfortunately re-importing or re-compiling doesn't re-create the VSIFTs in SQL. Does anybody have an idea how to re-create the VSIFT views from NAV?
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2018-05-12
    On each index, there is a MaintainSIFTIndex property, default=Yes. NAV creates indexed views when you change the property from No to Yes and save the table (and synchronize schema in newer versions) - but since the default is yes in order to be able to do so you must first change the MaintainSIFTIndex property to No and also save the table (and synchronize schema).

    Alternatively, you can change the order of indexes by inserting some dummy index with SIFT field on it right after the PK. If you save table all indexes and all SIFT views will be dropped and rebuild

    Sometimes if an index or indexed view has been removed saving a table with an index with MaintainSIFTIndex set to No is impossible as NAV throws an error (show SQL's missing object error). In such a case the missing indexed view must be recreated outside NAV - but it does not have to match the original. NAV does not check the definition and as long as the name will be exactly as required the view can be based on a very simple statement like SELECT 0 as Col1. As far as I remember the dummy view does not even have to have an index on it,

    I am not aware of any other methods to get NAV to re-create indexed views.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Works. Thanks a lot.

    Btw, just in case somebody faces the same issue. If the VSIFTs are missing and you try to restore a navdata backup then from NAV perspective the table definition is not equal to your source database although the exact same object is imported.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
Sign In or Register to comment.