Runtime error 3669, oracle, query modification

[strider86]

I need help troubleshooting a reporting code in VB6, using oracle database.
earlier the code was running just fine, but recently its been giving the Runtime error 3669. I searched the net for this error and have found out that its because of timeout. In the Administrative tools -> ODBC, i have disabled the "enable query timeout" check but it still doesnt work. The query is being called through the OpemRecordset function.

    Set rstTemp = conPubs.OpenRecordset(SqlString1, dbOpenDynamic)

This is the SqlString1 that is being run here:

SqlString1 = "SELECT TO_CHAR(VIEW_DC_WT_SNAPSHOT_02.TIMESTAMP,'MM/DD/YY HH24:MI'), max(VIEW_DC_WT_SNAPSHOT_02.VALUE1), max(VIEW_DC_WT_SNAPSHOT_02.sname1), max(VIEW_DC_WT_SNAPSHOT_02.VALUE2), max(VIEW_DC_WT_SNAPSHOT_02.sname2), max(VIEW_DC_WT_SNAPSHOT_02.VALUE3), max(VIEW_DC_WT_SNAPSHOT_02.sname3), max(VIEW_DC_WT_SNAPSHOT_02.VALUE4), max(VIEW_DC_WT_SNAPSHOT_02.sname4), max(VIEW_DC_WT_SNAPSHOT_02.VALUE5), max(VIEW_DC_WT_SNAPSHOT_02.sname5), max(VIEW_DC_WT_SNAPSHOT_02.VALUE6), max(VIEW_DC_WT_SNAPSHOT_02.sname6), max(VIEW_DC_WT_SNAPSHOT_02.VALUE7), max(VIEW_DC_WT_SNAPSHOT_02.sname7), max(VIEW_DC_WT_SNAPSHOT_02.VALUE8), max(VIEW_DC_WT_SNAPSHOT_02.sname8), max(VIEW_DC_WT_SNAPSHOT_02.VALUE9), max(VIEW_DC_WT_SNAPSHOT_02.sname9), max(VIEW_DC_WT_SNAPSHOT_02.VALUE10), max(VIEW_DC_WT_SNAPSHOT_02.sname10), max(VIEW_DC_WT_SNAPSHOT_02.VALUE11), max(VIEW_DC_WT_SNAPSHOT_02.sname11), max(VIEW_DC_WT_SNAPSHOT_02.VALUE12), max(VIEW_DC_WT_SNAPSHOT_02.sname12)"
        
        SqlStringTmp = ", max(VIEW_DC_WT_SNAPSHOT_02.VALUE13), max(VIEW_DC_WT_SNAPSHOT_02.sname13), max(VIEW_DC_WT_SNAPSHOT_02.VALUE14), max(VIEW_DC_WT_SNAPSHOT_02.sname14), max(VIEW_DC_WT_SNAPSHOT_02.VALUE15), max(VIEW_DC_WT_SNAPSHOT_02.sname15), max(VIEW_DC_WT_SNAPSHOT_02.VALUE16), max(VIEW_DC_WT_SNAPSHOT_02.sname16) From VIEW_DC_WT_SNAPSHOT_02 where VIEW_DC_WT_SNAPSHOT_02.ID =" & KRSNWT.WELL_TEST_ID & " group by VIEW_DC_WT_SNAPSHOT_02.timestamp,VIEW_DC_WT_SNAPSHOT_02.id"
        
        SqlString1 = SqlString1 & SqlStringTmp

Running this query directly in the database, i get the result in around 3.5 minutes. so probably this is a problem of timeout. But how do i get around it? this code is not using the QueryDef method. it is using Recordset.

Quering the database with

Select count(*) from view_dc_wt_snapshot_02

gives the result 1067478. and this query took arnd 6 minutes to give the result.

So my two questions are: How to bypass the timeout? and How can i improve my query to make it go faster? P.S. this isnt my coding, i am here to troubleshoot this

How can this be improved?

Thanks

[strider86]

Here is part of the code if any1 wants to see it::

Set rstTemp = conPubs.OpenRecordset(SqlString1, dbOpenDynamic)

      With rstTemp
