[posted and mailed, please reply in news]
Bungle (da**@court-technologies.com) writes:
Does anyone know or has anyone heard of any migration tools which
are/might be available for converting SQL Server 2000 databases to SQL
Server 2005 Express?
I'm guessing it is probably a bit early but it was worth a shot!
Well, I know of one, which is currently in beta. It's called SQL Server
2005.
You can simply attach your SQL 2000 databases to SQL 2005, and they
will be converted to SQL 2005. The same if you restore a backup from
SQL 2005. They will be left in compatibility mode 80, though.
Will the code run right away on SQL 2005? Well, it depends a little.
If you want to move on to compatibility level 90, there are a few
things to check:
o If you use index/lock hints, they must now be preceeded by WITH:
SELECT * FROM tbl WITH (INDEX = pk_tbl)
There is an exception for one-word hints, though.
o Old-style outer-joins *= may not work. (They do in the current
beta, but I think I saw something that it will change. They are
an abomination, so change them anyway.)
o There might be more, this is what I recall here and now.
Even if you stay on level 80, there are issues to look out for:
o SQL 2000 permitted for some incorrect syntax:
SELECT col FROM tbl ORDER BY crap.col
That is, in ORDER BY you could use non-existing aliases. There
were also some other funny things, that SQL 2005 now traps.
o If you make direct access to metadata in stored procedures
executed by plain users - system tables or INFORMATION_SCHEMA
does not matter - beware that users now by default does not
have access to metadata, if they don't have access to the
object itself.
And then of course, there is always the risk that a query executed
fine, now suddenly takes very long time, because the optimizer finds
another plan.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
