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

    Login

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

wscript.Quit

'--------------------
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"
 wscript.Quit
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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top