SQL Timeout Expired on Execute Reader

Question

I have an application that runs on our server that functions as a 'service' for all of our in-house applications. This application has been throwing an error every 25 minutes. The error is: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." "In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user." This was not happening until we made some recent changes to our app. The code that is failing simply calls a stored procedure and executes it using an sqldatareader. The stored procedure is below:

ALTER PROCEDURE GetActivePatientCoreMeasures 
-- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

SELECT DISTINCT ADT.[PV1 Room], adt.[Patient Account Number], [Diagnosis Codes].Description
FROM [CPSISQL].dbo.ADT
LEFT JOIN [CPSISQL].dbo.ADT_Diagnosis 
ON [CPSISQL].dbo.ADT.[Patient Account Number] = [CPSISQL].dbo.ADT_Diagnosis.[Patient Account #]
RIGHT JOIN [Diagnosis Codes]
ON ADT_Diagnosis.[Diagnosis Code] = [Diagnosis Codes].Code
WHERE [CPSISQL].dbo.ADT.[PV1 Discharge Date/Time] = ''
AND
(
    [CPSISQL].dbo.ADT.[PV1 Department] = '028' OR
    [CPSISQL].dbo.ADT.[PV1 Department] = '030' OR
    [CPSISQL].dbo.ADT.[PV1 Department] = '032' OR
    [CPSISQL].dbo.ADT.[PV1 Department] = '038' OR
    [CPSISQL].dbo.ADT.[PV1 Department] = '042' 
)
AND NOT(ADT_Diagnosis.[Diagnosis Type] = 'A')
ORDER BY adt.[PV1 Room]
END

Here is the routine that calls the stored procedure:

Private Sub UpdateDataFromADT_Codes(SqlConn As SqlConnection)
        Dim sqlCMD As SqlCommand = New SqlCommand("EXEC GetActivePatientCoreMeasures", SqlConn)
        Dim readerSQL As SqlDataReader = sqlCMD.ExecuteReader()

        While readerSQL.Read
            Dim thePatientNumber As String = SqlToString(readerSQL(1))
            Dim theCoreMeasure As String = SqlToString(readerSQL(2))

            For i As Integer = 0 To QueueOfPatientData.Count - 1
                If QueueOfPatientData(i).PatientNumber = thePatientNumber Then
                    With QueueOfPatientData(i)
                        Select Case theCoreMeasure
                            Case "PN"
                                .PN = "Yes"
                            Case "Sepsis"
                                .Sepsis = "Yes"
                            Case "STROKE"
                                .Stroke = "Yes"
                            Case "VTE"
                                .VTE = "Yes"
                            Case "CHF"
                                .CHF = "Yes"
                            Case "AMI"
                                .AMI = "Yes"
                        End Select
                    End With
                    'exit for since patient was found
                    Exit For
                End If
            Next
        End While

        readerSQL.Close()
    End Sub

Any help will be greatly appreciated!

What happens if you run the stored procedure from SSMS? How long does it take to run? It looks like the default timeout [is 30 seconds](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout(v=vs.110).aspx) for `SqlCommand`. — Mark, Jul 20 '16 at 16:13
This sounds exactly like a question I saw somewhere yesterday. Same column names and everything. I suggested on that one that you post the actual execution plan, table definition and indexes. You may also have some other kind of blocking going on. What were the recent changes you deployed? Are those changes hitting these same tables perhaps? — Sean Lange, Jul 20 '16 at 16:18
Ahh here we go....http://stackoverflow.com/questions/38466140/sql-stored-procedure-throws-error-every-25-minutes Perhaps you work together and both posted this as a question? — Sean Lange, Jul 20 '16 at 16:20

SQL Timeout Expired on Execute Reader

0 Answers0