SQL Server Performance

Migration tool from SQL 2K to SQL 2005

Discussion in 'SQL Server 2005 General DBA Questions' started by rekha, Nov 23, 2005.

  1. rekha New Member

    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=':(' />!]
  2. Twan New Member

    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
  3. Adriaan New Member

    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.
  4. mulhall New Member

    The Copy Database Wizard.
  5. rekha New Member

    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 ?
  6. Madhivanan Moderator

    I think you need to manually change them

    Madhivanan

    Failing to plan is Planning to fail
  7. satya Moderator

    Have you tried generating a script for all the SPs from SQL2K and then apply the same in SQL 2k5?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by rekha</i><br /><br />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=':(' />!]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  8. Adriaan New Member

    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?
  9. rekha New Member

    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.....

Share This Page