Overcoming the OPENQUERY Record Limit

Question

Overcoming the OPENQUERY Record Limit

stevenb 93057

Valued Member

Points: 62
More actions
November 13, 2014 at 12:08 am

#397637

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

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

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

ThisIsDanny SSC Journeyman Points: 83 More actions · Answer 2

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: 119902 More actions · Answer 3

Can you post the query you used?

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

woodjerry Valued Member Points: 66 More actions · Answer 4

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 Veteran Points: 200 More actions · Answer 5

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: 280 More actions · Answer 6

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 More actions · Answer 7

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

Evadman SSC Enthusiast Points: 124 More actions · Answer 8

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: 2380 More actions · Answer 9

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 More actions · Answer 10

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: 1004323 More actions · Answer 11

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.

Change is inevitable... Change for the better is not.

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

Robert-378556 SSCertifiable Points: 5917 More actions · Answer 12

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: 363 More actions · Answer 13

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: 363 More actions · Answer 14

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.