Query Param Limit with SQL Server

[dsbrady_98]

We have several reports where users can select a "subset" of users to apply the
results to (by selecting a custom-built query). In these reports, our queries
use cfqueryparam in the IN clause (with List="Yes"). cfqueryparam thus breaks
up the variable into individual SQL Server parameters. If the subset has too
many users (I know it happens at 2,800, but it might happen with fewer, as
well), the query will come back with an "The DBMS unspecified dbms error". If
I remove the queryparam, the problem goes away.

So, does anyone know if this is a problem with SQL Server itself (it's not
something I know how to test manually using Query Analyzer, unless I want to do
some VERY tedious hand-coding) or with the JDBC driver? If there's a fix for
it, I'd certainly prefer to use query parameters.

[Adam Cameron]

> So, does anyone know if this is a problem with SQL Server itself (it's not

One can only have 2000 items in an "IN" list. It's a limitation of SQL
Server, but I would not say it's a "problem". I'd be inclined to revisit
your code to see if it's possible to NOT have a 2800-item list in the
query. It's not very performant, for one thing.

Short term, loop over your list in blocks of 2000 items, adding a separate
IN() statement for each 2000.

--
Adam