I'm trying to use data from a SharePoint list in SSRS (Reporting services).
The report isn't too big - only a few dozens rows, but for each row I need to add data from a second list, which (currently) contains 7000 rows. On SharePoint 2007 I've successfully done similar things, but I can't get it to work on SharePoint 2010.
- Using owssvr.dll to get list data as xml (my favorite).
- Using the new ListData.svc service.
- Using the web services (Lists.asmx,
I keep hitting the same wall - I can't get any data because the list is too big! I get a similar error to:
The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
This is discouraging. I've read quite a lot on the subject and can't see a good way around it besides increasing the threshold on the whole application.
Is there an easy way around this new limitation? Is there a hard way?
I'm looking for a "best practice"... It doesn't look I can keep using any of these methods reliably. The only "good" solution I can think of it to use SSIS to copy all rows to a database, but that seems like an overkill...