Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ronmc
Starting Member
12 Posts |
 Posted - 2006-03-31 : 10:35:26
|
| I just wanted to pass this on. If you have upgraded from sql2000 to sql2005, the compatibility level of the database may still be set to 2000. On my local PC, I upgraded to 2005. We had a problem with a client when running a select statement. They where on Sql2005. When I tried to duplicate the problem on my local PC, I did not get a problem. Basically, Sql2005 has a problem with using derived field names and an alias in the order by clause.For example: select data1 as dataone from mytable order by mytable.dataone would not work in sql2005, but will work ok in sql2000. The command sp_dbcmptlevel 'databasename',90 will set the compatibility level to 2005.I realize that using an alias in the order by clause is probably not a "best practice", but we are working with some legacy code here.Just a tip, for anyone who can use it. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-03-31 : 16:04:58
|
| Yes this is the case with all SQL Server upgrades. After you are done upgrading, you need to change the compatibility level if you want to use the new version's features. So if you've ever upgraded from 6.5 to 7.0 or 6.5 to 2000 or 7.0 to 2000, then this is a step that you should be familiar with.Tara Kizeraka tduggan |
 |
|
|
|
|
|
Setting the database compatibility level