SQL Server Performance Forum – Threads Archive

Migration tool from SQL 2K to SQL 2005

Is there any automated tool that would migrate my 2K procedures to 2005 ? I am terribly pained by the idea of re-writing the procedures manually [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!]

Hi ya, You should be able to backup a 2k database and restore it on a 2k5 server, can’t you? That would take all of the stored procedures with it…? Cheers
Twan

The confusion here is probably to do with the option to use other programming languages to implement stored procedures in SQL 2005. It’s an option, and it’s even turned off by default. As far as I know, your T-SQL stored procedures should run just fine. The only really big issue is probably transferring any SQL logins you may be using. Another thing is if any of your procs is directly querying system tables (sysobjects, sysusers, etc.) because that is no longer possible.

The Copy Database Wizard.

Thanks for all your suggestions. I have another specific query about the *= and =* operators that have been revoked in 2005. I would have to change the syntax for all my queries with outer joins (we have about 1000 procedures with outer joins). How do I do this easily since this is not just a find and replace of these operators ?

I think you need to manually change them Madhivanan Failing to plan is Planning to fail

Have you tried generating a script for all the SPs from SQL2K and then apply the same in SQL 2k5? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by rekha Is there any automated tool that would migrate my 2K procedures to 2005 ? I am terribly pained by the idea of re-writing the procedures manually [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!] <hr height="1" noshade id="quote"></blockquote id="quote"> <hr noshade size="1">Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Okay, I learned something new: the old JOIN syntax is no longer supported in SQL 2005. Mind you, it was already obsolete in SQL 6.5, if not earlier than that … and we’re lucky in that we’ve never used that particular bit of syntax. While we’re on the subject, is there a list of T-SQL syntax that is no longer supported? And a list of obsolete syntax that is still supported, but should be replaced with current syntax?

Yes satya, generating the script in 2K and applying the same in 2k5 works. Thanks. The only little problem is that the generated sps no longer look like the ones that we have plus problems with the statements where @@spid has been used and things like that. Anyhow, this saves manual conversion effort! Will keep you posted on how the conversion is going on…..

]]>