Hi,

I need to change over 500 client ODBC connections as I am migrating a SQL 2000 database to a cluster and microsoft doesn't support renaming SQL clusters.

I have a vbscript (below) which allows me to setup the appropriate ODBC connection but it doesn't allow me to specifiy using mixed mode sql authentication and subsequently a password for that account (though it does insert the username).

Is there any way to do this?! If not is there a better way to automate mutiple odbc changes?

If I can't figure out a way to do this I'm going to have to go round to every client manually as this migration has to happen in a week!

Any help greatfully appriciated!

Scrpit:

Option Explicit
'Constants
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const HKEY_CURRENT_CONFIG = &H80000005

'Variables
On Error Resume Next
Dim DataSourceName
Dim DatabaseName
Dim Description
Dim DriverPath
Dim LastUser
Dim Server
Dim Trusted_connection
Dim DriverName
Dim InputFile
Dim iFSO
Dim ifile
Dim sComputer
Dim sPath

'Value assignment

DataSourceName = "ODBCname"
DatabaseName = "Database"
DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
LastUser = "username"
Server = "servername"
Trusted_connection = "Yes"
Description = "Connection_description"
DriverName = "SQL Server"
InputFile = "c:\pclist.txt"
Set iFSO = CreateObject("Scripting.FilesyStemObject")
Set ifile = iFSO.OpenTextFile(InputFile)
sPath = "SOFTWARE\ODBC\ODBC.INI\" & DataSourceName

'Read and loop through the input file
Do Until ifile.AtEndOfLine
sComputer = ifile.ReadLine
If (0 = CreateRegKey(sComputer, HKEY_LOCAL_MACHINE, sPath)) Then
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Database", DatabaseName
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Description", Description
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Driver", DriverPath
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "LastUser", LastUser
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Server", Server
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, sPath, "Trusted_Connection", Trusted_connection
Else
Exit Do
End If

'Write in "ODBC Data Sources" Key to allow ODBC Manager list & manage the new DSN
SetRegKeyStrValue sComputer, HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", DataSourceName, DriverName
MsgBox (sComputer & " DONE!")
Loop
ifile.Close
Set ifile = Nothing


'Create RegKey Function

Function CreateRegKey(sComputer, hTree, sKey)
Dim oRegistry
Dim lResult
Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonat e}//" & sComputer & "/root/defaulttdRegProv")
lResult = oRegistry.CreateKey(hTree, sPath)
If (lResult = 0) And (Err.Number = 0) Then
CreateRegKey = 0
Else
CreateRegKey = 1
MsgBox ("Create Key " & sKey & " Failed")
End If
Set oRegistry = Nothing
End Function

'set RegKey Function

Function SetRegKeyStrValue(sComputer, hTree, sKey, sValueName, sValue)
Dim oRegistry
Dim lResult
Set oRegistry = GetObject("winmgmts:{impersonationLevel=impersonat e}//" & sComputer & "/root/defaulttdRegProv")
lResult = oRegistry.SetStringValue(hTree, sKey, sValueName, sValue)
If (lResult = 0) And (Err.Number = 0) Then
SetRegKeyStrValue = 0
Else
SetRegKeyStrValue = 1
MsgBox ("Set Value for " & sKey & " Failed")
End If
Set oRegistry = Nothing
End Function