locked
Maximum Parameters in Where Clause RRS feed

  • Question

  • Hello All,

    I hit a problem that seems to be some limitation to amount of parameters one can pass in a query.  I found the following link confirming this horrible error :

    http://msdn.microsoft.com/en-us/library/ms143432.aspx

    Can anyone please elaborate this ‘parameter limit’? And does anyone know what can be done?

    Below is the actual 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.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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.

    Sunday, October 23, 2011 5:08 AM

All replies

  • Hello All,

    I hit a problem that seems to be some limitation to amount of parameters one can pass in a query.  I found the following link confirming this horrible error :

    http://msdn.microsoft.com/en-us/library/ms143432.aspx

    Can anyone please elaborate this ‘parameter limit’? And does anyone know what can be done?

    Below is the actual 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.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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.
    • Merged by Dan GuzmanMVP Sunday, October 23, 2011 5:46 AM duplicate question
    Sunday, October 23, 2011 5:10 AM
  • Are you passing more than 2100 parameters to SP?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, October 23, 2011 5:11 AM
  • Hello All,

    I hit a problem that seems to be some limitation to amount of parameters one can pass in a query.  I found the following link confirming this horrible error :

    http://msdn.microsoft.com/en-us/library/ms143432.aspx

    Can anyone please elaborate this ‘parameter limit’? And does anyone know what can be done?

    Below is the actual 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.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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.
    • Merged by Dan GuzmanMVP Sunday, October 23, 2011 5:43 AM duplicate question
    Sunday, October 23, 2011 5:12 AM
  • Can you provide the exact syntax leading to this error? Do you really try to pass 2100 parameters - sounds a bit too high :)
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, October 23, 2011 5:26 AM
  • Can anyone please elaborate this ‘parameter limit’? And does anyone know what can be done?

    Below is the actual 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.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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.


    The error is self-explanatory.  SQL server has a limit of 2100 parameters in a single request.

    A possible solution to address the restriction is a table-valued parameter.  But, as Naomi mentioned, we need to know more about what you are trying to accomplish.

    Since this same question is posted to the Transact-SQL forum, I'll merge this thread to that one so that everyone involved has the same context, thereby preventing duplicate effort.  Please don't pst the same question to multile forums.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Edited by Dan GuzmanMVP Sunday, October 23, 2011 5:40 AM added duplicate question note
    Sunday, October 23, 2011 5:35 AM
  • Since this same question is posted to the Transact-SQL forum, I'll merge this thread to that one so that everyone involved has the same context, thereby preventing duplicate effort. Please don't pst the same question to multile forums.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, October 23, 2011 5:45 AM
  • Hi  Charles !
     
    I think the problem is with your WHERE Clause in your SQL Statement, your WHERE Cluase contains IN like below;

    SELECT * FROM #YourTable WHERE @YourColumn IN (1,2,....,2101)
    

    Actually you can only have 2100 parameters, but each item inside the IN parentheses is counted as 1 parameter.
     
    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

     

    Sunday, October 23, 2011 6:01 AM
    Answerer
  • > I think the problem is with your WHERE Clause in your SQL Statement, your WHERE Cluase contains IN like below; > > > [code] > > SELECT * FROM #YourTable WHERE @YourColumn IN (1,2,....,2101) > > [/code] > This is in correct. You can have more than 2100 elements in an IN list. I've run tests with 10000 elements. Performance is horrible, but it runs. I recall that when I had 50000 elements in the list, I got an error message, and abandoned the test. I don't recall whether the error was because I hit a hard limit, or it was only due to resource constraints. (Also, the above SELECT is kind of useless. It will return either all rows in the table, or no rows.) Now, it is possible that Charles1234 is doing something like: [sql] WHERE col IN (@p1, @p2, .....) [/sql] And in this case, the limit is indeed 2100 parameters. In this case using a table-valued parameter is a good idea. If Charles1234 is on SQL 2005, he could still compose a concatenated list of the values and crack them into a table. This article on my web site gives details for both situations: http://www.sommarskog.se/arrays-in-sql.html
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, October 23, 2011 11:41 AM
  • Hi Erland !

    Sorry, if i am unable to put my point corretly. The point i want to make is similar which you made in your statement " like: [sql] WHERE col IN (@p1, @p2, .....) [/sql] And in this case, the limit is indeed 2100 parameters".

    I am thankfull to you that you always pin point the error.

    Thanks, Hasham

     

    Sunday, October 23, 2011 12:09 PM
    Answerer