Q

Changing SQL data source on all workstations

Can you change a SQL data source through Active Directory 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?
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

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchServerVirtualization

SearchCloudComputing

SearchExchange

SearchSQLServer

SearchWinIT

SearchEnterpriseDesktop

SearchVirtualDesktop

Close