Question posted in
The official documentation can be found here.

Asp.net – SQL Server JSON Response Limited in jQuery AJAX

I’m trying to load a Handsontable with JSON data from SQL Server using SQL Server’s "FOR JSON AUTO" option and the jQuery AJAX POST method. The issue is the data being returned from the WebMethod function I use to retrieve the SQL Server data is truncating the JSON and therefore "breaking" the JSON format. I don’t believe it’s the jQuery AJAX POST method because if I pull the same JSON data as a static string from the function it works. It only truncates and breaks when I pull the data from SQL Server using the SQL Server "FOR JSON AUTO" option.

I’ve included are some sample code below.

Here is the jQuery AJAX POST method used to load the Handsontable. It is referring to a WebMethod function that returns data from SQL Server. I checked the length of the data returned from the function and it’s being truncated to 2033 characters even though the full length is about 2257 characters.

$.ajax({
    type: "POST",
    url: "test.aspx/LoadJsonFromSqlServer",
    data: '{MyRecordID: "1" }',
    contentType: "application/json; charset=utf-8",
    dataType: "json",
    success: function (MyResponse) {
        console.log(MyResponse.d.length);
        var vMYDATA = JSON.parse(MyResponse.d);
        vHOT.loadData(vMYDATA);
    },
    error: function (MyResponse) {
        console.log("errorThrown = " + errorThrown);
    }
});

Here’s the WebMethod being called by AJAX above. It pulls data from SQL Server and uses the SQL Server "FOR JSON AUTO" option to format that data as JSON and return to AJAX above. This works fine when it’s a small data set. However, larger data sets seem to be truncated.

<System.Web.Services.WebMethod()>
Public Shared Function LoadSqlServerJson(ByVal MyRecordID As String) As String
    Dim vDBLOCAL As String = ConfigurationManager.ConnectionStrings("dbTest").ConnectionString
    Dim vCNLOCAL As SqlConnection
    Dim vSQLOCAL As String
    Dim vCMLOCAL As SqlCommand
    Dim vDRLOCAL As SqlDataReader
    Dim vRESULT As String = ""

    vCNLOCAL = New SqlConnection(vDBLOCAL)
    vCNLOCAL.Open()
    vSQLOCAL = "SELECT [TRANSACTION_ID],[PRODUCT_NAME],[TRANSACTION_QTY],[TRANSACTION_COST],[TRANSACTION_TOTAL],[TRANSACTION_NOTES],[PHYSICALTEMPLATE_ID] FROM [INPR_T_PHYSICALTEMPLATEDETAIL] FOR JSON AUTO" ' WHERE [TRANSACTION_ID]=@P00 FOR JSON AUTO;"
    vCMLOCAL = New SqlCommand(vSQLOCAL, vCNLOCAL)
    vCMLOCAL.Parameters.AddWithValue("@p00", MyRecordID)
    vDRLOCAL = vCMLOCAL.ExecuteReader()

    If vDRLOCAL.Read() Then
        vRESULT = vDRLOCAL(0)
    End If

    vCNLOCAL.Close()
    vDRLOCAL = Nothing
    vCMLOCAL = Nothing
    vCNLOCAL = Nothing

    LoadSqlServerJson = vRESULT
End Function

The reason I don’t think it has to do with the AJAX method is because if I pass the exact same JSON that the SQL Server’s "FOR JSON AUTO" option returns (when running the query directly in SQL Server) as a static string, it works perfectly. The data is not truncated in AJAX and the Handsontable loads. Below is the full string that is returned. If I use this method, it all works.

<System.Web.Services.WebMethod()>
Public Shared Function LoadJsonFromString(ByVal MyRecordID As String) As String
    Dim vRESULT As String = ""
    vRESULT = ""
    vRESULT += "["
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 1,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 2,"
    vRESULT += "    ""PRODUCT_NAME"": ""Chocolate Chips"","
    vRESULT += "    ""TRANSACTION_QTY"": 4,"
    vRESULT += "    ""TRANSACTION_COST"": 3,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 12,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 3,"
    vRESULT += "    ""PRODUCT_NAME"": ""Butterscoth Chips"","
    vRESULT += "    ""TRANSACTION_QTY"": 3,"
    vRESULT += "    ""TRANSACTION_COST"": 5,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 15,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 4,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 5,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 6,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 7,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 8,"
    vRESULT += "    ""PRODUCT_NAME"": ""Chocolate Chips"","
    vRESULT += "    ""TRANSACTION_QTY"": 4,"
    vRESULT += "    ""TRANSACTION_COST"": 3,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 12,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 9,"
    vRESULT += "    ""PRODUCT_NAME"": ""Butterscoth Chips"","
    vRESULT += "    ""TRANSACTION_QTY"": 3,"
    vRESULT += "    ""TRANSACTION_COST"": 5,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 15,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 10,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 11,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  },"
    vRESULT += "  {"
    vRESULT += "    ""TRANSACTION_ID"": 12,"
    vRESULT += "    ""PRODUCT_NAME"": ""Cookie Dough"","
    vRESULT += "    ""TRANSACTION_QTY"": 5,"
    vRESULT += "    ""TRANSACTION_COST"": 20,"
    vRESULT += "    ""TRANSACTION_TOTAL"": 100,"
    vRESULT += "    ""TRANSACTION_NOTES"": ""Test"","
    vRESULT += "    ""PHYSICALTEMPLATE_ID"": ""WTF"""
    vRESULT += "  }"
    vRESULT += "]"

    LoadJsonFromString = vRESULT
