SQL - How To Check Multiple Databases For Column?

[buymeapc]

Hi all,

So, I have a script that will scan through all my SQL databases that are running and check to see if their name matches the name I'm looking for and, if it finds a match, it will check a column's value and return it.

The script works fine, but it's so incredibly slow since there are only 2 out of 26 in my list that are matched. Is there a faster way to go about this?

I've attached the _SQL.au3 file I'm using, for easier testing 

Thanks for the help!

expandcollapse popup

#include <_SQL.au3>
#include <Array.au3>

Dim $sNRM_SQLUser = "username"
Dim $sNRM_SQLPass = "password"
Dim $sNRM_SQLHost = "(local)"
Dim $bNRM_SQLAuth = True
Global $sNRM_DatabaseName = "NRM"

Dim $iDatabases = NRM_ScanDatabases($sNRM_SQLUser, $sNRM_SQLPass, $sNRM_SQLHost, $bNRM_SQLAuth)
MsgBox(0, "", "Count: " & $iDatabases)

Func NRM_ScanDatabases($sUser, $sPass, $sHost, $bSQLAuth)
    ; If the SQL connection succeeded, then get the DB count
    Local $iCount = 0
    Local $aDatabaseList = _SQL_GetDatabaseArray($sHost, $sUser, $sPass, $bSQLAuth)

    If IsArray($aDatabaseList) Then
        For $i = 1 To UBound($aDatabaseList) - 1
            SplashTextOn("", "Querying database " & $i & " of " & (UBound($aDatabaseList) - 1), 300, 45, Default, Default, $DLG_NOTITLE)
            ConsoleWrite("--> Checking: " & $aDatabaseList[$i][0]  & @CRLF)
            If StringInStr($aDatabaseList[$i][0], $sNRM_DatabaseName) > 0 Then
                Local $iNRM_DBVersion = _SQL_GetNRMVersion($sHost, $sUser, $sPass, $bSQLAuth, $aDatabaseList[$i][0])
                If $iNRM_DBVersion <> -1 Then
$iCount += 1
ConsoleWrite($aDatabaseList[$i][0] & @CRLF)
EndIf
            EndIf
            SplashOff()
        Next
    EndIf
    Return $iCount
EndFunc

Func _SQL_GetDatabaseArray($sServer, $sUser, $sPass, $bSQLAuth = True)
    Local $oADODB
    Local $aDatabases
    Local $iRows, $iColumns
    
    ; Register the error handler to prevent a hard crash on COM errors
    _SQL_RegisterErrorHandler()
    
    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR Then
        Msgbox(16, "Error", _SQL_GetErrMsg())
        Return -1
    EndIf
    
    _SQL_ConnectionTimeout(-1, 5)
    If $bSQLAuth Then
        $oADODB.ConnectionString = 'Provider=SQLOLEDB.1;Password=' & $sPass & ';Persist Security Info=False;User ID=' & $sUser & ';Initial Catalog="";Data Source=' & $sServer
    Else
        $oADODB.ConnectionString = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog="";Data Source=' & $sServer
    EndIf
    $oADODB.Open
    
    If _SQL_GetTable2D(-1, "select [name] from master.dbo.sysdatabases where dbid > 4;", $aDatabases, $iRows, $iColumns) = $SQL_ERROR Then
        Msgbox(16, "Error", _SQL_GetErrMsg())
    EndIf
    _SQL_Close()

    Return $aDatabases
EndFunc

Func _SQL_GetNRMVersion($sServer, $sUser, $sPass, $bSQLAuth = True, $sDatabase = $sNRM_DatabaseName)
    Local $oADODB
    Local $iDBVersion = -1
    Local $hQuery, $aRow, $sString
    Local $iRows, $iColumns
    Local $sColumnThatHasTheVersion = "AVersion"
    
    ; Register the error handler to prevent a hard crash on COM errors
    _SQL_RegisterErrorHandler()
    
    $oADODB = _SQL_Startup()
    If $oADODB = $SQL_ERROR Then
        Msgbox(16, "Error", _SQL_GetErrMsg())
        Return $iDBVersion
    EndIf
    
    _SQL_ConnectionTimeout(-1, 3)
    If $bSQLAuth Then
        $oADODB.ConnectionString = "Provider=SQLOLEDB.1;Password=" & $sPass & ";Persist Security Info=False;User ID=" & $sUser & ";Initial Catalog=" & $sDatabase & ";Data Source=" & $sServer
    Else
        $oADODB.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" & $sDatabase & ";Data Source=" & $sServer
    EndIf
    $oADODB.Open
    
    If _SQL_GetTable2d(-1, "select " & $sColumnThatHasTheVersion & " from dbo.ADM_SystemVersion;", $aRow, $iRows, $iColumns) = $SQL_OK Then
        If IsArray($aRow) Then $iDBVersion = $aRow[UBound($aRow) - 1][0]
    EndIf
    
    _SQL_Close()
    _SQL_UnRegisterErrorHandler()

    Return $iDBVersion
EndFunc

_sql.au3

Edited January 2, 2015 by buymeapc

[mLipok]

Did you try other provider ? NCLI ?

What a SQL Version ?

[buymeapc]

I did try changing the provider to SQLNCLI10, but it was still about the same speed. I'd like this to work with SQL 2003 and up.

Am I perhaps taking extra steps in my logic that I don't need to take?

[buymeapc]

Bump...?

[jchd]

Just a remark. It's a rather uncommon need to have to scan multiple databases for their names: you're supposed to know your DB(s) filenames. If the schema of these pultiple DBs is same or even close enough, you could consider merging them all in one DB.

[buymeapc]

I understand, but the reason I am scanning through all the databases is that this script will be run on different servers, all of which will have a different database layout, so I have no way of know what they have loaded - I just need to see if they have a specific database (or databases) so I can read and display information about it/them.

[mLipok]

I know of a commercial product (for management companies - for accounting offices), which allows the user to create separate databases for each client. Thus, the application checks all databases and displays a list of possible databases to connect.

[mrider]

I did try changing the provider to SQLNCLI10, but it was still about the same speed. I'd like this to work with SQL 2003 and up.

Am I perhaps taking extra steps in my logic that I don't need to take?

 

Generally speaking, making a connection to a database is extremely expensive.  Consequently most data aware applications use a connection pool of previously spun up connections when speed matters.

You are creating numerous connections, and most likely this is your speed issue.

I would suggest altering your logic such that you open the connection to the server once and get the database names with that, and then iterate through each name and switch to that database without closing the connection.  This won't work, but something along the lines of:

_SQL_GetTable2D(-1, "select [name] from master.dbo.sysdatabases where dbid > 4;", $aDatabases, $iRows, $iColumns)
For $i = 1 to Ubound($aDatabases) - 1
    _SQL_Execute(-1, "use [" & $aDatabases[$i][0] & "]")
    _SQL_GetTable2d(-1, "select " & $sColumnThatHasTheVersion & " from dbo.ADM_SystemVersion;", $aRow, $iRows, $iColumns)
    ...
Next

[buymeapc]

@mrider: Thank you for your suggestion. You were absolutely correct - I was establishing a connection and closing it each time I connected to a database which was where the immense delay was. I changed it around so it was all in one function utilizing "use [DB]" each time. My check went from 10 seconds down to 2!!

Thank you!