Compatibility issue and CROSS APPLY

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

    --Divya

  • 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!

  • 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]

  • Really time-saver post.

    Thanks,

  • 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.

  • 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.

  • 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.

  • 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.

  • where is the function fn_TopOrders?

    How do i create it?

    thanks

    Great article

  • 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

  • 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.

  • 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!

  • 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

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

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