AJAX post to webmethod returns internal server error 500 only with large number of rows

I have a VB.Net webmethod that only throws a non descriptive 500 "error processing the request" when the number of returned rows increases. Debugging in the method, I can't see any error thrown on the server side. But if I use Fiddler to re-issue the command the same error shows so I assume it isn't my AJAX call but my webmethod. At this point I'm not sure how to get more details on the error since the server code isn't throwing an error.

The webmethod runs a SQL Query and then calls a function to format the results in HTML table format. The HTML table is later added to a page that uses DataTables for formatting. It works fine with 50 or rows but around 100 rows it throws the error. I can verify this because the first query returns 1 row per input parm supplied allowing me to test 50, 75, and 100 rows until I discovered the error.

At this point I'm not sure how to debug it. I can't see how to get more details on the possible cause.

    <System.Web.Services.WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public Shared Function GetReportData2(ByVal sRptId As String, ByVal sParms1 As String, ByVal sParms2 As String) As String
    'for some reason the parms on the screen come in with spaces as dividers
    sParms1 = sParms1.Replace(" ", ",").Replace(vbCr, ",").Replace(vbLf, ",")
    sParms2 = sParms2.Replace(" ", ",").Replace(vbCr, ",").Replace(vbLf, ",")
    Dim dt = New DataTable()

    Try
        Select Case sRptId
            Case "1"
                Using conn = New SqlConnection(ConfigurationManager.ConnectionStrings("DirectoryInfoConnectionString").ConnectionString)
                    Using cmd = New SqlCommand("s_rpt_BatchLookup_People", conn)
                        cmd.CommandType = CommandType.StoredProcedure
                        'set timeout to 60s
                        cmd.CommandTimeout = 60
                        cmd.Parameters.Add("@input", SqlDbType.VarChar, -1).Value = sParms1
                        Using adapter = New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                        End Using
                    End Using
                End Using
            Case "2"
                Using conn = New SqlConnection(ConfigurationManager.ConnectionStrings("DirectoryInfoConnectionString").ConnectionString)
                    Using cmd = New SqlCommand("s_rpt_BatchLookup_Client_Detail", conn)
                        cmd.CommandType = CommandType.StoredProcedure
                        'set timeout to 120s
                        cmd.CommandTimeout = 120
                        cmd.Parameters.Add("@input", SqlDbType.VarChar, -1).Value = sParms1
                        cmd.Parameters.Add("@cym", SqlDbType.VarChar, -1).Value = sParms2
                        Using adapter = New SqlDataAdapter(cmd)
                            adapter.Fill(dt)
                        End Using
                    End Using
                End Using
            Case Else
        End Select

        Return ConvertToHtmlFile2(dt)

    Catch ex As Exception
        Return ex.Message + ex.InnerException.ToString()
    End Try


End Function

    Public Shared Function ConvertToHtmlFile2(ByVal targetTable As DataTable) As String
    Dim myHtmlFile As String = ""
    If (targetTable Is Nothing) Then
        Throw New System.ArgumentNullException("targetTable")
    Else
        'Continue.
    End If
    Dim myBuilder As System.Text.StringBuilder = New System.Text.StringBuilder()
    myBuilder.Append("<thead><tr>")
    For Each myColumn As DataColumn In targetTable.Columns
        myBuilder.Append("<th>")
        myBuilder.Append(myColumn.ColumnName)
        myBuilder.Append("</th>")
    Next myColumn
    myBuilder.Append("</tr></thead>")
    'add body
    myBuilder.Append("<tbody>")
    'Add the data rows.
    For Each myRow As DataRow In targetTable.Rows
        myBuilder.Append("<tr>")
        For Each myColumn As DataColumn In targetTable.Columns
            myBuilder.Append("<td>")
            myBuilder.Append(myRow(myColumn.ColumnName).ToString())
            myBuilder.Append("</td>")
        Next myColumn
        myBuilder.Append("</tr>")
    Next myRow
    'Close tags.
    myBuilder.Append("</tbody>")
    'Get the string for return.
    myHtmlFile = myBuilder.ToString()
    Return myHtmlFile
End Function

Here is raw error data from Fiddler. I don't think it will help.

    HTTP/1.1 500 Internal Server Error
Server: ASP.NET Development Server/11.0.0.0
Date: Sun, 13 Sep 2015 20:23:55 GMT
X-AspNet-Version: 4.0.30319
jsonerror: true
Cache-Control: private
Content-Type: application/json; charset=utf-8
Content-Length: 91
Connection: Close

{"Message":"There was an error processing the request.","StackTrace":"","ExceptionType":""}

Update 1 I moved the webmethod to a separate asmx file for more testing. I also created a simple query to select X number of rows from a table. Once again the "internal server error 500" happens only when the number of rows exceeds a certain limit. But running the asmx directly does not throw the same error. It is only the AJAX POST request that appears to cause the error as the number of rows exceeds some limit.

Everything I read says this must be server side but if I isolate the asmx file it all works fine. I can't see what could be wrong with the AJAX post call to cause the error after a certain number of rows.

Solution

This ended up being a problem with the maxJsonLength not being set in the web.config. Due to customError mode being on, I did not get the message. Not sure why but setting the max value in the webmethod itself didn't help either. But I have read other posts saying it must be done in the webmethod. In the end this was hard to find because the error section of the AJAX call did not have a detailed message in the responseText field telling me the details of the error.

For more details on how the error message was lost see this post on customError mode and AJAX responseText