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

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.. — Guruprasad J Rao, Apr 01 '16 at 09:55
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. — MetalxBeat, Apr 01 '16 at 10:33
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. — TomTom, Apr 01 '16 at 10:35
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! — MetalxBeat, Apr 01 '16 at 10:41
Don't just give us a long code dump though - make sure that you create a [mcve]. With emphasis on the minimal. — Damien_The_Unbeliever, Apr 01 '16 at 10:53
@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. — TomTom, Apr 01 '16 at 11:12
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** — Shnugo, Apr 01 '16 at 11:14
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. — Damien_The_Unbeliever, Apr 01 '16 at 12:54
@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 — MetalxBeat, Apr 01 '16 at 13:39

score 9 · Answer 1 · edited Nov 28 '17 at 15:38

9

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.

edited Nov 28 '17 at 15:38

BrianCooksey

1,543
1
12
19

answered Apr 01 '16 at 10:34

TomTom

61,059
10
88
148

it is around 90 MB – Avrob Feb 23 '17 at 08:12

score 2 · Answer 2 · answered Apr 01 '16 at 13:37

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

answered Apr 01 '16 at 13:37

MetalxBeat

97
1
2
11

1

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... – Shnugo Apr 01 '16 at 16:43
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. – MetalxBeat Apr 04 '16 at 06:37
1

@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 – FindOutIslamNow Jul 24 '17 at 12:59

What is the maximum length of a SqlCommand query?

2 Answers2