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

DECLARE @StatisticsToDrop NVARCHAR(MAX);

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

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 (c.name = 'Business Unit Code' OR

c.name = 'Gen_ Bus_ Posting Group' OR

c.name = 'Gen_ Prod_ Posting Group' OR

c.name = 'No_ Series' OR

c.name = 'Tax Group Code' OR

c.name = 'VAT Bus_ Posting Group' OR

c.name = 'VAT Prod_ Posting Group' )

IF @@ROWCOUNT > 0 BEGIN

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

EXECUTE sp_executesql @StatisticsToDrop

END

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

end

close stat_cur

deallocate stat_cur