Wednesday, June 17, 2009

Cross Apply Incorrect syntax near '.'

I recently ran into an issue that had me puzzled for a bit.
I was using the new Cross Apply functionality in SQL 2005 and was getting the error:

Incorrect syntax near '.'

I checked and rechecked my syntax but couldn't figure out what I had done wrong.
After doing some googling I came accross this sqlteam post in which another poor guy had worked through the same issue.
He ended up figuring out that Cross Apply didn't work when the database was set to sql 2000 compatibility. It makes sense, though it might have been nice to get a more clear error message.

Armed with that information I looked up the code to change the db compatibility level, and what do you know my problem was solved.

Here is the code copied from the link above:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

12 comments:

Anonymous said...

You, sir, have just saved me (potentially) hours of debugging and hair-pulling. Well done. Thank you.

Anonymous said...

Perfect answer. I was pulling my hair for the past two days.

Anonymous said...

Started pulling my hair until I read your post

kabylus said...

you have to run the query in the master database :-)

Jeremy said...

Kabylus, are you saying that the Cross Apply code needs to be run in the master database?
Or the sp_dbcmptlevel needs to be run in the master database?

Anonymous said...

THANK YOU! Solved the problem.

Mahesh said...

Thanks a lot sir .... in fact i am not as much happy as today by reading this post . its save my lot of effort . 10 out of 10 rating .

Curro said...

From the future, thanks.... it's make me crazy...

Anonymous said...

thank you, I am happy to have the explanation now - however I cannot change the compatibility level of the database, as it is production DB.
I will backup and restore to another server, and I will run the DTA instead, on the copy.

Anonymous said...

I too found this very helpful. I can't change the compatibility mode so will have to find an alternative solution. Thanks!

richard kim said...

kabylus // thank you very mush~!! very very nice answer~!!

-from (south)korea

marcusc said...

ah snap, nice one :)