CFQUERY record count limitation

[Steve Sommers]

I've been trying to figure out an issue for days with some customer's data of our where totals did not agree between a totals only report and a detail report. There is a lot of data so reproducing the issue in a test environment added to the time. Anyway, while in the shower this morning it occurred to me, maybe this is not a logic issue in the report generator and instead a CF limitation -- let's dump out the record count. Low and behold, record count = 100000.

There appears to be a 100000 record limit for query result sets. I cannot find any reference to this limit nor anyplace to change it. I just thought other would like to know.

BTW, CF9, 64 bit.

[Miguel-F]

Just throwing this out there as a thought, it might not be a limitation of ColdFusion but a limitation of the database server.  Maybe not so much a limitation but perhaps a default setting for the maximum records to be returned.

What database server are you using?

[Steve Sommers]

Ms-SQL 2008. It's not a SQL limitation as far as I can tell. Query analyzer returned the full result set. It's no big deal - now that I know. A little note in the doc would have helped me a lot.

[Dan_Bracuk]

I just ran a cfquery from a redbrick database that returned 220740 rows.  You did say 100,000 and not 1,000,000 right?  Just in case, let me adjust that query.  Now I get 2,366,871 records.

[Steve Sommers]

Based on your findings, I looked further into this issue. Then I noticed the use of a homegrown cf_query tag that wraps the native cfquery. This custom tag sets the default value for maxrows at 100000. Doh!

Never mind!