SqlCommand maximum parameters exception at 2099 pa

Question

I am batching different queries in one SqlCommand stopping the queries batch when I hit the 2100 parameter limit. If my batch has 2100 or 2099 parameters I still get the exception.

The following test code throws 'too many parameters exception' even if the number of parameters is less than 2100.

var parametersMax = 2099;

var connection = new SqlConnection(@"Data Source=.;Integrated Security=SSPI;");
connection.Open();

var enumerable = Enumerable.Range(0, parametersMax);

var query = string.Format("SELECT {0}", String.Join(", ", enumerable.Select(s => string.Format("P{0} = @p{0}",s))));

var command = new SqlCommand(query, connection);

foreach(var i in enumerable)
    command.Parameters.Add(string.Format("p{0}",i), i);

// here: command.Parameters.Count is 2099

var reader = command.ExecuteReader(); // throws: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

Considering the exception message and the documentation here: http://msdn.microsoft.com/en-us/library/ms143432.aspx I was expecting to be able to have 2100 parameters in one query, but this doesn't seem to be true. Does anyone know why? Am I missing something?

(I am using Sql Server 2008 R2)

Answer 1

The command sent to SQL Server is

exec sp_executesql 
          N'SELECT P0 = @p0, P1 = @p1, P2 = @p2...',
          N'@p0 int,@p1 int,@p2 int...',
          @p0=0,@p1=1,@p2=2...

Note that 2 of the parameter slots in the call to sp_executesql are taken up with the NVARCHAR strings for query text and the parameter definitions thus "only" leaving 2,098 free for you to use.