Thursday, March 6, 2008

SQL 2008 Upgrade In Place


While working on testing the upgrade path from SQL 2005 to SQL 2008, I came across the following mesage when using the Upgrade Analyzer:


Message: SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.


This is very odd to me because the databases being upgrade have none of the above items in it. I did some searching online, and others seem to say that these messages are slightly vague. I did find a post on the MSDN Forums:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=659712&SiteID=1


but in the end I figured out that by resetting the ARITHABORT to on for all my databases would allow the Upgrade Advisor to pass this test.

sp_msforeachdb 'ALTER DATABASE [?] SET ARITHABORT ON WITH NO_WAIT'

Another thing I found with the Upgrade Advisor is it doesn't catch if you are not on SP2 or not, which is required in SQL 2005 to upgrade in place to SQL 2008. Once I fixed these two little items, everything else in the upgrade went smooth.

No comments:

Post a Comment