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

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

  • Comments 5
  • Likes
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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Great script, thank you

    Can you tell me how i could run this script against a list of computers?

    thanks,

  • Of course, you can retrieve the list of DSN via some registry functions. However, there is a much simpler method available in Windows 8 (Release Preview version) and Windows Server 2012 (Release Candidate version).

    You can use the Get-OdbcDsn to retrieve the list of all ODBC DSN installed in the system.

    See the blog article for more detail: blogs.msdn.com/.../odbc-dsn-management-in-the-next-release-of-windows-code-named-windows-8-and-windows-server-8.aspx

    Thanks,

    Ming.

    WDAC Team, Microsoft.

    (This post include information about a pre-release windows and is subject to change in future releases.)

  • To retrieve the list of ODBC System DSN, you can use the command:

           Get-OdbcDsn -DsnType System

    To retrieve the list of ODBC User DSN, you can use the command:

           Get-OdbcDsn -DsnType User

    Thanks,

    Ming.

    WDAC Team, Microsoft.

    (This post include information about a pre-release windows and is subject to change in future releases.)

  • Good morning all!

    How can I run the script agains a list of computers and store the results in a text file or Excel file, listing the DSN and the computer names? How about if I want to include the last user who logged into the computer?

    Thank you,

  • Your script does not work: "C:\scripts\systemdsn.vbs(11, 1) Microsoft VBScript runtime error: Type mismatch:
    'Ubound'
    Either you forgot a 'dim' statement or... something?