Upgrade Advisor on 2005 instance

  • I have a group of databases I inherited that are on compatability level 8 and I want to raise them up to level 9.

    The upgrade advisor will not let me point at a 2005 instance, so I was wondering if there was an easy way to force it to do this. The utility is nifty and does much of the work for me. Some of the common things I have seen it identify in my environment for existing 2000 instances is old style joins and using table aliases in the order by clause.

    I already have a find procedure on my management DB, so I could use that for finding old style joins, but it wouldn't work so well for finding other things.

    Before you answer "place it in development/test and test it there", please just try to answer my question:

    Can the upgrade advisor utility be made to analyze 2005 db's in compat level 8?

    Thank you in advance, especially GSquared

  • Nope, it can't do that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Here's the script I use for testing upgrade from 80 to 90.

    use [database] -- Please remember to fill this in!!

    go

    dbcc UPDATEUSAGE (0) WITH NO_INFOMSGS;

    go

    DBCC CHECKDB (0) with no_infomsgs;

    go

    -- Update all table stats

    declare Tables cursor local fast_forward

    for

    select name

    from sys.all_objects

    where type = 'u'

    declare @Table varchar(100), @SQL varchar(1000)

    open tables

    fetch next from tables into @table

    while @@fetch_status = 0

    begin

    select @sql = 'update statistics dbo.' + @table + ' with fullscan'

    exec (@sql)

    --raiserror(@table, 10, 1) with nowait

    fetch next from tables into @table

    end

    close tables

    deallocate tables;

    go

    DECLARE @sql VARCHAR(max),

    @Text VARCHAR(max),

    @ProcName VARCHAR(500),

    @ProcName1 VARCHAR(500)

    DECLARE @T TABLE

    (ProcName VARCHAR(200),

    sql VARCHAR(max),

    ErrorMessage VARCHAR(4000))

    DECLARE c Cursor FOR

    select name, definition

    from sys.all_objects

    inner join sys.sql_modules

    on all_objects.object_id = sql_modules.object_id

    where type in ('p','tf','if')

    and name not like 'dt_%'

    and name not like 'sys_%'

    Open C

    FETCH NEXT FROM c INTO @ProcName, @Text

    WHILE @@FETCH_STATUS = 0 BEGIN

    SET @text = REPLACE(@text, @ProcName, @ProcName + 'CreateTest') -- change proc name

    BEGIN TRY

    EXEC(@text) -- try to create the proc

    INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that could be created

    END TRY

    BEGIN CATCH

    INSERT @T values (@ProcName, @text, ERROR_MESSAGE()) -- record procs that couldn't be created

    END CATCH

    if exists

    (select *

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type = 'p')

    begin

    set @sql = 'drop procedure ' +

    (select name

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type = 'p')

    exec(@sql)

    end

    if exists

    (select *

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type in ('if','tf'))

    begin

    set @sql = 'drop function ' +

    (select name

    from sys.all_objects

    where name like '%' + @procname + 'createtest'

    and type in ('if','tf'))

    exec(@sql)

    end

    FETCH NEXT FROM c INTO @ProcName, @Text

    END

    CLOSE c

    DEALLOCATE c

    SELECT * FROM @T where errormessage is not null

    order by procname

    go

    On your dev box, convert the database to Compat 90, then run this script. It should give you what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you GSquared, I had not considered this type of methodology - I believe this will give me exactly what I need with minimal effort on my part. Excellent response lickety split in spite of my "please just answer the question" commentary.

    Much appreciated,

  • You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • FYI: There's a new version of Upgrade Advisor that works with SQL 2000 and SQL 2005. Maybe it would work on version 80 databases in a SQL 2005 instance. The download is here: http://www.microsoft.com/downloads/details.aspx?FamilyId=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en

    Greg

  • Thanks Greg. I already used the script GSquared provided and addressed the objects that needed fixing though. Funny enough there were a few procs that wouldn't create because they were syntactically incorrect on any version. I couldn't figure out how they would have gotten past even 7.0's parser. I couldn't imagine system tables would have been updated to alter the procedures - but who knows - I just fixed em and moved on.

    Anyway, thanks for the URL I'll go ahead and grab that because I will need it eventually (hopefully sooner than later).

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply