VBA Change Sql Timeout

I have the code below which works well in sql (copy code from immediate window into sql), however in instances where there is high usage on the server, it times out with around 41 Seconds. Is there an easy method to increase the timeout to around 2 mins?

Sub ROW21()
    With ProcImpReview
    Dim LASTCOL As Long
    SQLSTR = ""
    LASTCOL = .Range("IV6").End(xlToLeft).Column
        For I = 27 To LASTCOL
            SQLSTR = "select cast(sum(case when status = 'Closed' and Upper(Plant) = Upper('" & .Cells(5, I).Value & "')and CreateDatetime >= CONVERT(datetime,'" & DT & "',103)then 1 else 0 end)" _
                & "as varchar(5)) + '/' +" _
                & "Cast(sum(case when status is Null  and Upper(Plant) = Upper('" & .Cells(5, I).Value & "') and CreateDatetime >= CONVERT(datetime,'" & DT & "',103)then 1 else 0 end)as varchar(5))from [dbo].[vw_IE3_Quoteprogress_MY_MRO_Quotes]"

            Debug.Print SQLSTR
             Set RNG = .Cells(21, I)
             SQL_WEBSQL
        Next I
    End With
    SQLSTR = ""
End Sub


    Sub SQL_WEBSQL()

        Dim rs As Object
        Dim iCols As Integer
        Set rs = CreateObject("ADODB.Recordset")
        On Error GoTo ERR

            CONNECT_TO_WEBSQL
            rs.Open SQLSTR, PERSONALDBCONT
            If IsNull(rs(0)) Then
                RNG.Value = "0"
            Else
                RNG.CopyFromRecordset rs
            End If
            CLOSE_CONNECTION_TO_SQL

        Exit Sub

    ERR:
        CLOSE_CONNECTION_TO_SQL
        MsgBox "There was an error"
        Application.Visible = True
        End
    End Sub


Source: sql

Leave a Reply