End Function

One last thing, I read a number of related posts here with most suggesting altering the "web.config" file to increase the "maxJsonLength" value (in various ways) and none of them worked.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks again to Albert for his help in leading to the solution.

    Based on the link provided by his suggestion, there are two methods that worked. Each altered the SQL a bit to allow it to return the full string. There could be others, but these seemed the easiet.

    Method 1. Return the results to a variable. This method declares a variable "@JSON" and sets the results to that variable and then uses that variable in a final SELECT statement.

        vSQLOCAL += "DECLARE @JSON nvarchar(max)" & vbCrLf
    vSQLOCAL += "SET @JSON = (" & vbCrLf
    vSQLOCAL += "SELECT [TRANSACTION_ID],[PRODUCT_NAME],[TRANSACTION_QTY],[TRANSACTION_COST],[TRANSACTION_TOTAL],[TRANSACTION_NOTES],[PHYSICALTEMPLATE_ID]" & vbCrLf
    vSQLOCAL += "FROM [INPR_T_PHYSICALTEMPLATEDETAIL]" & vbCrLf
    vSQLOCAL += "FOR JSON AUTO" & vbCrLf
    vSQLOCAL += ")"
    vSQLOCAL += "SELECT @JSON"

    Method 2. Wrap the SQL in a SELECT statement. It simply wraps the original SQL with another SELECT statement:

        vSQLOCAL += "SELECT (" & vbCrLf
    vSQLOCAL += "SELECT [TRANSACTION_ID],[PRODUCT_NAME],[TRANSACTION_QTY],[TRANSACTION_COST],[TRANSACTION_TOTAL],[TRANSACTION_NOTES],[PHYSICALTEMPLATE_ID]" & vbCrLf
    vSQLOCAL += "FROM [INPR_T_PHYSICALTEMPLATEDETAIL]" & vbCrLf
    vSQLOCAL += "FOR JSON AUTO" & vbCrLf
    vSQLOCAL += ")"

    I opted to use the second method. Here's the full code:

    <System.Web.Services.WebMethod()>
Public Shared Function LoadSqlServerJson(ByVal MyRecordID As String) As String
    Dim vDBLOCAL As String = ConfigurationManager.ConnectionStrings("dbTest").ConnectionString
    Dim vCNLOCAL As SqlConnection
    Dim vSQLOCAL As String = ""
    Dim vCMLOCAL As SqlCommand
    Dim vDRLOCAL As SqlDataReader
    Dim vRESULT As String = ""

    vCNLOCAL = New SqlConnection(vDBLOCAL)
    vCNLOCAL.Open()

    vSQLOCAL += "SELECT (" & vbCrLf
    vSQLOCAL += "SELECT [TRANSACTION_ID],[PRODUCT_NAME],[TRANSACTION_QTY],[TRANSACTION_COST],[TRANSACTION_TOTAL],[TRANSACTION_NOTES],[PHYSICALTEMPLATE_ID]" & vbCrLf
    vSQLOCAL += "FROM [INPR_T_PHYSICALTEMPLATEDETAIL]" & vbCrLf
    vSQLOCAL += "FOR JSON AUTO" & vbCrLf
    vSQLOCAL += ")"

    vCMLOCAL = New SqlCommand(vSQLOCAL, vCNLOCAL)
    vCMLOCAL.Parameters.AddWithValue("@p00", MyRecordID)
    vDRLOCAL = vCMLOCAL.ExecuteReader()

    If vDRLOCAL.Read() Then
        vRESULT = vDRLOCAL(0)
    End If

    vCNLOCAL.Close()
    vDRLOCAL = Nothing
    vCMLOCAL = Nothing
    vCNLOCAL = Nothing

    LoadSqlServerJson = vRESULT
End Function

  2. 0

    Since it works with smaller datasets, then I would try changing the allowed size in web.config.

    Try this:

    <appSettings>
    <add key="aspnet:MaxJsonDeserializerMembers" value="2147483647" />
</appSettings>

    Now above is 2 gig, but the above appSettings key should fix this, and allow a larger size.

    You should find the above key in web.config after system.web, after system.webserver, and after runtime.

    Edit: Ok, then try these settings:

    <system.web.extensions>
 <scripting>
   <webServices>
     <jsonSerialization maxJsonLength="2047483647"/>
   </webServices>
 </scripting>
</system.web.extensions>


<security>
  <requestFiltering>
    <requestLimits maxAllowedContentLength="2048000000" maxQueryString="8192" />
  </requestFiltering>
</security>

    Edit: SQL server will truncate to 2033

    Given that SQL server will automatic limit json size returned, then seem some suggestions outlined here:

    FOR JSON PATH results in SSMS truncated to 2033 characters

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search