Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Compatibility issue and CROSS APPLY

By Divya Agrawal,

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.

Total article views: 5074 | Views in the last 30 days: 1
 
Related Articles
FORUM

compatability level

compatability level

FORUM

compatibility level

compatibility level

SCRIPT

Change database compatability level of all databases

Changes the database compatibility level of all databases to the given level.

FORUM

compatibility level

compatibility level

FORUM

Is it possible to simulate the cross apply function?

Can't change compatibility to 90. Cross apply and Pivot won't work.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones