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.
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
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])
fetch next from stat_cur into @id, @name
while @@fetch_status = 0 begin
set @statement = ‘DROP STATISTICS [‘ + object_name(@id) + ‘].[‘ + @name + ‘]’
exec sp_executesql @statement