Professional Web Applications Themes

Query Param Limit with SQL Server - Coldfusion Database Access

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 ...

  1. #1

    Default Query Param Limit with SQL Server

    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 yzer, 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.

    dsbrady_98 Guest

  2. #2

    Default Re: Query Param Limit with SQL Server

    > 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
    Adam Cameron Guest

Similar Threads

  1. How to limit # of rows returned from query
    By Steve Grosz in forum Macromedia ColdFusion
    Replies: 7
    Last Post: May 2nd, 07:35 PM
  2. .net client + Axis 1.1 server + wrapped param: null result
    By Zihong Lu in forum ASP.NET Web Services
    Replies: 13
    Last Post: June 23rd, 09:53 PM
  3. 2003 OS Server Hard drive install limit.
    By WOCONNO4 in forum Windows Server
    Replies: 3
    Last Post: June 23rd, 07:47 PM
  4. Pass Param to SQL Query
    By rikesh in forum ASP Components
    Replies: 3
    Last Post: January 30th, 08:58 AM
  5. Replies: 2
    Last Post: August 11th, 07:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139