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

 

8 thoughts on “NAV2018 – Upgrade Issue with Sync-NAVTenant

  1. Robert de Roos

    Hi Mark,

    I have the same issue on the standard NAV fields for Cust. Ledger Entry trying to upgrade from NAV7.1 (2013 R2).

    I have tried your solution and all statistics are removed but I still get the error message:

    PS C:\WINDOWS\system32> Sync-NAVTenant navpf2013r2upg
    Sync-NAVTenant : The following SQL error was unexpected:
    Incorrect syntax near ‘$7’.
    At line:1 char:1
    + Sync-NAVTenant navpf2013r2upg
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (0:Int32) [Sync-NAVTenant], NavCommandException
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$navpf2013r2upg,Microsoft.Dynamics.Nav.Management.Cmdlets.SyncNavTenant

    Windows event log:

    Incorrect syntax near ‘$7′.
    SQL statement:
    DECLARE @StatisticsToDrop NVARCHAR(MAX);
    SELECT @StatisticsToDrop = COALESCE(@StatisticsToDrop +’, ‘, ”) + ‘[CRONUS Pebble Wear Ltd_$Cust_ Ledger 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(‘CRONUS Pebble Wear Ltd_$Cust_ Ledger Entry’)
    AND (c.name = ‘Customer Posting Group’ OR
    c.name = ‘Salesperson Code’ OR
    c.name = ‘No_ Series’ OR
    c.name = ‘Recipient Bank Account’ OR
    c.name = ‘Message to Recipient’ )
    IF @@ROWCOUNT > 0 BEGIN
    SET @StatisticsToDrop = CONCAT(‘drop statistics ‘, @StatisticsToDrop)
    EXECUTE sp_executesql @StatisticsToDrop
    END

    This is the only Statistic left in the database and can not be removed:
    2081702764 $ndo$taskscheduling _WA_Sys_00000004_7C14436C

    Any suggestions how to solve this?

    Robert

    Like

    Reply
      1. Robert de Roos

        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.

        Like

      2. Robert de Roos

        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… 🙂

        Like

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 )

Google+ photo

You are commenting using your Google+ 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