What is the maximum length of a SqlCommand query?

Question

I am currently updating and reading values with a C# script from SQL Server 2014. When using a SQlCommand to executeNonQuery, it pops out an error when running the script:

IndexOutOfRangeException: Array index is out of range.
Mono.Data.Tds.Protocol.TdsComm.AppendInternal (Int16 s)
Mono.Data.Tds.Protocol.TdsComm.Append (System.String s)
Mono.Data.Tds.Protocol.Tds70.WriteRpcParameterInfo (Mono.Data.Tds.TdsMetaParameterCollection parameters)
Mono.Data.Tds.Protocol.Tds70.ExecRPC (TdsRpcProcId rpcId, System.String sql, Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean wantResults)
Mono.Data.Tds.Protocol.Tds70.Execute (System.String commandText, Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean wantResults)
System.Data.SqlClient.SqlCommand.Execute (Boolean wantResults)
System.Data.SqlClient.SqlCommand.ExecuteNonQuery ()
(wrapper remoting-invoke-with-check)
System.Data.SqlClient.SqlCommand:ExecuteNonQuery ()
Database.DataBaseConnection.Update () (at Assets/DataBaseConnection.cs:674)

I counted how many characters the SqlCommand has, and it is 8,125 characters (no spaces), 10,874 characters (with spaces).

There are 198 parameters but I guess that it is not due to that, because somewhere I read that the maximum amount of parameters for a single query was 2000, am I right?

I reduced the number of parameters (till 20 parameters) and consequently, command length and it works like a charm (875 characters without spaces and 1,221 characters with spaces).

To sum up, my question is: what is the maximum length of a SqlCommand query in SQL Server 2014? And in SQL Server 2008?

Sample of my code:

//New command to update values in input table in sql server
using (SqlCommand command = new SqlCommand("UPDATE DigitalInputs" +
    " SET Value = CASE Name" +
    " WHEN @LI_Input_Variable1_Name THEN @LI_Input_Variable1_Value" +
    " WHEN @LI_Input_Variable2_Name THEN @LI_Input_Variable2_Value" +
    " WHEN @LI_Input_Variable3_Name THEN @LI_Input_Variable3_Value" +
    //It is the same from 3 till 99
    " WHEN @LI_Input_Variable99_Name THEN @LI_Input_Variable99_Value" +
    " END" +
    " WHERE Name IN (@LI_Input_Variable1_Name, @LI_Input_Variable2_Name, @LI_Input_Variable3_Name,
    //It is the same from 3 till 99
    @LI_Input_Variable99_Name);", connection))
{

command.Parameters.Add(new SqlParameter("LI_Input_Variable1_Name", "LI_Input_Variable1"));
command.Parameters.Add(new SqlParameter("LI_Input_Variable1_Value", LI_Input_Variable1.ToString()));
command.Parameters.Add(new SqlParameter("LI_Input_Variable2_Name", "LI_Input_Variable2"));
command.Parameters.Add(new SqlParameter("LI_Input_Variable2_Value", LI_Input_Variable2.ToString()));
command.Parameters.Add(new SqlParameter("LI_Input_Variable3_Name", "LI_Input_Variable3"));
command.Parameters.Add(new SqlParameter("LI_Input_Variable3_Value", LI_Input_Variable3.ToString()));
//It is the same from 3 till 99
command.Parameters.Add(new SqlParameter("LI_Input_Variable99_Name", "LI_Input_Variable99"));
command.Parameters.Add(new SqlParameter("LI_Input_Variable99_Value", LI_Input_Variable99.ToString()));

command.ExecuteNonQuery(); //Execute the non query
}

Post-Edited: I'm implementing this script with MonoDevelop 5.9.6. in Unity3D

Solution 1

Let me answer the question you ask: What is the maximum length of a SqlCommand query?.

The max size of a statement is 65536*Network Packet Size - which by default is around 1500 bytes. Make the math - that is around 90MB.

Exceeding this, though will NOT lead to your error. But it is the question you did ask.

Solution 2

I've been trying to find the limit by reducing the amount of updated variables and I found a sweet spot in the length of the SQL Command

5,973 characters (no spaces) 7,967 characters (with spaces)

with 142 parameters

I think that 8,000 characters may be the limit in this case, but I cannot 100% assure it.

I forgot to say that I'm using MonoDevelop 5.9.6., integrated in Unity3D. Another user (Damien_The_Unbeliever) gave me the clue that it may be a connection pooling and/or threading issues with Mono's implementation of the SqlClient classes

Comments

[Guruprasad J Rao]

I don't think its a parameter length issue. There has to be some parameter which is kind of array and its causing IndexOutOfRange Exception.. Without more details its difficult to answer as to what is actually causing the issue..

[mxix]

Please supply a sample of your C# code.

[Jeremy]

if you have to ask...it's too long :)

[MetalxBeat]

I've just edited my question with a sample of my code, shortened. It is a bit chaotic but it works, I tried to use dataTable and xml but as it didn't work, I thought about doing it this way.

[TomTom]

That code is not reproducing the error and has no relationship to the question you asked. Voting to close. Please reopen a new question with a full compiling example.

[MetalxBeat]

I'm sorry but I do think that it fully has relationship with my question. I though it was unnessary to put a full compiling example, as it would be too long. But if that is the matter, I can do it without any problem!

[Damien_The_Unbeliever]

Don't just give us a long code dump though - make sure that you create a minimal reproducible example. With emphasis on the minimal.

[TomTom]

@MetalxBeat No, it does not. The question is how long a statement can be (read your title). If your statement would be too long, the error would be different. Thus no relation. Yes, this is not the question you wanted answered - but it is the one you did ask.

[Shnugo]

Try to use shorter names :-) 100 x 3 x LEN(@LI_Input_Variable1_abcde) is very much, 100 x LEN(@vXX) + 200 x LEN(@nXX) is much less

[Damien_The_Unbeliever]

There's only a small clue in your question (the stack trace) that you're using Mono, not Microsoft's CLR. If you search on mono sqlcommand index out of range you'll find quite a few hits that hint at possible connection pooling and/or threading issues with Mono's implementation of the SqlClient classes.

[MetalxBeat]

@Damien_The_Unbeliever I think that you're right! I've just answered my question, I'm gonna edit my question to include that I'm using MonoDevelop. Thank you :D

[Shnugo]

Did you try to use shorter names? 8000 is very close to the max length of SQL Server VARCHAR(intValue). Don't know the specialities of Mono but the string length might be an issue here... You could safe ~20 characters in 300 places, Should be worth a try...

[MetalxBeat]

Thanks for the advice! Yep, I know that. The thing is that I cannot shorten variable names. If I could I would do it for sure but they must remain like they are.

[Avrob]

it is around 90 MB

[FindOutIslamNow]

@MetalxBeat This is the same case happened to me now. I am thinking I can fix by not passing too many parameters; instead use a temp table for the logic of script