[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


limitaion on number of rows returned with OPENQUERY

Posted on 2007-10-11
Medium Priority
Last Modified: 2012-05-05
am using sql server 2005. Is there any limitation when I use the openquery option ? If so is there a work around?

Question by:raGadiraju
  • 3
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20061148
as openquery passes the query to the underlying driver/provider, you have to use the syntax of that engine.
if you use openquery to a oracle database, use the rownum to limit, with mysql it would be the limit syntax, if it is another sql server, it would be top (inside the query you pass in the openquery):

select * from openquery(linked_server, 'select * from yourtable where rownum <= 10' ) l


Author Comment

ID: 20061172
Let me explain the problem more clearly. I want all the row (over 8000 rows). However I am getting only 1000 rows. I am querying windows active directory to get all the employees.

LVL 18

Accepted Solution

Yveau earned 1500 total points
ID: 20061194
[...] This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

hope this helps ...
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20061212
see in the following page:

Hermann Croucamp has developed an idea + script to get around that limit, which seems to come from the ADSI provider...

hope this helps
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20061217
maybe you accepted too fast :)
LVL 18

Expert Comment

ID: 20061236
A split seems to be in place here :-)
But anyway, Glad I could be of any help !

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question