Ask the Expert

Changing SQL data source on all workstations

Can you change a SQL data source through Active Directory on all workstations? If not, do you know of a tool that will?

    Requires Free Membership to View

In the case of ODBC, you will have to use a logon script approach. Here is a script that can do the trick:
Dim DataSourceName
 Dim DatabaseName
 Dim Description
 Dim DriverPath
 Dim DriverName
 Dim LastUser
 Dim Regional
 Dim Server

 Const SystemFolder= 1
 Dim fso
 Dim SysFolder
 Dim SysFolderPath

 Set fso = wscript.CreateObject("Scripting.FileSystemObject")
 Set SysFolder =fso.GetSpecialFolder(SystemFolder)
 SysFolderPath= SysFolder.Path

 Set oArg= wscript.Arguments

 if oArg.Count <> 4 then
 call Usage
 end if

 'Pulling Parameters from command line arguments.
 DataSourceName = oArg(0)
 DatabaseName = oArg(1)
 Description = "My ODBC" & DatabaseName
 DriverPath = SysFolderPath & "sqlsrv32.dll"
 Server = oArg(2)
 LastUser = oArg(3)

 DriverName = "SQL Server"

 Set WshShell = WScript.CreateObject("WScript.Shell")

 'Create the key in the registry
 Dim RegEdPath
 RegEdPath= "HKLMSOFTWAREODBCODBC.INI" & DataSourceName & ""
 WshShell.RegWrite RegEdPath , ""

 'Put the values into the registry
 WshShell.RegWrite RegEdPath & "Database" , DatabaseName
 WshShell.RegWrite RegEdPath & "Description" , Description
 WshShell.RegWrite RegEdPath & "Driver" , DriverPath
 WshShell.RegWrite RegEdPath & "LastUser" , LastUser
 WshShell.RegWrite RegEdPath & "Server" , Server
 WshShell.RegWrite "HKLMSOFTWAREODBCODBC.INIODBC Data Sources" & DataSourceName , DriverName


Sub Usage
 wscript.echo "Create a system DSN in ODBC. SQL Server DLL is assumed"
 wscript.echo "Usage:"
 wscript.echo "InstallDSN.vbs <DSNName> <DataBaseName> <SQLServer> <User> "
 wscript.echo ""
 wscript.echo "Example: InstallDSN.vbs MyDSN Pub MySQLServer MyUser"
end sub
Paul Hinsberg

Additional Expert Help:
Be sure to check our Answer FAQ for more expert advice.
For faster answers, visit ITKnowledge Exchange.

This was first published in November 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: