I come from Singapore but find myself now living and working in the Netherlands, specialising in SMS 2003 / Configmgr 2007. I enjoy working and connecting people with computers and I hope I can share as much information as I can here through this blog.
“Computers do not just work like that, at the press of a button. From the moment a computer is switched on in your office, magic happens. That’s why you need an IT department and they do more than crawling under your desk to plug in that wire you kicked out.”
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 :)