January 18, 2008 at 10:32 am
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.
January 21, 2008 at 10:55 am
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.
January 22, 2008 at 2:02 am
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
January 22, 2008 at 3:31 am
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."
January 22, 2008 at 4:08 am
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...
January 22, 2008 at 9:16 am
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
January 23, 2008 at 8:26 am
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.
January 23, 2008 at 12:53 pm
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
January 23, 2008 at 11:17 pm
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
January 24, 2008 at 4:24 am
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.
January 28, 2008 at 10:49 am
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@sqlVARCHAR(max),
@TextVARCHAR(max),
@ProcNameVARCHAR(200),
@ProcName1VARCHAR(200)
DECLARE @T TABLE (ProcNameVARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))
DECLAREc Cursor FOR
SELECTO.Name, C.Text
FROMsysobjects O
JOIN syscomments C ON o.ID=C.ID
WHEREO.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BYO.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
January 28, 2008 at 11:05 am
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. @=)
January 28, 2008 at 12:57 pm
I'm in the same position of wanting to convert from 80 to 90 ..... Good posts here !
January 29, 2008 at 8:56 am
Good idea Tarvin. I will.
Luiz.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply