command line for setting up ODBC DSN ? 1

[JasonXie]

Hello All,

I am running NT4.0 Server and Win2000 Server.
I know how to use odbcad32.exe (GUI) to add DSNs one by one.
It is really boring if you are going to add many many DSNs, isn't it ?
I am wondering whether there is a way to use command lines to do that ,i.e., creat a batch file to add many DSNs ?

Many thanks in advance.

Jason

 

[tlbroadbent]

The method I use requires updating the registry. Make sure you have a good registry backup before proceeding.

Export the ODBC entries for a DSN from the registry to a .reg file. Use regedit.exe to export. Look for the keys under HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI for system DSN's. Look in HKEY_CURRENT_USER\Software\ODBC\ODBC.INI for user DSN's. My example uses system DSN's.

Load the .reg file into an editor and duplicate the entries, changing to match the additional DSN's.

-------------------------

Example: The following is the listing of the ODBC entries for a system DSN. You can see the keys and values.

REGEDIT4

[HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources]
"Tek Sample"="SQL Server"

[HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Tek Sample]
"Driver"="C:\\WINDOWS\\SYSTEM\\SQLSRV32.dll"
"Description"="DSN for Tek-Tips Sample"
"Database"="SampleDB"
"Server"="SQLServer1"
"LastUser"="Terry"

-------------------------

After duplicating the entries and changing keys and values for additional DSN's, the file contents would look like the following.

REGEDIT4

[HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources]
"Tek Sample"="SQL Server"
"Accounting"="SQL Server"
"Payroll"="SQL Server"

[HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Tek Sample]
"Driver"="C:\\WINDOWS\\SYSTEM\\SQLSRV32.dll"
"Description"="DSN for Tek-Tips Sample"
"Database"="SampleDB"
"Server"="SQLServer1"
"LastUser"="Terry"

[HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Accounting]
"Driver"="C:\\WINDOWS\\SYSTEM\\SQLSRV32.dll"
"Description"="DSN for Tek-Tips Sample 2"
"Database"="AccountingDB"
"Server"="SQLServer3"
"LastUser"="sa"

[HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Payroll]
"Driver"="C:\\WINDOWS\\SYSTEM\\SQLSRV32.dll"
"Description"="DSN for Tek-Tips Sample 3"
"Database"="PayrollDB"
"Server"="SQLServer1"
"LastUser"="sa"

-------------------------

After competing the updates, save the file with a .reg extension. Open explorer and double click the .reg file and the entries should be added to the Windows Registry. You can also import the file using Regedit. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.