July 18, 2008 at 1:33 pm
Does changing the compatibility level to 90 make permanent changes to the database, or would any changes be reversed when/if the CL were changed back to 80? If permanent, is there a list somewhere of the changes that I could use to compare both versions to see what needs to be tested?
We have a purchased application that came with a SQL database. The original installation was to SQL2000. We have since migrated the database to SQL2005, with compatibility level 80 (SQL Server 2000).
IIRC, we simply detached/reattached the database to migrate it, but I could be wrong. I can dig up my notes on that if needed.
I'd like to change this to CL 90 (SQL Server 2005) for some back-end reporting I'm doing.
I've asked the vendor, who gave their standard answer of (more or less) "it should work, but test it because it's not our problem if it breaks".
I can make a test copy of the database and ask the users to test it. However, I'm afraid that we'd still miss something (there's lots of functionality that is only used once in while).
If the changes aren't permanent, then I could switch it back if we ran into any problems (ideal). If they are permanent, it would help a great deal if I knew where to focus the testing.
Thanks much for any info.
July 18, 2008 at 1:52 pm
July 18, 2008 at 2:18 pm
Create a dev/test copy of your database, change the compatibility level to 90, then run this on it:
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
That will handle the main maintenance tasks you need to do, which includes updating all table statistics. It will also test each proc and function in the database to make sure it can compile under 2005. There are some things in 2000 that won't compile in 2005, and this will give you a list of any you have. It's all easy stuff to fix, most of the time, but it's important to know.
That will allow you to create a script to make any needed changes.
Once you change to 90, you cannot change back to 80. You can restore a backup made before the compatibility change, and that will be 80 (of course), but that means any data changes made, code changes, etc., that were after that backup, will be lost.
I've upgraded a number of databases, and with that script, it's pretty easy and quite fast. Couple of hours for the largest one I did (and that thing was a mess written by people who should never be allowed near T-SQL).
- 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
July 18, 2008 at 2:31 pm
Bijal Parekh, thanks for the link. I did a search for old threads, I swear!
GSquared - tremendous! That's exactly what I need. I'd read that procs could be a problem but hadn't been able to find details of what to check.
Thanks very much!
July 18, 2008 at 2:36 pm
hi grasshopper,
I am still new to SQL 2005 but I try and help as much as i can. It was just an attempt to help you.. 🙂
July 18, 2008 at 3:50 pm
Oh, I appreciate the help -- I just felt silly that I hadn't already found that thread. 🙂
Thanks!
July 18, 2008 at 4:33 pm
Some time ago I had the same challenge and wrote a script for this.
Take a look at http://www.sqlservercentral.com/scripts/Compatibility/62093/
Note that If the problem is inside of a dynamic SQL code, it won't be detected.
Good luck,
Luiz.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply