I have an issue where a system on our network is making a query (select * from table) which might result in 0 to 70k rows being returned.
For large result sets, say around 50k, the calling system is being notified that a DB error has occurred & the query is terminated (presumably by the server). It’s working with smaller result sets. The exact ‘tipping point’ hasn’t yet been identified.
Is there a config setting in SQL Server 2012 which sets a max limit on how many rows will be returned for a query?
You can use OFFSET and FETCH to effectively page through results
SELECT * FROM MyTable ORDER BY MyColumn OFFSET 100 FETCH NEXT 100 ROWS ONLY
Helpful article here…
Though i would look into why it fails to return large result sets 50k isn’t a lot in modern data.