Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267

    Unanswered: Pass Through Query Timeout

    To All--

    I have a simple pass through query that I run against mulitple SQL Servers. I loop through all the servers and change the Connect string (changing the server name) of the PTQ before I run the query.

    Most times this is fine. However, every once in a while I run into a server that doesn't exist. It takes the PTQ around 10 minutes before it fails and throws an error. I have attempted to update the ODBC Timeout property but that only works when the query is running and not when it is still attempting to connect.

    Is there any way to shorten the amount of time that it takes to fail or does anyone know a good way to check if the server is online? Thanks.

    C

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use this function to list all available servers:
    Code:
    Public Function DMOScanServers() As Variant
    '
    ' You need to load and install (Regsvr32)
    ' the sqldmo.dll library to use this function.
    '
        Dim Servers As Variant
        Dim strList As String
        Dim i As Integer
        
        Set Servers = SQLDMO.ListAvailableSQLServers
        For i = 1 To Servers.Count
            If Len(strList) > 0 Then strList = strList & ","
            strList = strList & Servers.Item(i)
        Next i
        DMOScanServers = Split(strList, ",")
        
    End Function
    Note: According to Microsoft (Installing SQL-DMO), sqldmo will be removed in the future versions of Microsoft SQL Server. You can still download and install it.

    As an alternative, you can install sqlcmd (sqlcmd Utility) and use this:
    Code:
    Function GetSQLServerList() As Variant
    
        
        Dim strLine As String
        Dim booInList As Boolean
        Dim strFlagFile As String
        Dim strList As String
        
        strFlagFile = "SQLSvrList_" & Format(Now, "yyyy-mm-dd_hh-nn-ss") & ".done"
        Open "SQLSvrList.cmd" For Output As #134
        Print #134, "sqlcmd -L > " & "SQLSvrList.txt"
        Print #134, "echo done > " & strFlagFile
        Close #134
        Shell "SQLSvrList.cmd"
        Do
        Loop Until Len(Dir(strFlagFile)) > 0
        Open "SQLSvrList.txt" For Input As #134
        Do Until EOF(134)
            Line Input #134, strLine
            If booInList = True Then
                strLine = Trim(strLine)
                If Len(strLine) > 0 Then
                    If Len(strList) > 0 Then strList = strList & ","
                    strList = strList & strLine
                End If
            End If
            If Left(strLine, 4) = "Serv" Then booInList = True
        Loop
        Close #134
        If Len(Dir("SQLSvrList.txt")) > 0 Then Kill "SQLSvrList.txt"
        GetSQLServerList = Split(strList, ",")
        
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •