Windows Forum

Ask something
Discussion Locked

Automatically create ODBC DSn

0 helpful
6 replies

by pete.zerger

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Does anyone know of a way to automatically create SQL and / or Access System DSNs via a script, some command line util, etc?

6 replies

by beermonster

Hi
Depending on what you want to achieve, you can use a file DSN. Go to the ODBC applet, and select 'file dsn'. Configure the dsn with all of the settings you require, following the wizard thru to the end. Now go to program files\common\odbc\datasources and view the dsn you have created. to recreate it via a script, simply echo each line to a file with a dsn extension in the datasources folder, making any changes as required (IE changing the db name etc). When you access the odbc applet, you'll see your new dsn shown there. Now use your file dsn to access the DB or MDB file. Hope this helps.

by joseph moore

Part 1
Yes. You can do this with a Registry file (.REG) It is a little complicated, but possible. For this example, I am gonna connect to a locally-run SQL database called Joe.

Go to the system with the DSN already made. Now, open up REGEDIT, and navigate to the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources
This is where the names of the System DSNs are kept (when you open ODBC in Control Panel, and click the System DSN tab, you see the info under this key). If you want to add a new SQL System DSN, then to this key, add a single line like this:
"Joe"="SQL Server"

Ok, now for the rest. This just sets up the name for it. Now, let's set up the actual DSN connection info. You need to make a new key folderand call it the same thing as what you just entered in the previous key. What I mean is, make the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Joe
And put in the following info:
"Driver"="sqlsrv32.dll"
"Description"="Test System DSN Connection to local database Joe"
"Server"="(local)"
"Database"="Joe"

And that is it. You should now have a new System DSN.

by joseph moore

Part 2
To make distribution of this easier, make a Registry file. Call it MyDSN.REG and give it all of the data from this example:

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"Joe"="SQL Server"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Joe]
"Driver"="sqlsrv32.dll"
"Description"="Test System DSN Connection to local database Joe"
"Server"="(local)"
"Database"="Joe"

Now, to do this for a DSN to an Access database, just follow the same steps, but there are a few more items for Access. There are a couple more subkeys you need to make.

It will be easiest, like I said before, to find a system that is already manually configured, and export all the DSN info you need from HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources and HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\"DSN_name_you_want_to_copy"
plus any subfolders it has.

Now, you will have to make 2 REG files, but you can combine them using Notepad. That is easy!

You can distribute the REG file in a logon script by doing a CALL regfilename.REG.

Or just walk it to each machine that needs it and double-click it.

E-mail me if you have problems.
Hope this helps.

This discussion is closed to new replies.