|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 8:03 AM
Points: 100,
Visits: 321
|
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, March 19, 2010 4:26 AM
Points: 128,
Visits: 929
|
|
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!
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Thursday, December 17, 2009 2:07 PM
Points: 206,
Visits: 393
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, February 09, 2010 2:00 AM
Points: 1,
Visits: 10
|
|
Really time-saver post.
Thanks,
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, December 21, 2009 3:12 PM
Points: 116,
Visits: 49
|
|
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.
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Thursday, March 18, 2010 1:24 PM
Points: 57,
Visits: 192
|
|
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.
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: Friday, March 19, 2010 11:12 AM
Points: 3,212,
Visits: 972
|
|
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.
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 1:59 AM
Points: 358,
Visits: 809
|
|
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.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Saturday, March 06, 2010 2:26 AM
Points: 8,
Visits: 21
|
|
where is the function fn_TopOrders?
How do i create it?
thanks
Great article
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 8:03 AM
Points: 100,
Visits: 321
|
|
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
|
|
|
|
