Sql Error sync-navtenant error NAV18

Fran_castle
Fran_castle Member Posts: 2
in NAV Three Tier
I´m upgradaing from Nav 13 to Nav 18, when ´m trying to compile objects I´m getting this error

The following SQL error was unexpected:Incorrect syntax near '$7'.

In the Event Viewer


Message:
<ii>El siguiente error SQL no se esperaba:
Incorrect syntax near '$7'.
Instrucción SQL:
DECLARE @StatisticsToDrop NVARCHAR(MAX);
SELECT @StatisticsToDrop = COALESCE(@StatisticsToDrop +', ', '') + '[Ex$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('Ex$Cust_ Ledger Entry')
AND (c.name = 'Customer Posting Group' OR
c.name = 'Salesperson Code' OR
c.name = 'No_ Series' )
@ROWCOUNT > 0 BEGIN
SET @StatisticsToDrop = CONCAT('drop statistics ', @StatisticsToDrop)
EXECUTE sp_executesql @StatisticsToDrop
END

Any Idea?

Best Answer

Answers

  • Fran_castle
    Fran_castle Member Posts: 2
    Thnks ErictP! Yesterday I saw the post, but I didn't realize that the answer was a little bit lower Lol
  • firozansari
    firozansari Member Posts: 3
    I´m upgrading from Nav 13 to Nav 18, when I´m trying to compile objects getting below error in Cust. Ledger entry and Value Entry.

    "The following SQL error was unexpected: Incorrect syntax near '$7'.
    "
    Please suggest how to resolved. I tried above process to resolve but couldn't success.

    Thanks in Advance
  • abrar273
    abrar273 Member Posts: 6
    I am stuck in the same situation for nav 13 to nav 18 upgrade, getting this same error error in Cust. Ledger entry and Value Entry.

    "The following SQL error was unexpected: Incorrect syntax near '$7'.

    I tried this solution but no success.

    @firozansari what solution you found?

    @ErictP

    Awaiting for response.

    Thanks in advance.
  • Montgo
    Montgo Member Posts: 3
    I run into the same problem.

    This is the SQL command:

    DECLARE @StatisticsToDrop NVARCHAR(MAX);
    SELECT @StatisticsToDrop = COALESCE(@StatisticsToDrop +', ', '') + '[BA$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('BA$Cust_ Ledger Entry')
    AND (c.name = 'Customer Posting Group' OR
    c.name = 'Salesperson Code' OR
    c.name = 'No_ Series' )
    @ROWCOUNT > 0 BEGIN
    SET @StatisticsToDrop = CONCAT('drop statistics ', @StatisticsToDrop)
    EXECUTE sp_executesql @StatisticsToDrop
    END


    If i use the SQL Management Studio there is no error. But Sync-NAVTenant always throws a syntax error The following SQL error was unexpected: Incorrect syntax near '$7'.

    Does anybody know how to fix this?

    Best Regards
  • cafeine
    cafeine Member Posts: 11
    Hi everyone, thanks for the useful tips above.

    Had the same issue for 2013-2018 upgrade, with Customer Ledger Entry index 7 and Value Entry index 12.

    The script didn't work for me but from some extra digging around SQL I solved the issue by using SQL Management Studio, finding the INDEX on that table (it's named as $7 etc same as error message) and I first took a backup of the index (Right click on the index > Script Index as > Create To > New Query Window, and then deleted the index from the table.

    You need to do this for all companies in the database.

    After compiling the object in NAV Dev Env. , I ran the previously saved INDEX CREATE scripts to repopulate in the index in each company.

    After that I recompiled with validation the table in NAV Dev Env and everything worked fine.

    I am no SQL expert so if someone disagrees with the above solution please comment so we can all learn.

    Thanks,
  • AntidotE
    AntidotE Member Posts: 61
    edited 2024-02-03
    helped link is relocated: https://marijebrummel.wordpress.com/2017/12/14/nav2018-upgrade-issue-with-sync-navtenant/
    but it did not help me, instead Cafeine's process was helpful and worked for me
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.

Categories