ODBC Attached Databases--Is there an easy way to update them?

[Brent White]

I am pondering moving SQL Server operations from an old SQL 7 Database
server that is likely to need some costly repairs in a year's time
(plus, we're possibly moving, anyway) to another newer server running
SQL 2000 (for now, maybe 2005 in the future). The front ends are all
Microsoft Access 2000 databases.

A lot of my database files depend on this server for lookups and I use
ODBC to run pass-through queries and read/update server tables.

Is there an easy way to mass update the tables to point to a new
server? Seemed like the last time I tried just changing the DSN, the
tables still pointed at the old server.

I have thought about redirecting the DNS entry in our Domain
Controller to the new server's IP address, but the old server is also
an FTP server, so I gotta tread carefully.

Can someone give me some guidance? If this is the wrong group, please
enlighten me and I'll post it in the right group.

 

[Tony Toews [MVP]]

I am pondering moving SQL Server operations from an old SQL 7 Database
server that is likely to need some costly repairs in a year's time
(plus, we're possibly moving, anyway) to another newer server running
SQL 2000 (for now, maybe 2005 in the future). The front ends are all
Microsoft Access 2000 databases.

A lot of my database files depend on this server for lookups and I use
ODBC to run pass-through queries and read/update server tables.

Is there an easy way to mass update the tables to point to a new
server? Seemed like the last time I tried just changing the DSN, the
tables still pointed at the old server.

Go DSN less. Then you don't have to configure the DSN on everyone's
system.

I much prefer DSN-Less connections as it is one less thing for someone
to have to configure and one less thing for the users to screw up.
This is also better for Citrix/TS farms where each individual system
would have to have a DSN created and maintained.

Using DSN-Less Connections
http://www.accessmvp.com/djsteele/DSNLessLinks.html
ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafast/GetFile.aspx?file=ODBCTutor01.htm&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm

I have thought about redirecting the DNS entry in our Domain
Controller to the new server's IP address, but the old server is also
an FTP server, so I gotta tread carefully.

However they would be totally different port numbers FTP is port 21
whereas SQL Server is, umm, 439 or something like that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Brent White]

Sorry for the delay in getting back to you. That worked great. I'm
going to try it for a week before shutting off the old SQL Server.

Now, I have another question. Now I have the specter of 2 servers, a
production server running SQL 2000 and a newer server running SQL
2005. This time, the new server is going to take over the old
server. Someone told me I could just rename the new server with the
name of the old one after giving the old one a different name. How
does that affect DSN-less and ODBC Connections? I don't have to do
this yet, but I would like to be able to rename the new server with
the old server's name so that we can keep the URL's for the Java
server the same, for as little disruption as possble. If I were to
run your code on our databases, it would take weeks to do.

Can I simply rename machines in this instance?