{"id":69323,"date":"2005-07-25T15:55:00","date_gmt":"2005-07-25T15:55:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2005\/07\/25\/how-can-i-retrieve-a-list-of-the-system-dsns-on-a-computer\/"},"modified":"2005-07-25T15:55:00","modified_gmt":"2005-07-25T15:55:00","slug":"how-can-i-retrieve-a-list-of-the-system-dsns-on-a-computer","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-retrieve-a-list-of-the-system-dsns-on-a-computer\/","title":{"rendered":"How Can I Retrieve a List of the System DSNs on a Computer?"},"content":{"rendered":"

\"Hey, \n

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

— RT<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, RT. You know, there\u2019s 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\u2019t know if that\u2019s true or not, but we know about an eerily-similar scripting corollary that is<\/I> true: questions about ODBC Data Sources always come in pairs.<\/P>\n

Scoff if you will, but you can\u2019t argue with the facts: two weeks ago we answered a question<\/B><\/A> 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! <\/P>\n

Listen, don\u2019t feel bad: that is<\/I> spooky.<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Yes, we know: you thought this thing about System DSNs was just another urban legend, akin to the old scare story<\/B><\/A> about people eating Pop Rocks, drinking a pop, and then having their stomachs explode. Better think again, huh?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

If you have no idea what we\u2019re 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<\/B> dialog box and looking on the System DSN<\/B> tab:<\/P>\"System \n


That\u2019s fine if you\u2019re working on the local machine. But what if you\u2019re interested in retrieving a list of the System DSNs on a remote machine, or what if you\u2019d like to inventory the System DSNs on a whole bunch of computers? How do you do something like that?<\/P>\n

Why, you use a script, of course:<\/P>

Const HKEY_LOCAL_MACHINE = &H80000002<\/p>\n
strComputer = “.”<\/p>\n
Set objRegistry = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\default:StdRegProv”)<\/p>\n
strKeyPath = “SOFTWARE\\ODBC\\ODBC.INI\\ODBC DATA SOURCES”<\/p>\n
objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes<\/p>\n
For i = 0 to Ubound(arrValueNames)\n    strValueName = arrValueNames(i)\n    objRegistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue    \n    Wscript.Echo strValueName & ” — ” & strValue\nNext\n<\/PRE>\n
For some reason there\u2019s no WMI class or other COM object designed to retrieve System DSNs. But that\u2019s 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\u2019s 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\u2019ll return and echo both the name and the value, thus replicating the information found in the dialog box.<\/P>\n
Our script begins by defining a constant named HKEY_LOCAL_MACHINE and setting the value to &H80000002; we\u2019ll use this constant to indicate the registry hive we want to work with. We then bind to the WMI service, connecting to the StdRegProv<\/B> class. (Which, as we always hasten to add, is found in the root\\default<\/B> namespace, not root\\cimv2. In fact, this was the subject of our first column<\/B><\/A> ever.) <\/P>\n
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<\/B> method and return a list of all the registry values stored in HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI\\ODBC DATA SOURCES:<\/P>
objRegistry.EnumValues HKEY_LOCAL_MACHINE, strKeyPath, arrValueNames, arrValueTypes\n<\/PRE>\n
As you can see, we pass EnumValues four parameters. The first two – HKEY_LOCAL_MACHINE and strKeyPath – are \u201cin parameters\u201d that represent the registry hive and registry path. The second two – arrValueNames and arrValueTypes – are \u201cout parameters;\u201d 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.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. In this particular script we won\u2019t actually use the data types; that\u2019s because all the values will be string values of type REG_SZ.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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\u2019d 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<\/I> 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<\/B> method to return the value assigned to that registry entry:<\/P>
objRegistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue\n<\/PRE>\n
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\u2019s left is to display that information onscreen:<\/P>
Wscript.Echo strValueName & ” — ” & strValue\n<\/PRE>\n
When we run the script we should get back information similar to this (depending on the DSNs available on the computer):<\/P>
Northwind — SQL Server\nScripting Content — SQL Server\nEvents — Microsoft Access Driver (*.mdb)\n<\/PRE>\n
Cool, huh? Bear in mind, though, that you should never run this script while eating Pop Rocks. No sense taking any chances, right?<\/P>\n\n<\/THEAD>\n\n\n
\n
P.S<\/B>. No need to ask: now you want to know if it\u2019s possible to create and delete System DSNs using a script. Well, for once we\u2019re way ahead of you<\/B><\/A>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I retrieve a list of the System DSNs on a computer?— RT Hey, RT. You know, there\u2019s 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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,31,26,3,5],"class_list":["post-69323","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-operating-system","tag-registry","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I retrieve a list of the System DSNs on a computer?— RT Hey, RT. You know, there\u2019s 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=69323"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/69323\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=69323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=69323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=69323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}