2

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?

Improve this question
6
  • 2
    What is the error message? By default SQL server returns everything you asked for unless you restrict with Top, Offset/Fetch, Join etc. Using those should not cause an error. Are you sure it is not a timeout error?
    – SqlWorldWide
    Jul 21, 2017 at 2:02
  • 1
    @SqlWorldWide Thanks for the response - I don't have a real error message to go from, hence searching for the query limiting is a shot in the dark. It's happening too quickly to be a timeout. Behaviour based on number of rows is all I know at the time. Good to know there's no arbitrary restrictions, thanks.
    – Beeblebrox
    Jul 21, 2017 at 2:08
  • 2
    You have contradicting statements: The calling system is receiving a DB error in which... (question) and I don't have a real error message... (comment). Could you please clarify a bit? Even if it is an application error it might help to solve your issues. Is there any other information you could share with us? SQL Server version. Client software. Application type (.Net, Delphi, VB, C#). It might be an error that can be solved on Stack Overflow. Unless you don't want us to solve the error just provide you with a solution. Select top (70000) * from table;
    – John K. N.
    Jul 21, 2017 at 5:01
  • 1
    @hot2use Thanks for reading, I can see why that is confusing. Slightly paraphrasing the message but it's 'a select error has occurred but no error message was returned by the server' - not much room for investigation there. The reason I'm shying away from that is that I don't know if that's coming from the SQL server, the app or even a driver. At this stage I have ruled out permission issues and network connectivity + found a working use case, so am interested in narrowing it down to potential SQL server config. Cheers.
    – Beeblebrox
    Jul 21, 2017 at 5:12
  • 1
    It's possibly being cutoff by the Remote Query Timeout setting on the remote system.
    – RBarryYoung
    Jul 21, 2017 at 14:10

1 Answer 1

Reset to default
2

You can use OFFSET and FETCH to effectively page through results

e.g.

SELECT * FROM MyTable ORDER BY MyColumn OFFSET 100 FETCH NEXT 100 ROWS ONLY

Helpful article here...

Article About OFFSET FETCH

Though i would look into why it fails to return large result sets 50k isn't a lot in modern data.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.