Sql Error sync-navtenant error NAV18

Fran_castle

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?

ErictP

I had the same with an upgrade from NAV2013 to NAV2018.

This helped me.
https://markbrummel.blog/2017/12/14/nav2018-upgrade-issue-with-sync-navtenant/

ErictP

I had the same with an upgrade from NAV2013 to NAV2018.

This helped me.
https://markbrummel.blog/2017/12/14/nav2018-upgrade-issue-with-sync-navtenant/

Fran_castle

Thnks ErictP! Yesterday I saw the post, but I didn't realize that the answer was a little bit lower Lol

firozansari

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

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

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

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

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