3

I want to publish an SQL Server database, for testing purposes AdventureWorksDW, the data warehouse sample database that provides Microsoft. I created a new "WCF Data Service" project in VS and created an Entity Framework Model to pass it into the generic parameter of DataService base class. I included in the model some dimensions and the facts table FactInternetSales. It seem that FactInternetSales table contains too much data (60398 records) to be accessed through OData by default. When I try to iterate the FactInternetSales entity set from a console app, I get an System.Net.WebException with the message:

The underlying connection was closed: The connection was closed unexpectedly.

If I limit the numbers of rows to iterate, said 1000, I have not problem.

On the other hand, when I try to load the data from FactInternetSales into PowerPivot using this OData Service, I get this error message:

Unable to obtain schema for data feed 'FactInternetSales'. Please make sure this feed exists.

In the InitializeService method of the OData Service I have set config.SetEntitySetPageSize("*", 1000000), but it seem don't have any effect. I added also the following code to the service's web.config, without success: 

<system.web>
  ...
  <httpRuntime maxRequestLength="40960" />
</system.web>
...
<system.webServer>
  <security>
    <requestFiltering allowDoubleEscaping="true">
      <requestLimits maxAllowedContentLength="2147483647" />
    </requestFiltering>
  </security>
</system.webServer>

I guess it is a matter of configuration to allow this amount of data flow through the OData Service, but I can't find tho good place where I should change the default behavior.

Lester
  • 513
  • 5
  • 15
  • 2
    Did you try setting the page size to a much smaller number (typically it's something like 100-500)? Basically you want to get one response down to a managable size (no more than couple of MB, probably even less than that). PowerPivot will loop over all the pages, but since each page (a standalone HTTP request) is small enough, it should not fail randomly. – Vitek Karas MSFT Mar 29 '11 at 21:11
  • Thank you Vitek, setting the PageSize to 200 works fine for PowerPivot, but how can I iterate programmatically through all the items in a traditional foreach? It only give me the 200 firsts. How can I handle each "page"? – Lester Mar 29 '11 at 22:40
  • I found the answer about how to handle the pagesize defined serverside using the DataServiceQueryContinuation class. – Lester Mar 30 '11 at 16:41

0 Answers0