Why is 2098 the maximum number of parameters in SqlCommand.Parameters

  • Thread starter Thread starter Paul Clark
  • Start date Start date
P

Paul Clark

If I exceed 2,098 parameters when calling SqlCommand.ExecuteNonQuery() I get
A SqlException:

"A severe error occurred on the current command. The results, if any,
should be discarded."

Does anyone know why I might be limited to 2098 parameters?
 
I want to second this.
I would call any software using more than 40-50 parameters in any function
call being broken by design.

--
Regards

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)
 
I'd have to agree with Paul, although I don't think he
made any implication about who incorrectly designed
anythign.

In your scenario, you would probably want to pass x number
of parameters through loops so you could conveniently
execute the 1000 Inserts. Since you can keep the
connection open, what possible downside is there to
sending it 1000 times vs sending 1000 inserts in a batch?
If you sent it as a transaction, you would put unnecessary
locks on the DB and if you didn't, and you got a failure,
how would you recover elegantly?

That's not to mention that accumulating 3000 params and
writing that statement would be a nightmare. And if you
included all of them in a batch, you'd have a Tremendously
large string to pass over the network.

As such, I can see only downside to making a 1000 Insert
SQL Statement vs a small one (or even better a Stored
Proc) that takes a smaller number and is just called over
and over again.

If there's a compelling reason to do it like you mention,
I'm certianly interested in hearing it.

Cheers,

Bill
 
I think we are at cross-purposes. I took "2098 parameters" to mean a
statement like

insert into someTable values(@param1, @param2, @param3.... @param2098);

Regards

Ron
 
Back
Top