Posted by Erland Sommarskog on 06/04/05 23:17

[posted and mailed, please reply in news]

Matt (john.bandettini@rbs.co.uk) writes:
> I have SQL Server versions 6.5 and 2000 installed on a Windows 2000
> server.
> I installed 6.5 first, coppied a backup of a database to the server and
> restored it. I then installed 2000. I am trying to use the upgrade
> wizzard.
>
> On the first screen I keep the default settings, export and import
> objects and data, use a named pipe. Its all on the same server, give it
> the sa password for both installations (its the same). All services are
> running under local system account.
>
> It switches between the two versions a couple of times then comes up
> with this error message:
>
> Couldn't create or register the SQLUpgrade DSN
>
> Anybody got any idea what it means?

Never seen the message. It seems that the upgrade wizard tries to
create an ODBC data source, but fails for some reason. Permissions?
I've no idea.

Just like "Joe Cool", I have never run the upgrade wizard. We carried out
all our 6.5/2000 migrations on our own. And, really, it's not that
difficult at all.

1) If you don't have scripts for your databases under version control,
script them out through Enterpise Manager.

2) Run the scripts on SQL 2000, and fix any problems.

3) For each database, run this:
SELECT bcp ' + db_name() + '..' + name + ' out ' + name + '.bcp ' +
'-S server -U sa -P xxxx -b 1000 -c -t@!@ -r"\n<->\n" ' +
CASE WHEN EXISTS (SELECT *
FROM syscolumns c
WHERE c.id = o.id
AND c.status & 0x80 <> 0)
THEN ' -E'
ELSE ''
END
FROM sysobjects o
WHERE type = 'U'
ORDER BY o.name

4) Save the output in a .BAT file, and run the file from the SQL 2000
box.

5) Edit the file, to change "out" to "in" and change the server name
and authentication information.

6) Run the file again.

At this point all data has been migrated. Logins and users are left to
do, but that is not to tricky to find out.

The advantage of this method, over any wizard, is that you have control
over what is going on, and you are not stumped by things that the wizard
does behind your back.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]