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:
You, sir, have just saved me (potentially) hours of debugging and hair-pulling. Well done. Thank you.
Perfect answer. I was pulling my hair for the past two days.
Started pulling my hair until I read your post
you have to run the query in the master database :-)
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?
THANK YOU! Solved the problem.
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 .
From the future, thanks.... it's make me crazy...
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.
I too found this very helpful. I can't change the compatibility mode so will have to find an alternative solution. Thanks!
kabylus // thank you very mush~!! very very nice answer~!!
-from (south)korea
ah snap, nice one :)
Post a Comment