What is the fastest way to check for SQL Server?

Question

I have an MS Access program used in many places. It connects to MS SQL Server tables, but the server name is different in every place. I am looking for the fastest way to check server availability. The code I use is as follows:

ShellWait "sc \\" & ServerName & " qdescription MSSQLSERVER > " & Qt(fn)
FNum = FreeFile()
Open fn For Input As #FNum
Line Input #FNum, Result
Close #FNum
Kill fn

If InStr(Result, "SUCCESS") Then ...

ShellWait: executes a shell command and waits for it to complete. Qt: wraps the string in double quotes
fn: temporary file name variable

I am running the above code with a list of server names (of which only one is usually available). The code takes about one second if the server is available and takes about 8 seconds for each unavailable server. I would like to get both of these lower if possible, but especially the case of failure, as this happens most often.

Answer 1

nslookup.exe sc.exe. SQL Server , nslookup . , SQL Server 8 1 . , . , , (, [ShellWait, Qt, PassThru, LogError] ):

UPDATE: , dmaruca clsRunApp ( ) , Philippe . , , . , :

Function SQLServerDBExists(ComputerName As String, DbName As String) As Boolean
Const LocalHost = "127.0.0.1"
Dim Result As String, RunApp As New clsRunApp

    On Error GoTo Err_SQLServerDBExists

    If ComputerName <> LocalHost And _
       ComputerName <> "." And _
       ComputerName <> Environ("COMPUTERNAME") Then
        'Check for existence of the server using Name Server Lookup'
        Result = RunApp.RunAppWait_CaptureOutput("nslookup " & ComputerName)
        If InStr(Result, "Non-existent domain") Or _
           InStr(Result, "Default servers are not available") Then
            SQLServerDBExists = False
            GoTo Exit_SQLServerDBExists
        End If
    End If

    Result = RunApp.RunAppWait_CaptureOutput("sc \\" & ComputerName & " qdescription MSSQLSERVER")
    If InStr(Result, "SUCCESS") Then
        With PassThru("SELECT Name FROM sysdatabases " & _
                      "WHERE Name='" & DbName & "'", "master", ComputerName)
            SQLServerDBExists = (Not .EOF)
        End With
    End If

Exit_SQLServerDBExists:
    Exit Function
Err_SQLServerDBExists:
    LogError Err.Number, Err.Description, "SQLServerDBExists", "AttachToSQL"
    Resume Exit_SQLServerDBExists
End Function

. , Environ ( "COMPUTERNAME" ) 100% , , . , .

Answer 2

ADO - , . ():

Dim cn As ADODB.Connection 
Set cn = New ADODB.Connection
cn.ConnectionTimeout = 4     ' Wait at most 4 seconds for connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=" & ServerName & ";Integrated Security=SSPI"

On Error Resume Next
cn.Open
If Err.Number > 0 Then
    ...
Else
    cn.Close
    ...
End If
On Error Goto 0   ' replace 0 with previously used error handler

Answer 3

, , , . , , windows api. , ().

, .

Function SQLServerDBExists(ServerName As String, DbName As String) As Boolean
Dim Result As String
Dim cls as new clsRunApp

On Error GoTo Err_SQLServerDBExists

'Check for existence of the server
Result = cls.RunAppWait_CaptureOutput("nslookup " & ServerName)
If InStr(Result, "Non-existent domain") Then
    SQLServerDBExists = False
    GoTo Exit_SQLServerDBExists
End If

 Result = cls.RunAppWait_CaptureOutput("sc \\" & ServerName & " qdescription MSSQLSERVER")
If InStr(Result, "SUCCESS") Then
    With PassThru("SELECT Name FROM sysdatabases " & _
                  "WHERE Name='" & DbName & "'", "master", ServerName)
        SQLServerDBExists = (Not .EOF)
    End With
End If

Exit_SQLServerDBExists:
    Exit Function
Err_SQLServerDBExists:
    LogError Err.Number, Err.Description, "SQLServerDBExists", "AttachToSQL", , , Erl
    Resume Exit_SQLServerDBExists
End Function

Answer 4

, ( ), , , . , , .

Answer 5

telnet servername 1433

Answer 6

, , DNS. ( "" ), ( ), .

, , ( ) ( xml - - ) . , (), , , , xml- .

( ) IP- / "" : , "localhost". " ", , IP-. , ip aaa.bbb.ccc.ddd, , aaa.bbb.120.132, 120.132 .