Compatibility issue and CROSS APPLY

Question

Compatibility issue and CROSS APPLY

Divya Agrawal

Hall of Fame

Points: 3144
More actions
August 6, 2009 at 12:14 am

#213129

Comments posted to this topic are about the item Compatibility issue and CROSS APPLY
--Divya

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

peter-757102 SSCertifiable Points: 6877 More actions · Answer 1

Divya,

Good to mention this problem, its just the title is a little dramatic for those not reading the article as cross apply compatibility is not really broken. But its understandable as this is needed to be found by people that have the described issue.

I have been running into the same thing in the past and lucky for me quickly realized what was the issue as I had the code running in another database with the correct compatibility level before.

It seems as when the database compatibility level is at 80 uses a distinct parser that is unaware of the later additions to the language. Otherwise it would be able to point out that cross apply of a function is not possible given the compatibility setting.

Useful submission!

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 2

Ummm... good article, but surely as a matter of course you would be careful to check the version being run when using new SQL Server statements? For instance, if you tried to run a recursive CTE, you'd also get the same issue...

Random Technical Stuff[/url]

aliinal Newbie Points: 9 More actions · Answer 3

aliinal

Newbie

Points: 9

August 6, 2009 at 5:21 am

#1035272

Really time-saver post.

Thanks,

Dave Slee Old Hand Points: 300 More actions · Answer 4

I would caution that if you have any deprecated syntax in the database's views and stored procs (SQL-89 joins for example), changing the compatibility mode can cause them to break. You want to carefully consider any changes to your databases' compatibility levels. Just like newer syntax is won't work on older compatibility levels, some deprecated syntax will not work with newer compatibility levels.

If you're not sure whether this applies to your database or not, Microsoft's Upgrade Adviser Utility is a good place to start. Unfortunately, if your applications have a lot of in-line SQL, this process can become significantly more painful.

G33kKahuna SSCommitted Points: 1726 More actions · Answer 5

Come on; such simple solutions are expected out of novice heads me not brilliant minds like you .... 🙂

Changing compatibility is not an option in most cases especially when you are housing a pure 80 DB with 80 specific query constructs or features that are deprecated in 90. Another way to workaround is to create a new DB with compatibility set to 90 and cross query the 80 database.

SanjayAttray SSChampion Points: 13157 More actions · Answer 6

Kaushik Kumar (8/6/2009)
Come on; such simple solutions are expected out of novice heads me not brilliant minds like you .... 🙂
Changing compatibility is not an option in most cases especially when you are housing a pure 80 DB with 80 specific query constructs or features that are deprecated in 90. Another way to workaround is to create a new DB with compatibility set to 90 and cross query the 80 database.

Agreed. -- Create a new DB with compatibility set to 90 and cross query the 80 database on 2005 server. What if application still supports 80 compatibility only and you have to upgrade server from 2000 to 2005 due to business policy/requirement ? Changing compatibility does not looks like an option in above case.

By the way, I am dealing issues / supporting with 3 application right now which are housed on 2005 with compatibility 80.

SQL DBA.

David McKinney SSChampion Points: 10472 More actions · Answer 7

I've found that you can do a surprising amount of new stuff in a database with compatibility level 8..

I say surprisingly because I didn't expect to be able to use any of the new features of 2005.

I would echo what's been said before that changing the compatibility level from 8 to 9 isn't something you should do without considering the implications. Why was the compatibility 8 to begin with? There may be a\ good reason.

jaroho Old Hand Points: 310 More actions · Answer 8

where is the function fn_TopOrders?

How do i create it?

thanks

Great article

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 9

fn_TopOrders returns the top 3 Orderid,Orderdate from the SalesOrder table. As my issue was not more based upon that i have not included the function..!!

--Divya

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 10

While the problem is a simple one, I am sure every one of us has a story when we spent hours if not days on something completely trivial only to say at the end: Duh! Thank you for sharing your experience, I am sure you saved some folks a lot of frustration.

SuperDBA-207096 SSCrazy Eights Points: 8176 More actions · Answer 11

Changing the compatibility level without checking to see if something else broke could be a recipe for disaster; not really sure if this is a viable solution, I think the answer to this is a little misleading.

Compatibiltiy level can effect many other things within the application, so I would proceed with caution!

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 12

Yes you are right. But we can do one thing change the compatibility level to 90 and then perform the operation and again change the compatibility to 80..

This problem i have faced as we have migrated from SQL Server 2000 to 2005 but the compatibility level remains the same, so it wont make any harm as such

--Divya