July 25th, 2005

How Can I Retrieve a List of the System DSNs on a Computer?

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I retrieve a list of the System DSNs on a computer?

— RT

SpacerHey, Scripting Guy! AnswerScript Center

Hey, RT. You know, there’s an old Hollywood superstition that suggests that famous movie stars always die in threes: if a famous star dies today, then according to legend two more famous stars are doomed to die in the next week as well. We don’t know if that’s true or not, but we know about an eerily-similar scripting corollary that is true: questions about ODBC Data Sources always come in pairs.

Scoff if you will, but you can’t argue with the facts: two weeks ago we answered a question about retrieving the set of ODBC drivers installed on a computer. And now, out of the blue, we get a question about retrieving System DSNs!

Listen, don’t feel bad: that is spooky.

Note. Yes, we know: you thought this thing about System DSNs was just another urban legend, akin to the old scare story about people eating Pop Rocks, drinking a pop, and then having their stomachs explode. Better think again, huh?

If you have no idea what we’re talking about (something which seems to occur more and more often with this column) System DSNs are simply a shortcut method for connecting to databases and other data sources. You can view a list of the System DSNs available on a computer by bringing up the ODBC Data Source Administrator dialog box and looking on the System DSN tab:

System DSNs


That’s fine if you’re working on the local machine. But what if you’re interested in retrieving a list of the System DSNs on a remote machine, or what if you’d like to inventory the System DSNs on a whole bunch of computers? How do you do something like that?

Why, you use a script, of course:

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = “.”

Set objRegistry = GetObject(“winmgmts:\\” & strComputer & “\root\default:StdRegProv”)

strKeyPath = “SOFTWARE\ODBC\ODBC.INI\ODBC DATA SOURCES”

objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes

For i = 0 to Ubound(arrValueNames) strValueName = arrValueNames(i) objRegistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue Wscript.Echo strValueName & ” — ” & strValue Next

For some reason there’s no WMI class or other COM object designed to retrieve System DSNs. But that’s OK: because this information is stored in the registry we can still write a script to grab and return the DSNs. As you might expect, that’s exactly what the preceding script does: it opens the registry, zips down to HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC DATA SOURCES, and then returns the names and values of the all the registry entries found there. Each entry will consist of a name (representing the DSN name) and a value (representing the DSN driver). We’ll return and echo both the name and the value, thus replicating the information found in the dialog box.

Our script begins by defining a constant named HKEY_LOCAL_MACHINE and setting the value to &H80000002; we’ll use this constant to indicate the registry hive we want to work with. We then bind to the WMI service, connecting to the StdRegProv class. (Which, as we always hasten to add, is found in the root\default namespace, not root\cimv2. In fact, this was the subject of our first column ever.)

Following that, we assign the registry path within HKEY_LOCAL_MACHINE to a variable named strKeyPath. With that done we can then use this line of code to call the EnumValues method and return a list of all the registry values stored in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC DATA SOURCES:

objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes

As you can see, we pass EnumValues four parameters. The first two – HKEY_LOCAL_MACHINE and strKeyPath – are “in parameters” that represent the registry hive and registry path. The second two – arrValueNames and arrValueTypes – are “out parameters;” that means they represent information that the EnumValues method returns to us. After EnumValues runs, arrValueNames will be populated with the names of all the registry values found in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC DATA SOURCES; arrValueTypes, meanwhile, will be populated with the registry data type for all those values.

Note. In this particular script we won’t actually use the data types; that’s because all the values will be string values of type REG_SZ.

At this point in time we have the name of each registry entry; if all we wanted to do was echo back the name we’d practically be done. However, we also wanted to echo back the value (that is, the driver name) for each DSN. To do that, we need to connect to each individual registry entry and return the value. And to do that we need to set up a For Next loop that walks through the array of registry entries. For each item in that array we assign the DSN name to a variable named strValueName. We then call the GetStringValue method to return the value assigned to that registry entry:

objRegistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue

In this script, strValue is an out parameter that contains the registry value. We now have the DSN name in one variable (strValueName) and the DSN driver in another variable (strValue). All that’s left is to display that information onscreen:

Wscript.Echo strValueName & ” — ” & strValue

When we run the script we should get back information similar to this (depending on the DSNs available on the computer):

Northwind — SQL Server
Scripting Content — SQL Server
Events — Microsoft Access Driver (*.mdb)

Cool, huh? Bear in mind, though, that you should never run this script while eating Pop Rocks. No sense taking any chances, right?

P.S. No need to ask: now you want to know if it’s possible to create and delete System DSNs using a script. Well, for once we’re way ahead of you.

Author

0 comments

Discussion are closed.