none
MS SQL Server limitation during edmx schema update RRS feed

  • Question

  • Hi,

    during updating model from (big) database in EF 6 I encountered problem with SQL server limitation:

    An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

    I can see in profiler that during updating model, EF is trying to run sp_executesql procedure with 2XXX parameters. I found that the same problem was already posted in year 2012. Is there any solution/workaround for this problem after 4 years (and I don't mean splitting edmx schema)?

    Friday, September 16, 2016 3:24 PM

All replies

  • Hi xxviktor,

    According to your description, it seems that it is not a entity framework issue, it's MS SQL Server limitation, I would suggest that you could post your issue on MS SQL forum for support.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    In addition, Since you use too many parameters, I suggest you could use a table-valued parameter, such as:

    --Create the type only once in your database
    create type myTableType as table (a int)
    
    --This happens every time
    declare @a myTableType;
    
    insert into @a (a) values (1), (2), (3);
    
    select * from someTable
    where id in (select a from @a)
    Then you can have parameters of that type (in my example myTableType) passed to your stored procedure. Keep in mind that they behave for all intents and purposes like a table variable. For more information, please refer to:

    https://code.msdn.microsoft.com/Stored-Procedure-with-6c194514

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, September 19, 2016 1:15 AM
    Moderator
  • Hi Cole,

    well, it is kind of an EF limitation, because it tries to update whole model in one step. Problem could be solved if the EF would know that the db is MS SQL (which has got a 2100 parameters limitation) and than update model in more steps (so it would not update entire schema in one run of sp_executesql procedure but divide model to two halves and run sp_executesql twice).

    I am afraid that table valued parameter will not help me there, because I am not talking about query to business entities but about updating model from database - it's EF function and EF is calling query to db sys tables to obtain metadata about tables. I am not sure how much I can control updating model from database in Entity Framework.

    Monday, September 19, 2016 7:13 AM
  • Hi xxviktor,

    >>Problem could be solved if the EF would know that the db is MS SQL (which has got a 2100 parameters limitation) and than update model in more steps (so it would not update entire schema in one run of sp_executesql procedure but divide model to two halves and run sp_executesql twice).

    As far as I know, it seems that EF does not provide related function to deal with the issue.

    I would suggest that you could post a feedback on the following link.

    https://connect.microsoft.com/VisualStudio/Feedback

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 27, 2016 8:49 AM
    Moderator