Increasing the number of rows returned by a SCCM Report Query

Some people like big and massive queries that return thousands of rows. However, SCCM Report Viewer does not like handling more than 10,000 rows by default in a result set returned by a single query. I am in favour though of this default as it is good practice to limit queries as much as possible to improve performance and efficiency. However, there can be situations when you will need to manually adjust this limit. One indication that you have hit the limit is when the Report provider shows the following error after running the query with your defined parameters. Do not fret! A simple alteration in the registry will resolve this matter!

 

You must do the registry modification on the reporting point server, it could be a primary site for example, all depending on where you are running the report from.

1. Open up the Registry Editor <REGEDIT>

2. Navigate to HKEY_LOCAL_MACHINE\Software\Microsoft\SMS\Reporting

3. Create a DWORD value named Rowcount if it does not exist.

4. Set its value to the number of rows that you want returned. The unlimited setting (which returns all rows) is '0xffffffff'.

5. Note also that the Report Viewer by default sets the maximum to 10,000 rows if an invalid number is entered.

6. Restart the SMS_REPORTING_POINT Service ("c:\inetpub\wwwroot\SMSComponent\SMSRPH.exe")

7. Run your query and have fun :)