Recently I have been working with the 'CROSS APPLY' operator, and I faced a strange error while using it. This article describes how I solved it.
You might be aware of the CROSS APPLY operator, which operates on two input tables, the second of which can be a derived table. These tables can be called the left and right tables. The CROSS APPLY operator implements one logical query processing phase -- it applies the right table expression to each row from the left table, and produces a result table with the unified result sets.
Let's say I have a table named 'Sales.Customer' and 'Sales.Order'. My objective was to retrieve the top three customer details who have placed the last order. The query below could easily achieve this output.
SELECT C.custid, A.orderid, A.orderdate FROM Sales.Customers AS C CROSS APPLY dbo.fn_TopOrders(C.custid,3) AS A
Unfortunately to my surprise it did not work. Every time I executed this, it always gave me the error:
Incorrect syntax near '.'.
When I removed the function from the right table and use a subquery instead, it worked fine. What is this strange problem? I was very confused, and I searched on the Internet and many blogs. Everywhere I found the same syntax. There was nothing wrong with my query.
Ultimately I found the solution while checking my configuration. I was working with a database having compatibility level of 80. This compatibility level is for SQL Server 2000, and the database had been previously a SQL Server 2000 database that was migrated to SQL Server 2005. I changed the compatibility level as 90 and the query worked perfectly.
To change the compatibility level of any database you can right click the database and go to the properties. Then under that select 'Options' where you can select the proper compatibility level and save the settings.
This can also be achieved via a T-SQL statement as:
exec sp_dbcmptlevel 'MyDatabase',90
The next time when such issue an arises, if you can see no problem with your syntax or other things, just check for the compatibility level.
