August 15, 2008 at 7:26 am
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
August 15, 2008 at 7:30 am
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
August 15, 2008 at 7:33 am
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
August 15, 2008 at 7:56 am
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,
August 15, 2008 at 1:21 pm
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
August 18, 2008 at 4:55 pm
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
August 18, 2008 at 5:23 pm
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