NAV 2017 table definition does not match SQL table

Doomhammer
Doomhammer Member Posts: 211
in NAV Three Tier
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

  • thomasbarbut
    thomasbarbut 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,
  • Doomhammer
    Doomhammer 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
  • thomasbarbut
    thomasbarbut Member Posts: 25
    Maybe the database was not synchronized before you did the backup or there were uncompiled objects.

    best regards,
    Thomas Barbut
  • Doomhammer
    Doomhammer 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
  • thomasbarbut
    thomasbarbut 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'
  • guidorobben
    guidorobben Member Posts: 157
    These fields are from an Extension. Fields from an extensions are not visible in the Dev Environment.
  • Doomhammer
    Doomhammer 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
  • Doomhammer
    Doomhammer Member Posts: 211
    bump. anyone?
    Martin Bokůvka, AxiomProvis
  • Yutavo
    Yutavo Member Posts: 7
    Hello,
    "Has sales forecast" field - it is a part of extension.
    Try to uninstall standard extensions. It should help you.
  • lynge
    lynge 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 :-).
  • Doomhammer
    Doomhammer Member Posts: 211
    OK, thank you for information, I will try it. Extensions are new for me :)
    Martin Bokůvka, AxiomProvis
  • Kowa
    Kowa Member Posts: 926
    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
  • robintheswede
    robintheswede 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.

Categories