Options

NAV 2017 table definition does not match SQL table

DoomhammerDoomhammer Member Posts: 211
Hi,
I bumped into strange problem - NAV 2017 table definition in Development does not match table definition in SQL.
NAV 2017 CH, table 27 - Item. There is one more field in SQL table, this field is not included in Development Environment table. Field name is "Has sales forecast" (or similar).
Field is created when new company is initialised.
Problem occured after migrating to CU6.

I wanted to export nav data and import them into other database. Import is failing with message Cannot import the data because the table with the ID 27 in the specified file has different fields or key definitions than the application
database. Make sure that you are importing data into the same application as it was exported from.
Tried to synchronize table schema, verified objects in both databases...
Anyone knows, why SQL table has extra field not included in NAV table?

Thanks, Martin.
Martin Bokůvka, AxiomProvis

Comments

  • Options
    thomasbarbutthomasbarbut Member Posts: 25
    Hello,
    if the field has no DATA just delete it from SQL. :smiley: .
    If it has data try this to Synchronize SQL table and Dynamics NAV table.
    https://msdn.microsoft.com/en-us/library/dd338982.aspx
    Don't try this in production Enviroment. . .

    best regards,
  • Options
    DoomhammerDoomhammer Member Posts: 211
    But I am not sure, why this happened :( and not sure if there are other tables with this problem...
    Martin Bokůvka, AxiomProvis
  • Options
    thomasbarbutthomasbarbut Member Posts: 25
    Maybe the database was not synchronized before you did the backup or there were uncompiled objects.

    best regards,
    Thomas Barbut
  • Options
    DoomhammerDoomhammer Member Posts: 211
    No, I performed multiple sync before creating export. There are no compiled objects.
    Strange field is also created when init of new company is performed.
    Martin Bokůvka, AxiomProvis
  • Options
    thomasbarbutthomasbarbut Member Posts: 25
    I know this might sound odd . . .
    Maybe there is some trigger in the SQL Database on one of the tables which creates the columns?
    Try the below Query and check the triggers.
    SELECT 
         sysobjects.name AS trigger_name 
        ,USER_NAME(sysobjects.uid) AS trigger_owner 
        ,s.name AS table_schema 
        ,OBJECT_NAME(parent_obj) AS table_name 
        ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
        ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
        ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
        ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
        ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
        ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
    FROM sysobjects 
    
    INNER JOIN sysusers 
        ON sysobjects.uid = sysusers.uid 
    
    INNER JOIN sys.tables t 
        ON sysobjects.parent_obj = t.object_id 
    
    INNER JOIN sys.schemas s 
        ON t.schema_id = s.schema_id 
    
    WHERE sysobjects.type = 'TR'
    
  • Options
    guidorobbenguidorobben Member Posts: 157
    These fields are from an Extension. Fields from an extensions are not visible in the Dev Environment.
  • Options
    DoomhammerDoomhammer Member Posts: 211
    I have two databases, one with this field and other without. So how can I manage to get them in consistent state with same structures?
    Martin Bokůvka, AxiomProvis
  • Options
    DoomhammerDoomhammer Member Posts: 211
    bump. anyone?
    Martin Bokůvka, AxiomProvis
  • Options
    YutavoYutavo Member Posts: 7
    Hello,
    "Has sales forecast" field - it is a part of extension.
    Try to uninstall standard extensions. It should help you.
  • Options
    lyngelynge Member Posts: 85
    Agree with Yotavo, it is from the "Sales and Inventory Forecast" extension.
    It you don't need it, get rid of it:

    PowerShell:
    Get-NAVAppInfo -ServerInstance <your NAV server instance name>
    ...will list installed extensions.

    Uninstall-NAVApp -ServerInstance <your NAV server instance name>
    ...will uninstall an installed extension. It will prompt you for the name, that is why you have to list them first so you know what they are called :-).
  • Options
    DoomhammerDoomhammer Member Posts: 211
    OK, thank you for information, I will try it. Extensions are new for me :)
    Martin Bokůvka, AxiomProvis
  • Options
    KowaKowa Member Posts: 920
    If you only need to check for installed extensions, you will also find these in the dbo.NAV App table on SQL Server. Screenshot of this here:
    http://www.msdynamics.de/download/file.php?id=6290&mode=view
    Kai Kowalewski
  • Options
    robintheswederobintheswede Member Posts: 12
    Hi,
    great suggestions by Yutavo and lynge. This is probaly right in some scenarios. It didn't solve mine but your suggestions did point out the right direction for me when tourbleshooting. I had exactly the same error! I also tried to both UnInstall and also UnPublish, but the problem was that I had no apps, so my problem could never be solved this way..

    Instead, as I realized that my database didn't have any existing apps/extensions, I had to Install and Publish the apps from the NAVDVD.

    My scenario was that I had upgraded from NAV2013R2 to NAV2017RTM, and had imported objects from an environment where extensions were installed. So my upgraded objects somehow "expected" that extensions would exist installed, at least this is my interpretation. In any case, Install and Published solved my issue! :)


    PS. Great that you wrote this "dump" today, hehe, nice coincidence because I ran into the issue the same day (today)! And also, please note, I use 2017 RTM, so these kind of issues are not specific to CU6 (or later). Correct me if I am wrong :) Hope this helps someone.
Sign In or Register to comment.