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»»

List of SQL 2005 features incompatible with level 80? Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2008 3:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 12:41 PM
Points: 37, Visits: 323
Does anyone know of a comprehensive list, or link, documenting most/all the features for SQL Server 2005 that cannot be used, or are not fully compatible, with databases set at compatibility level 80?

It is not finding discussions and documentation about the effects of upping the compatibility level 80 to 90 for databases that have been migrated to SQL Server 2005. It's well discussed in these forum ;), and fairly well documented in the BOL for sp_dbcmptlevel (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm), as well as the Upgrade Advisor help file. But it is harder find a good list of the SQL 2005 features that cannot be used on level 80 databases, other than just a general statement about the new features (new T-SQL reserved words, etc).

My group has several databases migrated from SQL Server 2000 that are still on compat level 80, and now we want to "sell" the advantages of upping the level to 90. We would like to show a list of all the new features that can only be done with level 90 databases. Once the databases in production are set to 90, they would not react well to an issue that forces us back down to compat level 80. So to prove that the extra testing is worthwhile, we'd like to present the advantages in a full list.

Thanks in advance!
Post #446684
Posted Sunday, January 27, 2008 8:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
I don't know of any comprehensive lists. In my book, the biggest feature you gain by moving to 90 compat mode is being able to use all the DMVs.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #448119
Posted Monday, January 28, 2008 1:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
One important thing (which is documented in BOL) is that the "WITH" keyword for table and index hints is NOT optional in mode 90. The script generated by Enterprise Manager in SQL 2000 for table changes likes to omit the WITH keyword.


Post #448150
Posted Monday, January 28, 2008 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:14 AM
Points: 7,197, Visits: 6,341
There's no comprehensive list, but ISTR that I had to change compatibility before I could use PIVOT / UNPIVOT.

I can't tell for sure now because all the DBs I use PIVOT on are set at 90, none at 80.



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #448206
Posted Monday, January 28, 2008 5:17 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, April 28, 2014 8:16 AM
Points: 175, Visits: 281
Reports via SSMS are only available in 90 mode.

MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
Post #448228
Posted Tuesday, January 29, 2008 1:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 1:20 PM
Points: 16, Visits: 144
We had lot of trouble while migrating from 2k to 2k5. And our applciation is so big that it took us months to finaly come to 2005; but all the dbs are still at level 80. since, this took this long, there were many code changes that went into the system since the upgrade. Now, I'm planning to change the cmpatibility level to 90; but I'm stuck since, I cannot run the Upgrade Advisor on the new databases as they reside on 2005 instance. All my servers, includign Dev/QA are in 2005 now. since my dbs are big in size, I'm finding it difficult to install a new 2K instance and restore the dbs. Can anybody clear my doubts -
1) Can I restore the cmpt 80 db backups taken on a 2005 server on to a 2000 server instance?
2) Is there any way, I can make Upgrade advisor to run against cmpt 80 dbs attached to a 2005 instance?
3) Is there any scripts or so that will do the same purpose as Upgrade advisor.

Thanks,
--smk
Post #449070
Posted Tuesday, January 29, 2008 4:29 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
#1 - Absolutely not. Once a database is upgraded to the latest version, it can't be reverted to a down-level version

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #449126
Posted Wednesday, January 30, 2008 12:40 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, April 28, 2014 8:16 AM
Points: 175, Visits: 281
1,2,3: No

MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
Post #449209
Posted Wednesday, January 30, 2008 4:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, August 25, 2014 7:14 AM
Points: 7,197, Visits: 6,341
Actually, you have 2 options, but both are a LOT of hard work.

1) restore a pre-conversion backup to SQL 2000. Change the stuff you changed the first time around (without upgrading) and then run the Upgrade Advisor.

2) Script out your database and all its objects. Run the Create Statements against a brand new SQL 2000 database. As long as you don't have SQL 2k5 Only data types or other objects, this should work fine. Then use the Import/Export wizard in 2005 to send the top 500 rows or so of data from 2005 to the 2000 database. Then run the Upgrade Advisor.

You don't necessarily need the data to run the UA, but I'd advise having a little bit in your 2000 DB just in case. It won't hurt to have the data there and you might uncover something that you normally wouldn't see in the UA without the data.

Those are your only options, though. You can't restore a 2005 backup to a 2000 database or detach / attach from 2005 to 2000. Sorry.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #449262
Posted Wednesday, January 30, 2008 10:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 1:20 PM
Points: 16, Visits: 144
Thanks for all your suggestion guys.

I will go for Brandie's second option. The first option is difficult especially because of the size of the dbs and it is difficult to trace all the changes that has been applied to the system.

--smk
Post #449537
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse