Compatibility Level 90

  • Hi guys,

    I have a database in compatibility level 80 and I need to upgrade it to 90.

    But here and there I find a proc that has to be adjusted, otherwise it won't work under level 90.

    I have seen to ways to identify those procs:

    1. I can try to execute the proc to see if it will work

    2. I can Try to recompile the proc to see if it will recompile without problems

    Since I have lots and lots of procs, I am looking for a faster (and easier) way to find the bad procs.

    Is there an utility that can check this for me?

    I know that if I could find a way to recompile all procs in the database, I could find the ones with problem. But sp_recompile won't recompile a proc immediately. It will only force a recompilation next time the proc is executed. (I can't afford errors in production time)

    Thanks in advance,

    Luiz.

  • Ok.

    Since I have got no answer I'm assuming this is a hard one.

    One solution would be to create a test version of the database, convert it to level 90 and then run all procs. This way I would know the procs that must be fixed.

    Is there a way (maybe a script) to run all procs in a database ?

    Thanks a lot,

    Luiz.

  • What about Upgrade Advisor? He should find potential problems.

    MCP ID# 1115468 Ceritified Since 1998
    MCP, MCSE, MCP+I, MCSE+I, MCSA,
    MCDBA SQL7.0 SQL 2000, MCTS SQL 2005

  • As far as I know we can't put database to comp. lev. 90 straightawy after the upgradation. You need to work on all the stored procedures in comp. lev. 90; some Stored procedures might give different result in 80 & 90.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • It's not a guaranteed solution (ie. you can't guarantee it will hit ALL SP's), but you could use profiler to record the activity on the live DB for a period of time, then run that against a level 90 DB...

  • I agree with Berto IF you can put the database in a SQL 2000 instance. Upgrade Advisor does a pretty good job of detecting incompatible usage in stored procedures but it only works on SQL 2000. You can't even connect to a SQL 2005 instance.

    Greg

  • Thank you for all your ideas. They have helped me to understand my options.

    Upgrade Advisor seems a good solution, but it's not possible to downgrade the database to 2000 version. I'll try to find an old backup version and see what Upgrade Advisor says.

    Best regards,

    Luiz.

  • You might try scripting all of your procs to a single file. Include DROPs when you are setting it up. Then put a BEGIN TRANSACTION at the top of the file and a ROLLBACK TRANSACTION at the bottom. Run the script in a query window and ignore any 'cannot create dependency' errors.

    Tom Garth

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Restore a recent copy of your production database, change it to 90, drop and create all stored procedures, views, functions. Save all objects that cause errors and correct for update script.

    While you are at it, look at your foreign keys for use for the new NULL cascade option, my devs like to create circular relationships...

    Sick some power users at this "test" database with your apps.

    This should give you a 90% test and will not take that long.

    Be aware that things are different, so some T-SQL may need work (SQL USER / LOGIN maintenance will be primary fixes, as are SQL Mail) The switch to db_mail is simply to change to the stored procedure that is used.

    Have fun...

    Andy

  • You can't downgrade a DB to SQL 2000, but you can script out all the objects, recreate them in SQL 2000, move some of the data (top 1000 or 5000 rows?) via the Import/Export Wizard and then run the Upgrade Advisor against that.

    Just a thought.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Based on the suggestions that you guys gave, I decided to create a script to test all procs and functions.

    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, it will list the procs that couldn't be created. 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.

    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

  • That's pretty cool. You should do the "Submit a Script" so that one gets into the newsletter. You'll get all sorts of feedback then. @=)

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I'm in the same position of wanting to convert from 80 to 90 ..... Good posts here !

  • Good idea Tarvin. I will.

    Luiz.

Viewing 14 posts - 1 through 14 (of 14 total)

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