locked
Web Report return limit 10,000 rows RRS feed

  • Question

  •  

    Hello,

     

    I'm trying to run a report that must return more than 10,000 rows of information. I've tried the reg hack from SMS 2003 but that did not work for CM. Is there a different workaround for this issue in CM? Or did I simply fail to correctly make the hack?

     

    The registry change I refer to is detailed below:

     

    Changing the number of rows returned by a report query

    For performance reasons, Report Viewer limits the result set returned by a report query to 10,000 rows. You can modify the registry to override this limit and return any number of rows by using the following procedure.

    To change the number of rows returned by a report query

    1.

    On all computers on which a reporting point is enabled, run Regedt32.exe or Regedit.exe.

    2.

    Navigate to the following registry key:

    \HKEY_LOCAL_MACHINE_USER\Software\Microsoft\ SMS\Reporting

    3.

    In the Reporting key, create a DWORD value named Rowcount, and then set its value to the number of rows that you want returned. If you want to return all rows, set the value to 0xffffffff, which is the hexadecimal equivalent of -1.

    The appropriate number of rows is returned by any report query that is run from this reporting point.

     

    Thanks!

     

    --Grant

    Thursday, January 10, 2008 4:33 PM

Answers

All replies

  • Thursday, January 10, 2008 5:30 PM
  •  

    Hi Wally,

     

    I tried this to no avail- these are the same instructions that existed for SMS 2003. I assume that HKLM_USER is just another name for HKLM?

     

    I created the Reporting key under the SMS key and then the Rowcount DWORD and set it to hex for 50,000 but still only 10k rows are reflected. Any ideas? Is there a log I could check for this?

     

    Thanks,

     

    --g

    Monday, January 14, 2008 4:28 PM
  • You also need to change the ASPBufferingLimit setting in IIS. I assumed the doc link I pointed you to covered it, but now I see it does not.

     

    I believe you can set this in a file for IIS (Metabase.xml), or from its IIS Manager tool.

    Tuesday, January 15, 2008 7:09 PM
  • I have set the reg to ffffffff, the ASPBufferingLimit in the Metabase to a value over 4 million and the ASP script timeout to 180 seconds on the configuration of the default web site in IIS. None of this has worked at all. I still only render 10,000 rows. I am running CM07 on Windows server 64 bit edition.

    Tuesday, January 22, 2008 5:06 PM
  • I checked with dev, and there is one more thing to try:

     

    There is another configuration that configure the SQL Query time out. By default it is 300 seconds.

    It can be configured in global.asa. Session(“DBCommandTimeout”)=300, you can increase it to a bigger value and restart IIS.

     

    Tuesday, January 22, 2008 6:08 PM
  •  

    I have increased this value as well, however it never takes 5 minutes(300 seconds) before it times out. Usually after about 10 seconds it returns an error http 500 that says this site is under maintenance or this site has a programming error. As soon as I change the rowcount value back to zero (0) the page renders in reporting but still only 10,000 rows.
    Tuesday, January 22, 2008 7:12 PM
  • Okay, I got it working by doing all the things mentioned above as well as increasing the AspBufferingLimit from 4 million to over 24 million. Thanks

    Wednesday, January 23, 2008 1:47 PM
  • Hi David,

     

    We had someone internally who was having problems exactly like yours. He just responded stating that he got them working by setting the ASPBufferingLimit to over 20 million. Sounds like that was similar to your experience.

     

    This looks to be one we need to file away :-)

     

    Thanks for the followup,

     

    Wally

     

    Wednesday, January 23, 2008 9:12 PM
  • I tried several registry values (all multiples of 1024) and none of them worked until I used 0xffffffff.  Bingo.
    • Edited by HeyAdmin Thursday, June 21, 2012 11:29 PM
    Thursday, June 21, 2012 11:25 PM
  • Hello HeyAdmin, I have tried all things in the posts above including the 0xffffffff setting.  When I apply the latter all I get is 10,000 records.  I can use the 32767 setting and I get 32767 records back!  All the forums and the tech help entries I have found refer to the 0xffffffff as the one to return all.  For me and for others above this simply does not work.  I am running SCCM 2007 on Win 2003 R2.  The only thing I query is that when I set this in the registry and then return back into it I see 41ffffff not 0xffffffff.  Also, when you view the data colun in registry editor it displays as 0x41ffffff (1107296255).  I am unsure if this is normal and that this is just how the value displays once set.  All I know is that id does not return more than 10,00 records.

    Any suggestions would be gratefully received.

    Many thanks.

    Tuesday, June 26, 2012 8:50 AM