Do While Not .EOF
            ' Result1 is a string
                    
                TimeStamp = .Fields(0)
                result1(0) = .Fields(1)
                result1(1) = .Fields(3)
                result1(2) = .Fields(5)
                result1(3) = .Fields(7)
                result1(4) = .Fields(9)
                result1(5) = .Fields(11)
                result1(6) = .Fields(13)
                result1(7) = .Fields(15)
                result1(8) = .Fields(17)
                result1(9) = .Fields(19)
                result1(10) = .Fields(21)
                result1(11) = .Fields(23)
                result1(12) = .Fields(25)
                result1(13) = .Fields(27)
                result1(14) = .Fields(29)
                result1(15) = .Fields(31)

                If IsNull(.Fields(2).Value) Then
                shrtname1(0) = " "
                Else
                shrtname1(0) = Replace(Mid(.Fields(2), InStr(InStr(1, .Fields(2), "_") + 1, .Fields(2), "_") + 1, InStrRev(.Fields(2), "_") - InStr(InStr(1, .Fields(2), "_") + 1, .Fields(2), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(4).Value) Then
                shrtname1(1) = " "
                Else
                shrtname1(1) = Replace(Mid(.Fields(4), InStr(InStr(1, .Fields(4), "_") + 1, .Fields(4), "_") + 1, InStrRev(.Fields(4), "_") - InStr(InStr(1, .Fields(4), "_") + 1, .Fields(4), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(6).Value) Then
                shrtname1(2) = " "
                Else
                shrtname1(2) = Replace(Mid(.Fields(6), InStr(InStr(1, .Fields(6), "_") + 1, .Fields(6), "_") + 1, InStrRev(.Fields(6), "_") - InStr(InStr(1, .Fields(6), "_") + 1, .Fields(6), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(8).Value) Then
                shrtname1(3) = " "
                Else
                shrtname1(3) = Replace(Mid(.Fields(8), InStr(InStr(1, .Fields(8), "_") + 1, .Fields(8), "_") + 1, InStrRev(.Fields(8), "_") - InStr(InStr(1, .Fields(8), "_") + 1, .Fields(8), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(10).Value) Then
                shrtname1(4) = " "
                Else
                shrtname1(4) = Replace(Mid(.Fields(10), InStr(InStr(1, .Fields(10), "_") + 1, .Fields(10), "_") + 1, InStrRev(.Fields(10), "_") - InStr(InStr(1, .Fields(10), "_") + 1, .Fields(10), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(12).Value) Then
                shrtname1(5) = " "
                Else
                shrtname1(5) = Replace(Mid(.Fields(12), InStr(InStr(1, .Fields(12), "_") + 1, .Fields(12), "_") + 1, InStrRev(.Fields(12), "_") - InStr(InStr(1, .Fields(12), "_") + 1, .Fields(12), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(14).Value) Then
                shrtname1(6) = " "
                Else
                shrtname1(6) = Replace(Mid(.Fields(14), InStr(InStr(1, .Fields(14), "_") + 1, .Fields(14), "_") + 1, InStrRev(.Fields(14), "_") - InStr(InStr(1, .Fields(14), "_") + 1, .Fields(14), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(16).Value) Then
                shrtname1(7) = " "
                Else
                shrtname1(7) = Replace(Mid(.Fields(16), InStr(InStr(1, .Fields(16), "_") + 1, .Fields(16), "_") + 1, InStrRev(.Fields(16), "_") - InStr(InStr(1, .Fields(16), "_") + 1, .Fields(16), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(18).Value) Then
                shrtname1(8) = " "
                Else
                shrtname1(8) = Replace(Mid(.Fields(18), InStr(InStr(1, .Fields(18), "_") + 1, .Fields(18), "_") + 1, InStrRev(.Fields(18), "_") - InStr(InStr(1, .Fields(18), "_") + 1, .Fields(18), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(20).Value) Then
                shrtname1(9) = " "
                Else
                shrtname1(9) = Replace(Mid(.Fields(20), InStr(InStr(1, .Fields(20), "_") + 1, .Fields(20), "_") + 1, InStrRev(.Fields(20), "_") - InStr(InStr(1, .Fields(20), "_") + 1, .Fields(20), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(22).Value) Then
                shrtname1(10) = " "
                Else
                shrtname1(10) = Replace(Mid(.Fields(22), InStr(InStr(1, .Fields(22), "_") + 1, .Fields(22), "_") + 1, InStrRev(.Fields(22), "_") - InStr(InStr(1, .Fields(22), "_") + 1, .Fields(22), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(24).Value) Then
                shrtname1(11) = " "
                Else
                shrtname1(11) = Replace(Mid(.Fields(24), InStr(InStr(1, .Fields(24), "_") + 1, .Fields(24), "_") + 1, InStrRev(.Fields(24), "_") - InStr(InStr(1, .Fields(24), "_") + 1, .Fields(24), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(26).Value) Then
                shrtname1(12) = " "
                Else
                shrtname1(12) = Replace(Mid(.Fields(26), InStr(InStr(1, .Fields(26), "_") + 1, .Fields(26), "_") + 1, InStrRev(.Fields(26), "_") - InStr(InStr(1, .Fields(26), "_") + 1, .Fields(26), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(28).Value) Then
                shrtname1(13) = " "
                Else
                shrtname1(13) = Replace(Mid(.Fields(28), InStr(InStr(1, .Fields(28), "_") + 1, .Fields(28), "_") + 1, InStrRev(.Fields(28), "_") - InStr(InStr(1, .Fields(28), "_") + 1, .Fields(28), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(30).Value) Then
                shrtname1(14) = " "
                Else
                shrtname1(14) = Replace(Mid(.Fields(30), InStr(InStr(1, .Fields(30), "_") + 1, .Fields(30), "_") + 1, InStrRev(.Fields(30), "_") - InStr(InStr(1, .Fields(30), "_") + 1, .Fields(30), "_") - 1), "_", "-")
                End If
                If IsNull(.Fields(32).Value) Then
                shrtname1(15) = " "
                Else
                shrtname1(15) = Replace(Mid(.Fields(32), InStr(InStr(1, .Fields(32), "_") + 1, .Fields(32), "_") + 1, InStrRev(.Fields(32), "_") - InStr(InStr(1, .Fields(32), "_") + 1, .Fields(32), "_") - 1), "_", "-")
                End If

               

                For RowStart = 14 To RecCount + 13
                    'For ColStart = 2 To 17
                            'identif = shtDaily.Cells(12, ColStart).Value
                            If TimeStamp = shtDaily.Cells(RowStart, 1).Value Then
                            
                            For ColStart = 2 To 17
                            identif = shtDaily.Cells(12, ColStart).Value
                            
                            For j = 0 To 15
                            If identif = shrtname1(j) Then
                            shtDaily.Cells(RowStart, ColStart).Value = result1(j)
                            End If
                            Next
                            
                            'If identif = shrtname1(ColStart - 2) Then
                               'shtDaily.Cells(RowStart, ColStart).Value = result1(ColStart - 2)
                               'shtDaily.Cells(RowStart, 2).Value = result1(9)
                               'shtDaily.Cells(RowStart, 3).Value = result1(15)
                               'shtDaily.Cells(RowStart, 4).Value = result1(4)
                               'shtDaily.Cells(RowStart, 5).Value = result1(1)
                               'shtDaily.Cells(RowStart, 6).Value = result1(0)
                               'shtDaily.Cells(RowStart, 7).Value = result1(7)
                               'shtDaily.Cells(RowStart, 8).Value = result1(12)
                               'shtDaily.Cells(RowStart, 9).Value = result1(10)
                               'shtDaily.Cells(RowStart, 10).Value = result1(5)
                               'shtDaily.Cells(RowStart, 11).Value = result1(2)
                               'shtDaily.Cells(RowStart, 12).Value = result1(11)
                               'shtDaily.Cells(RowStart, 13).Value = result1(8)
                               'shtDaily.Cells(RowStart, 14).Value = result1(6)
                               'shtDaily.Cells(RowStart, 15).Value = result1(3)
                               'shtDaily.Cells(RowStart, 16).Value = result1(14)
                               'shtDaily.Cells(RowStart, 17).Value = result1(13)
                               'End If
                               Next
                            End If
                   ' Next
                
                 Next

           .MoveNext
           Loop

This is where the query result is to be used, but it gives the runtime error 3669 on the 1st line itself. Also, for clarification, this program queries the database, and puts the results in an excel sheet.

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

[leinad31]

Hmmmm, what could have changed recently... Have you tried asking the database administrator if he recently changed the timeout setting at the database?