Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


List of SQL 2005 features incompatible with level 80?


List of SQL 2005 features incompatible with level 80?

Author
Message
s.rich
s.rich
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
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 Wink, 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!
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
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
Ian Yates
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445
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



Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7787 Visits: 8738
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
berto
berto
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
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
smk-127973
smk-127973
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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
Paul Randal
Paul Randal
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2161 Visits: 1714
#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
berto
berto
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
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
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7787 Visits: 8738
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
smk-127973
smk-127973
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search