Too many parameters were provided in this RPC request

Question

I have a console application that compare two tables from two different databases and returns the missing data from the other table. I have this error message: "An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: 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."

I know, there are too many parameters (6400), but than how should I compare this two tables and get the missing data? I tried this code:

static void Main(string[] args)
{
    using (DataClasses_LOG01DataContext log01 = new DataClasses_LOG01DataContext(ConfigurationManager.ConnectionStrings["conn_log"].ConnectionString))
    {
        List<string> list = new List<string>();

        var l01 = log01.name_numbers
            .Select(x => new { x.name, x.number });

        foreach (var item in l01)
        {
            list.Add(item.name.Replace(".", "") + "_" + item.number);
        }

        using (DataClasses_SQL01DataContext sql01 = new DataClasses_SQL01DataContext(ConfigurationManager.ConnectionStrings["conn_sql"].ConnectionString))
        {
            var log = sql01.names
                .Select(x => new { x.name });

            var missing = log.Where(x => !list.Contains(x.name));

            foreach (var item in missing)
            {
                string[] result = item.ToString().Split('_');

                Console.WriteLine("{0} {1}", result[0], result[1]);
            }
        }
    }
}

Possible duplicate of [Too many parameters were provided in this RPC request. The maximum is 2100.?](http://stackoverflow.com/questions/1028547/too-many-parameters-were-provided-in-this-rpc-request-the-maximum-is-2100) — Mad Myche, May 18 '17 at 12:54

score 1 · Accepted Answer · answered May 18 '17 at 14:05

Consider using a simple batching mechanism, here's a simple extension method for your LINQ that may help ...

public static IQueryable<List<T>> BatchesOf<T>(this IQueryable<T> source, int chunkSize)
{
    return source
        .Select((x, i) => new { Index = i, Value = x })
        .GroupBy(x => x.Index / chunkSize)
        .Select(x => x.Select(v => v.Value).ToList())
        .ToList();
}

... this will return "batches" of your params that you can then use to run multiple queries ...

static void Main(string[] args)
{
    using (DataClasses_LOG01DataContext log01 = new DataClasses_LOG01DataContext(ConfigurationManager.ConnectionStrings["conn_log"].ConnectionString))
    {
        var list = log01.name_numbers
            .Select(x => x.name.Replace(".", "") + "_" + x.number))
            .BatchesOf(1000)
            .ToList();

        using (DataClasses_SQL01DataContext sql01 = new DataClasses_SQL01DataContext(ConfigurationManager.ConnectionStrings["conn_sql"].ConnectionString))
        {
            list.ForEach(batch => {
               var log = sql01.names.Select(x => x.name);
               var missing = log.Where(x => !batch.Contains(x.name));

               foreach (var item in missing)
               {
                  string[] result = item.ToString().Split('_');
                  Console.WriteLine("{0} {1}", result[0], result[1]);
               }
            });

        }
    }
}

Too many parameters were provided in this RPC request

1 Answers1