Compatibility Level 8.0

Question

Compatibility Level 8.0

WILLIAM MITCHELL

SSChampion

Points: 13766
More actions
January 14, 2008 at 7:07 am

#182680

I am working with a 3rd party web app that uses SQL Server. In June 2007, the customer installed a new server with 2005, and the app vendor upgraded their db to 2005 and set the compatibility level 9.0.
Now in January 2008, the vendor determined that they needed to reset the level back to 8.0 because they were having problems with parts of their app.
Has anybody else run into something like this, where the app needed to remain at level 8.0?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Yes, We have had the issue. And still have it unfortunately. So we are redoing the third party app In house thast works in SQL 2005 mode.

-Roy

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

So what types of things don't work at level 9.0?

I don't have access to the vendor's technical staff, so I'm just guessing what could be wrong. I'm surprised that it took them 6 months to discover that something was broken. In your case, did you know right away, or did it take time for the issues to surface?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Before we even started the upgrading our DBs to SQL 2005, we did a dry run in our QA. There we found out that the application does not work. This App was first designed in SQL 7 and was never upgraded by the vendor. So lots of embedded SQLs started throwing error. Especially the Joins.

-Roy

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 4

We also face the same problem when we changed the compatibility level from 80 to 90. Our application was a ASP web aplication and custom controls which were used in the application stopped working. We have to change the code to make the application working.

you can go through the below link which explains behavioural changes after changing the compatibility level

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

HTH

Lowell SSC Guru Points: 323518 More actions · Answer 5

we had to maintain level 80 compatibility for a while;

tracing the application, we found that some sql statements which you could get away with in 80 fail in 90;

specifically it was like this:

update outertable

set somecol=

(

select sum(anothercol)

from innertable

where outertable.id=innertable.id

group by outertable.col3) ;

in 80 you could group by columns from the tables outside of the parenthesis group, in 90 it was not allowed;

they also had a couple of views that used the old =* or *= for joining syntax.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

I had trouble years ago using this type of correlated sub query to do updates. I found that this type of approach, using derived tables, works much better and I've found no compatibility issues with it. I'll use Orders and OrderDetail for the example:

UPDATE Orders

SET Orders.SubTotal = X.Total

FROM Orders

INNER JOIN

(SELECT Orders.OrderID, SUM(OrderDetail.Total) AS Total

FROM Orders

INNER JOIN OrderDetail ON

Orders.OrderID = OrderDetail.OrderID

GROUP BY Orders.OrderID

) AS X ON

Orders.OrderID = X.OrderID

-- Optional WHERE for single update

WHERE

Orders.OrderID =

Todd Fifield