DSN changes are ubiquitous in any most systems that need to connect directly to a database. Although you can prevent them by aliasing your DB to a CNAME and doing the modification globally, or adding an additional IP address, that’s something you learn “for the next time”. Here’s my script on how to do this easily.
The code:
Option Explicit
const HKEY_LOCAL_MACHINE = &H80000002
Dim DBs, strComputer, StdOut, oReg, Servers, NewServer
' Edit the following lines
DBs = Array("DBName", "AnotherDB", "YetAnotherDB")
Servers = Array("servername", "servername.domain.local", "serveralias")
NewServer = "newserver.domain.local"
strComputer = "."
Set StdOut = WScript.StdOut
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_
strComputer & "\root\default:StdRegProv")
ChangeDSN "SOFTWARE\ODBC\ODBC.INI"
ChangeDSN "SOFTWARE\Wow6432Node\ODBC\ODBC.INI"
Sub ChangeDSN(Root)
Dim arrSubKeys, strDBName, subkey, bMatchDB, DB, bMatchServer, Server, strServerName
oReg.EnumKey HKEY_LOCAL_MACHINE, Root, arrSubKeys
If IsNull(arrSubKeys) Then Exit Sub
For Each subkey In arrSubKeys
'StdOut.WriteLine subkey
' Check if the DB name is in the list of moved names
oReg.GetStringValue HKEY_LOCAL_MACHINE, Root & "\" & subkey, "Database", strDBName
oReg.GetStringValue HKEY_LOCAL_MACHINE, Root & "\" & subkey, "Server", strServerName
'StdOut.WriteLine "DBName is " & strDBName
bMatchDB = False
bMatchServer = False
strDBName = LCase(strDBName)
strServerName = LCase(strServerName)
For Each DB In DBs
If LCase(DB) = strDBName then bMatchDB = True
Next
For Each Server in Servers
If LCase(Server) = strServerName Then bMatchServer = True
Next
If bMatchDB And bMatchServer Then
' Yes, so make dure the DB name is set correctly
StdOut.WriteLine subkey & " - Matched"
oReg.SetStringValue HKEY_LOCAL_MACHINE, Root & "\" & subkey, "Server", NewServer
End If
Next
End Sub
Now, put this on an available share and just execute this on all the workstation using your management software or use psexec to run this on all the machines you need:
C:\Users\dafonso\Desktop> psexec \\* -u domain\user cscript.exe //nologo \\share\ChangeDSN.vbs
That’s it.