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)
        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

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

        Exit Sub

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

Source: sql

Leave a Reply