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

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

 

Advertisements
This entry was posted in Dynamics NAV. Bookmark the permalink.

One Response to NAV2018 – Upgrade Issue with Sync-NAVTenant

  1. René Brummel says:

    Had exactly the same issue. Thanks bro!

    Liked by 1 person

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s