NAV2018 – Upgrade Issue with Sync-NAVTenant

Ok, so today I had another issue in my NAV 2018 upgrade that made my blood pressure go up. I kept getting a weird error in the Sync-NAVTenant PowerShell command. I was afraid that this would be a showstopper and I had to report the issue and wait for CU1 or CUx.

What did I do…

As in any upgrade I had opened the NAV2017 database in a NAV2018 C/Side, deleted everything but tables and imported my merged objects. Compile with Schema Sync Later and then you should be ready to apply the schema changes.

First of all, be aware that this takes a while. It moves a lot of data around in this step. My database is 150GB and it ran for about half an hour. We have relatively good HP Lefthand SAN boxes.

This is the error that I kept getting

Sync-NAVTenant : The following SQL error was unexpected:
Incorrect syntax near 'Unit'.
At line:1 char:1

And this is the SQL Statement that gave the error. I found it in the Windows Event Log


SELECT @StatisticsToDrop = COALESCE(@StatisticsToDrop +', ', '') + '[Clean Company$G_L Entry].'+

FROM sys.stats AS s

INNER JOIN sys.stats_columns AS sc ON (s.stats_id = sc.stats_id AND sc.object_id = s.object_id)

INNER JOIN sys.all_columns AS c ON (c.column_id = sc.column_id AND c.object_id = s.object_id)

WHERE s.object_id = OBJECT_ID('Clean Company$G_L Entry')

AND ( = 'Business Unit Code' OR = 'Gen_ Bus_ Posting Group' OR = 'Gen_ Prod_ Posting Group' OR = 'No_ Series' OR = 'Tax Group Code' OR = 'VAT Bus_ Posting Group' OR = 'VAT Prod_ Posting Group' )


SET @StatisticsToDrop = CONCAT('drop statistics ', @StatisticsToDrop)

EXECUTE sp_executesql @StatisticsToDrop


The Solution…

The message is indicating that it was trying to delete statistics which failed. So my idea was to do that before the upgrade myself, hoping that then NAV would skip it. And it did.

The script is one that I stole from my good friend Jorg Stryk a long time ago. You can find it here but you should make a small change.

This is the modified script. I hope this may help someone else running into this issue.

set statistics io off

set nocount off

declare @id int, @name varchar(128), @statement nvarchar(1000)

declare stat_cur cursor fast_forward for

select [id], [name] from sysindexes

where (indexproperty([id], [name], N’IsStatistics’) = 1)

and (isnull(objectproperty([id], N’IsUserTable’),0) = 1)

order by object_name([id])

open stat_cur

fetch next from stat_cur into @id, @name

while @@fetch_status = 0 begin

set @statement = ‘DROP STATISTICS [‘ + object_name(@id) + ‘].[‘ + @name + ‘]’

begin transaction

print @statement

exec sp_executesql @statement

commit transaction

fetch next from stat_cur into @id, @name


close stat_cur

deallocate stat_cur



  1. René Brummel says:

    Had exactly the same issue. Thanks bro!

    Liked by 1 person

  2. Robert de Roos says:

    I have tried to remove all indexes, but that did not work.
    But maybe I need to do this before I have imported the new objects.
    I will try this again.


  3. Robert de Roos says:

    As a NAV guy I of-course tried to do this by removing the keys from the table in NAV. That did not work, because you cannot save the changes using force (because of data loss).
    You of-course ment deleting it using SQL management Studio and so I did and this solved the problem.
    Thank you so much… 🙂


  4. jjosetorres says:

    I have the same problem of $ 7 error that I can not solve, how is it deleted from SQL Management?
    Thank you very much

    Liked by 1 person

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.