Is there a way to use a script to compare the system DSNs on a machine to SQL databases on an instance?

Question

I'm looking for a way to possible have a script get all system DSNs from a server as well as get all SQL databases on an instance and compare the two to make sure that all DSNs are installed on this server. I have a script to grab the DSNs and I have a separate script to get the SQL DB's.

DSN script:

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)
    If LEFT(strValueName, 2) = "SB" Then
        objRegistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue    
        strDSN = strDSN & strValueName & VBCRLF 
    End If
Next

wscript.echo strDSN

SQL DB script:

dim sServer, sConn, oConn, colDatabases, sDatabaseName, sUser, sPassword, strQry, run, rslt

sDatabaseName="Master"
sServer="."
sUser="."
sPassword="."
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=" & sDatabaseName
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, sUser, sPassword

strQry = "select name from sys.databases where name like 'sb%'"
set run = oConn.execute(strQry)
run.MoveFirst
Do Until run.EOF
rslt = rslt & "Database: " & run("name") & VBCRLF 
run.MoveNext
Loop 
MsgBox rslt
oConn.Close

I can run these two separately and compare them myself but I'd like to be able to consolidate them and have the script compare them and give feedback on what may be missing.

score 1 · Accepted Answer · edited May 23 '17 at 12:05

You need to collect your DSNs (names) and your Databases (names) into two Arrays, ArrayLists, or Dictionaries. Then you can use standard Set operations (union, intersection, difference, complement) to identify the interesting (sub)sets, e.g. DSNs with no corresponding database, databases with no corresponding DSN, the 'good' pairs, ...).

Just two lines are needed to add a Dictionary to your first script:

Set dicODBC = CreateObject("Scripting.Dictionary") ' <<<<<<<<
For i = 0 to Ubound(arrValueNames)
    strValueName = arrValueNames(i)
    If LEFT(strValueName, 2) = "SB" Then
        objRegistry.GetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue
        strDSN = strDSN & strValueName & VBCRLF
        dicODBC(strValueName) = 0 ' <<<<<<<<
    End If
Next

You surely can apply this strategy to your second script and combine both programs into one that ends with two collections (Dictionaries) dicODBC and dicDB (make sure that its keys don't include the "Database: " prefix).

Now look at this script to see how to compare the arrays obtained via dicX.Keys().

This doesn't seem to be adding the items to the dictionary. When I test it and show the items in the dictionary all I can return is "0" for each item. — BSanders, Sep 09 '14 at 14:37

Is there a way to use a script to compare the system DSNs on a machine to SQL databases on an instance?

1 Answers1