I need to be able to return data from 4000 customers. These 4000 customers unfortunately must be in the multi value parameter so I can select for example 10 different customers from the list and it will then return its data (with extra filter, so it saves time scrolling up & down). Correct me if I'm wrong, multi-value parameter only handles 1000 values at max, although I can see all 4000 values in the list, it will not return all 4000 (it does in the preview, not after it's deployed). I have tried to skim it down to 900 and it will return all 900. I do understand that it is not rational for a user to tick checkboxes more than that, but I still won't be able to return the rest of the 3000+ customers. What is the best way of doing it please?
我需要能够返回4000个客户的数据。不幸的是，这4000个客户必须是多值参数，因此我可以从列表中选择10个不同的客户，然后它将返回数据(使用额外的过滤器，这样可以节省上下滚动的时间)。如果我做错了，请纠正我，多值参数最多只能处理1000个值，尽管我可以看到列表中的所有4000个值，但它不会返回所有的4000(它在预览中执行，在部署后不会)。我试着把它撇去到900，它会把900都还回来。我确实明白，对于用户来说，勾选复选框的次数超过这个数目是不合理的，但是我仍然不能返回3000多个客户中的剩余部分。请问最好的方法是什么?
2 个解决方案
#1
3
We also are experienced same problem on our Development environment sql server 2008 R2 SP1.. But we found the workaround: <...>\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Web.config Inside add key:
我们的开发环境sql server 2008 R2 SP1也遇到了同样的问题。但我们找到了解决办法:<…> \ \ Microsoft SQL Server \ MSRS10_50程序文件。MSSQLSERVER \ Reporting Services \ ReportManager \ Web。配置里面添加关键:
<!-- language: xml -->
<add key="aspnet:MaxHttpCollectionKeys" value="10000" />
Restart reporting services and it should be working. Without adding aspnet:MaxHttpCollectionKeys default value is 1000.
重新启动报表服务，应该可以工作了。不添加aspnet:MaxHttpCollectionKeys默认值是1000。
源
#2
2
The limit of 1000 rows comes from declaring things like this:
1000行的限制来自如下声明:
DECLARE @myTable MyTableType
INSERT INTO @myTable VALUES (1), VALUES (2), VALUES (3)
EXEC myProc @myTable
That's known as row-constructor syntax, and there is a limit there.
这就是行构造函数语法，这里有一个限制。
You can get around this by using single-inserts, or batching the row-constructors together. While that seems like it might be very poor for performance, SQL Server creates what is known as a 'trivial plan' - and you can read some more about it on Bob B's excellent blog - here and here.
您可以通过使用单插入或将行构造函数组合在一起来解决这个问题。虽然这看起来可能对性能很差，但是SQL Server创建了一个所谓的“琐碎计划”——您可以在Bob B的优秀博客上阅读更多关于它的内容——这里和这里。
#1
3
We also are experienced same problem on our Development environment sql server 2008 R2 SP1.. But we found the workaround: <...>\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Web.config Inside add key:
我们的开发环境sql server 2008 R2 SP1也遇到了同样的问题。但我们找到了解决办法:<…> \ \ Microsoft SQL Server \ MSRS10_50程序文件。MSSQLSERVER \ Reporting Services \ ReportManager \ Web。配置里面添加关键:
<!-- language: xml -->
<add key="aspnet:MaxHttpCollectionKeys" value="10000" />
Restart reporting services and it should be working. Without adding aspnet:MaxHttpCollectionKeys default value is 1000.
重新启动报表服务，应该可以工作了。不添加aspnet:MaxHttpCollectionKeys默认值是1000。
源
#2
2
The limit of 1000 rows comes from declaring things like this:
1000行的限制来自如下声明:
DECLARE @myTable MyTableType
INSERT INTO @myTable VALUES (1), VALUES (2), VALUES (3)
EXEC myProc @myTable
That's known as row-constructor syntax, and there is a limit there.
这就是行构造函数语法，这里有一个限制。
You can get around this by using single-inserts, or batching the row-constructors together. While that seems like it might be very poor for performance, SQL Server creates what is known as a 'trivial plan' - and you can read some more about it on Bob B's excellent blog - here and here.
您可以通过使用单插入或将行构造函数组合在一起来解决这个问题。虽然这看起来可能对性能很差，但是SQL Server创建了一个所谓的“琐碎计划”——您可以在Bob B的优秀博客上阅读更多关于它的内容——这里和这里。