NAV 2017 table definition does not match SQL table

Doomhammer

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.

thomasbarbut

Hello,
if the field has no DATA just delete it from SQL. .
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

But I am not sure, why this happened and not sure if there are other tables with this problem...

thomasbarbut

Maybe the database was not synchronized before you did the backup or there were uncompiled objects.

best regards,
Thomas Barbut

Doomhammer

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.

thomasbarbut

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

These fields are from an Extension. Fields from an extensions are not visible in the Dev Environment.

Doomhammer

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?

Doomhammer

bump. anyone?

Yutavo

Hello,
"Has sales forecast" field - it is a part of extension.
Try to uninstall standard extensions. It should help you.

lynge

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

OK, thank you for information, I will try it. Extensions are new for me

Kowa

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

robintheswede

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.