upgrading sql2000 to 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

upgrading sql2000 to 2005

hi,
my company wants to upgrade sql2000 to 2005 and they asked me to make some changes in SP’s can someone gudie me what are the tips and steps to upgrade a SP in 2005(like any keywords or changes in Order By clause).Please help me
I’m sure your 2000 SP will work in 2005 without any changes.
In 2005, error handling has changed and many new clauses like output clause and new set based operations has been added like except,intersect.
So you could try altering your SP for optimisation
The best is to check with Upgrade advisor for the databases you want to upgrade, also chekc the changes on the compatibility issues using SQL server books online. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
hi,
thanks for repling me but the thing is that some SP’s has to change, for better perfomance like a SP in 2000 using Nolock should use WITH Nolock in 2005, a Order By clause has b change if u find any plz reply me
It is hard to say how best you can achieve the good performance when upgraded without looking at the code, I think it is not possible to post all of them here. As referred earlier many system functions and stored procedures supported in earlier versions of SQL Server will not work. Books Online for SQL Server 2005 has an index of all backward-compatibility issues, including database engine changes and deprecated commands. Commands that were marked as deprecated prior to SQL Server 2000 should not have been used in SQL Server 2000 anyway. The best option is to test the upgrade on the dev. environment and see how your application is controlled or managed after the upgrade, fyi look at this add-inhttp://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc. Using NOLOCK is deprecated in SQL 2005http://msdn2.microsoft.com/en-us/library/ms143729.aspx. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
SQL Server 2005 Books Online
Backward Compatibility The following LINK contain backward compatibility information for Microsoft SQL Server components. This content includes information about deprecated features, discontinued features, breaking changes, and behavior changes. http://msdn2.microsoft.com/en-us/library/ms143232.aspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

]]>