Creating multiple ODBC connections

Recently for one of my projects at work I was given a requirement to add over 100 ODBC connections. That my friend is way too many for a lazy person such as myself to add manually, not to mention I had to do it in three different environments. Dev, pre-production and production.

I did a little searching via google as usual and also posted this question on twitter.com/#sqlhelp.

“Is there a way to add multiple ODBC connections? I have over 100 to add and don’t want to do it by hand”.

While waiting on responses to my question I went through the returned search results. I did find some reference to batch files and a way using PowerShell. The post about batch files was not clear to me and did not leave me with a warm and fuzzy. The PowerShell example was very nice (I ❤ Powershell) but the OS is Windows 2003 and would require changing the environment. We do our best to keep all servers the same (dev, preprod, prod) to reduce trouble shooting issues related to configurations.

Noticing a few responses, here is one from Ted Krueger/@onpnt | twitter | blog.

@SQLAJ I’ve used registry changes in the past to handle that. don’t recall another method off the top of my head. #sqlhelp

And another from Joe Webb/@joewebb | twitter | blog

@sqlaj Should be possible with PowerShell. A quick Google search yielded quite a few hits; haven’t done it myself tho. #sqlhelp

And one more from Todd D Nelson/@ToddDNelson | twitter | blog

RT @onpnt: @SQLAJ I’ve used registry changes in the past to handle that. #sqlhelp (Me too – Works well – YMMV)

Using the suggestion of registry changes I started to investigate. These are the steps I took.
NOTE: Editing your registry can be dangerous if done wrong. It is best to backup your registry before making any changes. I also did all of the initial work on the DEV server and not on production or preproduction. I won’t be going into that in this post but you can find information related to that here. Backup your registry

Open up your registry editor. This can be done by going to the Start menu and clicking on Run.

Then type in “regedit”.

This will open your registry editor. Now navigate to the following area.
“HKEY_LOCAL_MACHINE\SOFTWARE\ODBC”
It should look like this.


If you expand the ODBC folder you will find additional folders. The one labeled ODBC Data Sources is where our DSN will eventually be stored. If you have some now they will appear. Currently I have one in there.

(pay no attention to the *mdb reference (waving hand) it is not actually there)

Now go the File menu on the registry editor and click export. This will allow you to export the format and values needed to create your datasources and the connection information. Open the file with Notepad, do not just double click. Doing so will prompt you with the following question.

Since you just exported the file it is not really a big deal but you should click “no” and right click to open with Notepad. Once open it should look similar to this.


We will use this as a template to create the multiple connections.
This above picture is using SQL Server ODBC connection. The ones I had to create were a bit more complicated as you can see by the picture below. I had to use a third party ODBC driver and additional information for connections but the steps will be the same.


The next step is to copy the ODBC connection
(under the branch “[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]”)
like the picture below and paste them back into the document.


After you have pasted them edit the DSN accordingly. Such as connection1, connection2,…

You will do the same for the connection information (under the branch
“[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC File DSN]”) c opy and paste.

Then change the information for each of the connections

All of this, depending on the number of connections, is the tedious part. Since I had to add over 100 connections, it took me several days. Mainly because I support multiple projects and each connection was different.

Now to the cool part of the post. As I mentioned earlier, I did all the initial work on the DEV server. Once I finished adding all the DSNs and the connection information for each of the 100 plus ODBC Connections I confirmed the connections were added correctly. Do this by opening the ODBC connection manager in Administrator Tools and testing the connection. The reason you should do this is because some of the previous steps (cut, paste, edit) are prone to mistakes, especially when done manually. After confirming (or fixing) you then export the specific branch of the registry needed.

NOTE: DO NOT EXPORT THE ENTIRE REGISTRY.

Navigate to this branch and export.
“[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC]”
Open it again (right click) and make sure is has all the DSN and Connection information in the file. If not use the registry editor to find the correct branch so you capture all the required information.
You will then copy this file to the other servers you will need to create these connections on. Once copied to the server. Now you can double click on the file, this time say “Yes” to import the information into the registry.

After import is successful, (and there is no reason is shouldn’t if you followed along) you will see the follow message.

Open the registry editor again and you will see all your connections added.

And the connection information for an individual DSN.

Now rinse, wash and do it again on the additional servers.
I know this saved me many, many hours of work. Hopefully, it will save you some time too.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s