Sql Error sync-navtenant error NAV18

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_castleFran_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
  • firozansarifirozansari 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
  • abrar273abrar273 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.
  • MontgoMontgo 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
  • cafeinecafeine 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,
  • AntidotEAntidotE 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.
Sign In or Register to comment.