List of SQL 2005 features incompatible with level 80?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2005 » SQL Server 2005 General Discussion » List of SQL 2005 features incompatible with...

Add to briefcase

15 posts, Page 1 of 2

1 2 »»

List of SQL 2005 features incompatible with level 80?

Expand / Collapse

Author

Message

Posted Wednesday, January 23, 2008 3:38 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, April 15, 2013 8:18 AM
Points: 37, Visits: 303

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

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

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

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443

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. Sad

Sad

Post #448150

Posted Monday, January 28, 2008 4:37 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

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, MCDBA, MCSA

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

Group: General Forum Members
Last Login: Friday, February 22, 2013 6:52 AM
Points: 175, Visits: 279

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

Group: General Forum Members
Last Login: Thursday, February 07, 2013 9:01 AM
Points: 16, Visits: 118

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

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601

#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

Group: General Forum Members
Last Login: Friday, February 22, 2013 6:52 AM
Points: 175, Visits: 279

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

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

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, MCDBA, MCSA

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

Group: General Forum Members
Last Login: Thursday, February 07, 2013 9:01 AM
Points: 16, Visits: 118

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

15 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse