I have recently joined a team where they are using JSON serialization to pass parameter arrays to SQL Server stored procedures where they are then deserialized and the required values extracted i.e. Each stored procedure has a '@Parameters' parameter of type VARCHAR(MAX). The framework creating and executing the command is written in C# and uses standard .NET types (SqlCommand, SQlParameter) etc.
It appears that when the serialized content is greater in length than a threshold that the stored procedure is not being properly executed but no exceptions are raised. Nothing appears to happen. Running SQL Profiler I have observed that there is no attempt to execute the stored procedure in SQL Server.
For example: In one case there are just 30 instances of a type with no more than eight properties being serialized. The serialization succeeds and the value is assigned to the sql parameter in the parameters collection of a SqlCommand (there is only one parameter). The command is executed but nothing happens. If there are fewer occurrences of a type then it succeeds. When it does not succeed an exception is not being raised.
Using: SQL Server 2008 C# .NET 4.0 JSON Serialization provided by Newtonsoft. The SqlParameter in code is created as a varchar max. Client Server architecture - there are no intermediary services.
Does anyone know of a limit for JSON serialized values being passed as a sql parameter with a SqlCommand or have any ideas as to what might be causing this behaviour?
I've figured out what was happening in my case. It had nothing to do with the serialziation of the value in a sql parameter as I found that if I waited long enough (5 -7 minutes in my case) that the procedure eventually executed.
In the stored procedure there is a cursor being used to extract the records of interest (this is a batch update procedure). This cursor was calling the CLR function to deserialize the Json for each value it required. By inserting the desirialized data into a temp table for use in the cursor the 'issue' was resolved.