topic AW:Re: AW:Re: LDAP limit 1000 records in QlikView

LDAP limit 1000 records

Tue, 15 Jun 2010 15:36:49 GMT

Hello @ all,

does anybody knows how I can get more than 1000 records by using an active directory (LDAP source)?

As I know this limit is used in all LDAP queries.

I have no access to the directory so if it is possible, I want to set a parameter.

Thanks

LDAP limit 1000 records

Wed, 16 Jun 2010 05:20:48 GMT

Use this query

First 1000 Load * from table name;

Regards

Ashish

AW:Re: LDAP limit 1000 records

Wed, 16 Jun 2010 07:36:03 GMT

Wrong way. I don't want to limit the records I want to unlimit them because the LDAP returns only 1000 records.

Regards

AW:Re: LDAP limit 1000 records

Wed, 16 Jun 2010 07:49:01 GMT

Your table contain only 1000 record that why load return only 1000

please count no of record in your table.

Regards

Ashish

AW:Re: AW:Re: LDAP limit 1000 records

biester — Wed, 16 Jun 2010 09:14:28 GMT

The reason is obviously the default AD settings, see http://support.microsoft.com/default.aspx?scid=kb;en-us;315071&sd=tech

If you don't have access to the AD, I wonder how you should set the desired parameters.
Perhaps you could try to export AD to csv with csvde (http://www.computerperformance.co.uk/Logon/Logon_CSVDE_switches.htm), but I'm afraid also with this you will have the limitation.

Rgds,
Joachim

AW:Re: AW:Re: LDAP limit 1000 records

Wed, 16 Jun 2010 09:28:56 GMT

I couldn't change the settings in the AD, so I am looking for a parameter in the LDAP-statement.

The csv-download isn't a option for a periodical download.

Thanks anyway

AW:Re: AW:Re: LDAP limit 1000 records

biester — Wed, 16 Jun 2010 09:53:15 GMT

I can't test it here, but try adding e.g. PageSize=5000.

Rgds,
Joachim

AW:Re: AW:Re: LDAP limit 1000 records

Wed, 16 Jun 2010 10:03:21 GMT

no success,

the datas are still limited to 1000 records

AW:Re: AW:Re: LDAP limit 1000 records

biester — Wed, 16 Jun 2010 11:05:24 GMT

OK, you obviously can't use PageSize in the connection string. So you have to query with a trick/loop (see examples http://qcstagingweb.typo3konsult.se/index.php?id=575&tx_mmforum_pi1%5Baction%5D=list_post&tx_mmforum_pi1%5Btid%5D=2192), or you do it as already suggested (also in the mentioned post) by reading AD with an external tool/program/script which you can also execute in the first line of the reload script, so I don't see why it should not be apt for a periodical download.

Rgds,
Joachim

AW:Re: AW:Re: LDAP limit 1000 records

Wed, 16 Jun 2010 12:09:57 GMT

good idea

but......

I tried this

let

LOAD

SQL

'LDAP://AD-definition'

where

upper(left(samAccountName,1))=chr(64+$(x)) and memberOf

= AD-definition';

let

loop

but I didn't get a record. x=x+1; select name, samAccountName, mail, objectGUID, displayname, l from *, 'default' as Gruppe,upper(samAccountName) as %USERID ; while x<27 x=1;

Trying to set the where upper(left(samAccountName,1))=chr(64+$(x)) statement into the load-statement gives only 1000 records, too, because the sql-statement recieves only them.

Any idea abot the sql-statement?

Regards

LDAP limit 1000 records

Fri, 26 Aug 2011 16:17:20 GMT

I've got the same issue. The optimal solution would be to page the results, but I don't know how to do that in the QV script. It'd be nice if QlikTech would weigh in on this one.

My current work around is to limit alphabetically:

SQL SELECT

cn,

displayName,

mail

from 'LDAP://DC=XXXXXX,DC=com'

WHERE objectCategory='person'

and objectClass='user'

and memberOf='CN=DL.XXXXXX,OU=Distribution Groups,OU=Exchange,DC=XXXXXX,DC=com'

AND displayName <= 'E'

;

SQL SELECT

cn,

displayName,

mail

from 'LDAP://DC=XXXXXX,DC=com'

WHERE objectCategory='person'

and objectClass='user'

and memberOf='CN=DL.XXXXXX,OU=Distribution Groups,OU=Exchange,DC=XXXXXX,DC=com'

AND displayName > 'E'

AND displayName <= 'J'

;

ETC....

LDAP limit 1000 records

rwunderlich — Fri, 26 Aug 2011 17:22:05 GMT

Look at the QV Cookbook example "Load from Active Directory - Users only." for a solution to the rowlimit problem.

-Rob

http://robwunderlich.com

Re: LDAP limit 1000 records

qlikviewforum — Tue, 23 Jul 2013 12:01:03 GMT

Hi,

The QVW which tries to load the data from LDAP loads only the partial data frequently. We are unable to find the root cause of the issue as the QVW doesn't fails but it loads only the partial data.Can someone please help us to identify the root cause of the issue. It has been happening from quite a long time but we didn't succeed in finding the root cause of the issue. Hope someone can help us for the same.

Regards,

qvforum

Re: LDAP limit 1000 records

Tue, 12 Nov 2013 14:29:09 GMT

Hi,

The LDAP server is preset to 1000 in maximum number of objects that are returned in a single search result (MaxPageSize).

So, you must to change this using "ntdsutil.exe". The max value is 262,144.

Follow this: How to view and set LDAP policy in Active Directory by using Ntdsutil.exe

Regards,

Rodrigo