SRP_COM and SQL unknown errors

sbotes · June 2018

I am using SRP_Com to establish a connection with a SQL database. Once established I do a select to see if a particular record exists. If it does not I INSERT a new record. If it does exist I UPDATE the existing record. The following code is an example of how I do the query to determine if the record is present.
The Select Statement

Script = 'SELECT TOP 1 ': SqlColumns<1,1> :' FROM ':SQL_Filename:' WITH(NOLOCK) WHERE ' KeyVariable

SQLColumns is a list from a previous call.
KeyVariable is the key necessary to find a specific row. Example: Acct = 'SB1234'

The check is done using calls to a service routine.

Service ExecuteQuery(objConnection, QueryStatement, ReturnDataRows)

    Results = False$    ; // Assume no results from this query for now.
    If ReturnDataRows NE True$ then ReturnDataRows = False$
    If (objConnection NE '') AND (QueryStatement NE '') then
        objRecordSet    = SRP_COM(objConnection, 'CALL', 'Execute', QueryStatement)
        If SRP_COM('', 'ERROR') then
            Error_Services('Add', 'ADO error in the ' : Service : ' service: ' : SRP_COM('', 'ERROR'))
            Results     = ''    ; // Since there is an error, no result should be returned.
        end else
            If ReturnDataRows then
               Results = SQL_Services('GetAllQueryDataRows', objRecordSet)
                If Error_Services('HasError') then
                    ErrorMessage    = Error_Services('GetMessage')                    
                End
            end else
                Results = True$
            end
        end
        SRP_COM(objRecordSet, 'CALL', 'Cancel')
        SRP_COM(objRecordSet, 'RELEASE')
    end else
        Error_Services('Add', 'The objConnection or QueryStatement argument was missing in the ' : Service : ' service.')
    end

    Response = Results
Return


Service GetAllQueryDataRows(objRecordSet)
    DataRows    = ''

    If objRecordSet NE '' then
        DataRows        = SRP_COM(objRecordSet, 'CALL', 'GetString', 2, -1, @FM, @RM)
        If SRP_COM('', 'ERROR') then
            Error   = SRP_COM('', 'ERROR')
        end
        If DataRows[-1, 1] EQ @RM then DataRows[-1, 1] = ''

---->  The following error occurs randomly. (Unknown ADO error in the ' : Service : ' service)
        
     If DataRows EQ objRecordSet then
            DataRows    = ''
            Error_Services('Add', 'Unknown ADO error in the ' : Service : ' service.')
        end else
            If SRP_COM('', 'ERROR') then
                Error   = SRP_COM('', 'ERROR')
                If IndexC(Error, 'current record has been deleted', 1) then
                    DataRows    = ''
                end else
                    Error_Services('Add', 'ADO error in the ' : Service : ' service: ' : SRP_COM('', 'ERROR'))
                end
            end
        end
    end else
        Error_Services('Add', 'The objRecordSet argument was missing in the ' : Service : ' service.')
    end

    Response = DataRows

end service

DataRows and objRecordSet will all to often be equal and that indicates an error has occurred. I cannot duplicate it at will but do see it occur fairly often. There seems to be a tie to workload on the SQL Server but that is just a hunch.

SRP_COM does not return any error when checked just prior to checking the equal condition. Thus the 'Unknown' in the error message posted to Error_Services.

The SQL Server is sitting in the Azure cloud. There is some discussion on the web about Transient Errors and SQL. I have added the retry logic that was suggested but just can't seem to get around this error nor figure out exactly what is happening.

Any ideas or suggestions would be appreciated.