Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am getting below Sql Exception message while deleting 20200 rows.

The incoming request has too many parameters. The server supports a maximum of 2100 parameters. 
Reduce the number of parameters and resend the request.

I Changed my code and pass maximum 2000 parameters but I still got the error.


What I have tried:

I used linq skip and take extension method and try to pass limited numbers of parameters.

DELETE FROM dbo.MyTable 
WHERE identifier = @pIdentifier AND value in (@pFieldValues)


int parametersCount = fieldValues.Count;
int restrictedSize = 2000;
try
{
	int NumberOfTimesToExecuteDeleteCommand = (int)Math.Ceiling((double)parametersCount / restrictedSize);

	for (int i = 1; i <= NumberOfTimesToExecuteDeleteCommand; i++)
	{
		cmd.AddParametersWithValues("@pFieldValues", fieldValues.Skip((i - 1) * restrictedSize).Take(restrictedSize));
		cmd.ExecuteNonQuery();
	}
}
catch (Exception ex)
{
	string er = ex.Message;
}
Posted
Updated 14-Jan-20 6:00am

I don't have the necessary setup to test this at the moment, but I think that all of your existing parameters just remain part of the command after you call ExecuteNonQuery. That would mean that in the second loop iteration, you end up with 4000 parameters in the query. Try clearing all parameters before adding new ones.
 
Share this answer
 
v2
This is because, at each iteration of your for loop, you are adding a new parameter to the command.
You could try:
C#
try
{
   int NumberOfTimesToExecuteDeleteCommand = (int)Math.Ceiling((double)parametersCount / restrictedSize);
   cmd.Parameters.Add("@pFieldValues", SqlDbType.<enter your desired type here>);
   for (int i = 0; i < NumberOfTimesToExecuteDeleteCommand; i++)
   {
      cmd.Parameters["@pFieldValues"].Value = fieldValues.Skip(i * restrictedSize).Take(restrictedSize));
      cmd.ExecuteNonQuery();
   }
}

Or you could issue a separate delete command for each value, eventually wrapped in a transaction.
 
Share this answer
 
v2
I am an anti-ORM person, and I personally would create a Stored Procedure to do this and pass in your "parameters" as a delineated string.

Without knowing what type the field values are in your program or database and what they may contain; I am going to base this off of a common type (string or numeric) and I am going to just use a comma as a deliminator

Here is a sample SQL Stored Procedure
SQL
CREATE PROCEDURE dbo.MyTable_Delete_ByIdentifierAndValues (
	@Identifier	INT, 			-- choose appropriate data type
	@FieldValues	NVARCHAR(8000),
	@Delineator	NCHAR(1) = ','
) AS 
BEGIN
	DELETE	MyTable
	WHERE	identifier = @Identifier
	AND	[value] IN ( SELECT value FROM STRING_SPLIT(@FieldValues, @Delineator);
END
GO
And here is the rough C# code to utilize it
C#
SqlConnection conn = new SqlConnection(connectionstring);

string Delineator = ",";
int RowsAffected = -1;

SqlCommand cmd = new SqlCommand("dbo.MyTable_Delete_ByIdentifierAndValues", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Indetifier", /* identifier */);
cmd.Parameters.AddWithValue("@FieldValues", /* FieldValues.ToDelineatedString */ );
cmd.Paramaters.AddWithValue("Delineator", Delineator);

conn.Open();
RowsAffected = cmd.ExecuteNonQuery();
conn.Close();
 
Share this answer
 
Comments
istudent 14-Jan-20 15:21pm    
Thank you. I learned something today.
MadMyche 14-Jan-20 15:28pm    
What did not work? What is the content of the identifier, fields, and delineater?
Richard Deeming 15-Jan-20 9:13am    
For SQL Server, I'd be inclined to use a table-valued parameter instead of a delimited string:
Use Table-Valued Parameters (Database Engine) - SQL Server | Microsoft Docs[^]

Or possibly an XML or JSON string, depending on the SQL Server version.

Particularly if the field values aren't integers, and could possibly contain the delimiter character within a single value. :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900