Selecting id from database where not in large collection, Too many parameters provided

Question

I am working on a application that has two databases.

I have an object, let's call it permit, that has a list of id's referencing to id's from a table, lets call that tasks, in the other database.

I am trying to run the following query:

var listOfUsedIds = select taskid from Permit_Task;
Select * from task where id not in (listOfUsedIds)

When I run this code I get the error:

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 can't run a sub select or any thing because NHibernate won't let me do that over two databases.

Can anyone help me how to work around this problem?

you would check this as well: http://stackoverflow.com/questions/656167/hitting-the-2100-parameter-limit-sql-server-when-using-contains — Davide Piras, Sep 15 '11 at 08:29
The message is clear: `**too many parameters** were provided`. Solution: use **less** parameters.... — marc_s, Sep 15 '11 at 08:43
One option is to insert the ids into a temporary table first. Temporary tables are often stored in memory and quite efficient. — Miserable Variable, Sep 15 '11 at 08:45
This should not be closed as duplicate of a query that hits the limit using contains -- with contains the client can fire multiple queries and trivially union the results; with **not in** the client side operations becomes more complex — Miserable Variable, Sep 15 '11 at 09:02
@Davide Piras the OP states `not in` which excludes batches i think — Firo, Sep 15 '11 at 10:18
You can create a link server and fetch the table of second database from first database. It's possible in sql server. — Romil Kumar Jain, Apr 26 '12 at 13:11

score 1 · Answer 1 · answered Sep 15 '11 at 11:40

using (var tx = session.BeginTransaction())
{
    session.CreateSQLQuery("CREATE TEMP TABLE usedIds (id INT)").ExecuteUpdate();

    for (int index = 0; index < ids.Length; index++)
    {
        // TODO: batch this
        session.CreateSQLQuery("INSERT INTO usedIds VALUES (:p" + index + ")")
            .SetParameter("p" + index, id)
            .ExecuteUpdate();
    }

    session.CreateSQLQuery("CREATE INDEX usedIds_idx ON usedIds (id)").ExecuteUpdate();


    Batch batch;
    while((batch.List = session.CreateSQLQuery("SELECT id FROM tasks t WHERE 1 = (SELECT COUNT(*) FROM usedIds u WHERE u.id = t.id) LIMIT 10 OFFSET " + batch.Number).List<int>()).Count > 0)

    {
        var tasks = session.QueryOver<Task>()
            .Where(t => t.Id.IsIn(batch))
            .List();
        // Do something with the tasks
    }
    tx.Commit();
}

or

public TaskMap()
{

    Map(x => x.IsUsedCount).Formula("SELECT (SELECT COUNT(*) FROM usedIds u WHERE u.Id = Id)").LazyLoad();
}

var tasks = session.QueryOver<Task>()
    .Where(t => t.IsUsedCount == 0)
    .List();

score 0 · Answer 2 · answered Sep 15 '11 at 15:34

0

Just break the listOfUsedIds into smaller blocks (say, 200 ids each), run the query for each block, and .Concat() the results.

answered Sep 15 '11 at 15:34

Diego Mijelshon

52,548
16
116
154

Selecting id from database where not in large collection, Too many parameters provided

2 Answers2