Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Compatibility issue and CROSS APPLY Expand / Collapse
Author
Message
Posted Thursday, August 6, 2009 12:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:52 AM
Points: 143, Visits: 553
Comments posted to this topic are about the item Compatibility issue and CROSS APPLY

--Divya
Post #765979
Posted Thursday, August 6, 2009 3:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:27 AM
Points: 337, Visits: 2,282
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!
Post #766028
Posted Thursday, August 6, 2009 4:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
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
Post #766074
Posted Thursday, August 6, 2009 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 7:39 AM
Points: 1, Visits: 19
Really time-saver post.
Thanks,
Post #766087
Posted Thursday, August 6, 2009 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:07 AM
Points: 134, Visits: 122
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.
Post #766155
Posted Thursday, August 6, 2009 9:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 28, 2014 5:00 PM
Points: 68, Visits: 279
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.
Post #766318
Posted Thursday, August 6, 2009 9:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #766335
Posted Thursday, August 6, 2009 10:02 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, December 15, 2014 9:36 AM
Points: 652, Visits: 1,901
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.
Post #766358
Posted Friday, August 7, 2009 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, April 27, 2013 8:35 AM
Points: 8, Visits: 27
where is the function fn_TopOrders?
How do i create it?

thanks
Great article
Post #766863
Posted Friday, August 7, 2009 11:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 15, 2014 6:52 AM
Points: 143, Visits: 553
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
Post #767163
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse