Wednesday, May 9, 2012

"Syntax error near a" and the Acinta_Split() function

I have come across this error while compiling the view/dynamic view "ACINTA_DV LedgerOfAccountsRelation" in the C5 data model:
"Syntax error near a".

It happens in this line of the view:

    SELECT COUNT(*)
    FROM Acinta_Split(';', a.TXLLEVXRK) x



The error occurs because SQL Server does not recognize the function "Acinta_Split", which is part of the Acinta Shortcut package.

Now, the function was actually compiled and present in the system, so the error seems mysterious at first.
It turns out that the database was configured to have compatibility level 80 = SQL Server 2000.

And since table-based functions were introduced in SQL Server 2005 it is also the natural explanation why SQL Server throws a syntax error at us.

Solution

To get out of this error you have two choices:

  1. Set the compatibility level of the database to at least 90 (SQL Server 2005)
  2. Comment out the parts of the view that make use of the Acinta_Split function. Block comments have the form: "/* <commented code> */
Obviously, you should choose solution 1. whenever possible. But under some circumstances this is not possible. One such circumstance is when you have a C5 version 3.0 solution, because this version of C5 is only compatible with SQL Server 2000; It does not work with later versions of SQL Server.

Applies to

Please note that this error and its solution applies generally and not just to the C5 solution. I.e. it may apply to:
  • Acinta Shortcut for C5
  • Acinta Shortcut for Ax
  • Acinta Shortcut for Nav
  • Acinta Shortcut for XAL
  • Acinta Shortcut for Visma