Overcoming the OPENQUERY Record Limit

  • stevenb 93057

    Valued Member

    Points: 62

    Comments posted to this topic are about the item Overcoming the OPENQUERY Record Limit

  • Jeff Moden

    SSC Guru

    Points: 994293

    I'm not sure what you're talking about. I use OPENQUERY for sometimes millions of rows all in one shot. There's something else going on for your problem. What driver are you using?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • ThisIsDanny

    SSC Journeyman

    Points: 83

    There is a setting limiting the amount of rows that can be returned via a single query against active directory, 1000 rows by default

    http://support2.microsoft.com/default.aspx?scid=kb;en-us;299410

    You can bypass this by changing the method of returning the data : ADO, vbscript, powershell, reduced resultset etc. Just be aware of the load on the domain controller when running large AD queries (etc. frequency vs duration).

  • Andy Warren

    SSC Guru

    Points: 119675

    Can you post the query you used?

  • woodjerry

    Valued Member

    Points: 66

    Supposing you couldn't get around the 1000 limit, wouldn't it be more ideal to get a record count and using a row_over() function iterate by a predefined chunk size?

  • Matthewgnslue

    SSC Rookie

    Points: 28

    This is not a limitation with OPENQUERY. This is a limitation with LDAP. It is not advised to change the MaxPageSize (to work around this issue).

    I would argue a more elegant and effective solution can be found here:

    https://github.com/mitshel/tech-inv-xe/blob/master/sql/StorProcs/spqueryad.sql

  • jcasement

    SSC Veteran

    Points: 220

    Please change the Title/Subject of this article.

    This has nothing to do with OPENQUERY. It's very misleading. There is no such limit on OPENQUERY.

    Thanks

  • kit-1143032

    SSC Enthusiast

    Points: 115

    Totally agree with that. We run open query all the time and it is definitely not an issue.

  • Evadman

    SSC Enthusiast

    Points: 124

    This confused me because I have a few hundred batches that use openquery to download millions of records each. After reading the article, the fact that this is specific to the LDAP limitation was found. The article title is pretty bad.

    I had this issue before and I just had the LDAP admin dump the directory to LDIF using slapcat.

  • Kick6Tiger

    SSCrazy

    Points: 2368

    Well, at least the title sparked enough interest to get people to read. I guess that's worth something. And yes, the LDAP limit will be a frustration no matter what you use to query...even directly from C# (at least it was way back in the day when I actually wrote code).

    Aigle de Guerre!

  • brianchristopherbrown

    SSC Enthusiast

    Points: 178

    I realize that, by now, there is a consensus that the limit is imposed by LDAP and not OPENQUERY.

    However, in a previous job experience I was asked to get around the 8000-character limit imposed by OPENQUERY, and I think the same solution may apply here as well.

    Here goes: I created a SQLCLR assembly that accepted my query as nvarchar(MAX) did the querying for me. I could pass in a query that was (practically) of any length, and it would stream records back to the database ad nauseum.

    I think this workaround would bypass your LDAP limitation, but the truth is I never had anything related to LDAP come up at the time, so I don't know for certain.

    - Brian from Chicago

  • Jeff Moden

    SSC Guru

    Points: 994293

    Perhaps it's not even an LDAP limit. Perhaps it's as simple as a DOS buffer limit, which can be easily overcome though the use of the ancient, largely unknown, but very effective DOS MODE command which allows you to easily change the buffer size. It's also works for some things in PowerShell although I've run into a 128 character limit there on some things before. If anyone knows how to beat that on the screen instead of the clever trickery of outputting to a file, I'm all ears.

    Here's and example of the DOS MODE command that I was talking about...

    MODE CON COLS=8000 LINES=6000 COL

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Robert-378556

    SSCertifiable

    Points: 5382

    What's wrong with output to a file/device/pipe?

    It was a common practice in the age of dos.

    It's still a common practice on unix systems.

    Most modern windows users have the black-textmode-window-fobia.

    Interestingly, they use the same term "clever trickery", when they see one :hehe:

    "You type commands? You learn all commands and their syntax?"

    Same sort of attitude is toward sql and is the reason for popularity of tools that hide the sql trickery.

    Sorry, off topic.

  • MarbryHardin

    Old Hand

    Points: 333

    You just need to pick a single unique field like user name, specify an order by on that, then you can just use the last value returned as the input to a > @LastValue for the next iteration. When you return < the max records you're done.

    Another limiter could be in the LDAP query itself, you could break it out by OU for instance into separate queries.

    It would probably be prudent, if possible for your implementation, to do this in a scheduled process to load to SQL rather than hitting it live also.

  • MarbryHardin

    Old Hand

    Points: 333

    Jeff Moden (11/13/2014)


    Perhaps it's not even an LDAP limit. Perhaps it's as simple as a DOS buffer limit, which can be easily overcome though the use of the ancient, largely unknown, but very effective DOS MODE command which allows you to easily change the buffer size. It's also works for some things in PowerShell although I've run into a 128 character limit there on some things before. If anyone knows how to beat that on the screen instead of the clever trickery of outputting to a file, I'm all ears.

    Here's and example of the DOS MODE command that I was talking about...

    MODE CON COLS=8000 LINES=6000 COL

    I'm not sure exactly where the limitation actually lies, but I've been admonished by admins before to not try and bypass that as it can cause performance issues with AD.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply