Technical Article

Testing all procs before upgrading to Compatibility Level 90

,

If you use SQL Server 2005 but your database is still in Compatibility Level 80 (SQL Server 2000), you can use this script to test if any procs or functions won't work when you upgrade the database.

The script is quite simple. All that it does is to try to recreate a proc with a different name (Temp_TestProc_DeleteMeTemp_TestProc_DeleteMe). If something goes wrong, the script will record the proc name, the code used to create the proc, and the error message. After trying this with all procs in the database, the procs that couldn't be created will be listed. Those will be the ones that must be fixed before upgrading the database to compatibility level 90.

A word of caution:

1. If the problem is inside of a dynamic SQL code, it won't be detected.

2. This script is intended to find incompatibility only in procs and functions. You still have to check codes used to create triggers and views.

Luiz Barros.

DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)

DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))

DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BY O.Name, C.colid

Open C
FETCH NEXT FROM c INTO @ProcName, @Text 
SET @sql=@Text
SET @ProcName1=@ProcName

WHILE @@FETCH_STATUS = 0 BEGIN

FETCH NEXT FROM c INTO @ProcName, @Text 
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text 
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')

BEGIN TRY
EXEC(@sql) -- try to create the proc
END TRY

BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH

print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName

END
END

CLOSE c
DEALLOCATE c

IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
SELECT * FROM @T

Rate

4.87 (15)

You rated this post out of 5. Change rating

Share

Share

Rate

4.87 (15)

You rated this post out of 5. Change rating