Problem with export Active Directory

[qlikconsultant]

I have the following code:

LET arg=chr(01);	// Set search start name to a very low value
Do 
	ADUsers:
	First 1000		// Workaround for undefined SQL error. Don't forget to sort the entries, so the loop continues
	Load Distinct
		// Add addtional Fields As required
		name As UserName,
		sn As Nachname,
		givenName As Vorname,
		mail As Email,
		telephonenumber As Festnetz,
		mobile As Mobilnummer,
		manager,
		company As Firma,
		department As Abteilung,
		title As Position,
		lastLogonTimestamp As Letzter_Login,
		lastLogon,
		userAccountControl As Status,
		otherPager,
		physicalDeliveryOfficeName as Office
	;
	SQL Select 
		//Fields to select 
		//Add addtional Fields As required
		name, 
		sn,
		givenName,
		distinguishedName,
		mail,
		telephonenumber,
		mobile,
		manager,
		company,
		department,
		title,
		objectCategory,
		objectClass,
		lastLogonTimestamp,
		lastLogon,
		userAccountControl,
		otherPager,
		physicalDeliveryOfficeName
		
	From 
		'LDAP://$(RootDse)'  
	Where 
		objectCategory='person' 
		And objectClass = 'user'  
		And name>'$(arg)'	// Get rows where "name" is GT the arg
	Order by 
		name
	; 
	
	Exit Do When ScriptError > 1	// Stop loop if SELECT has error
	Exit Do When NoOfRows('nameTable') = 0;  // Stop loop if SELECT returns nothing
	Exit Do When Peek('UserName') = '$(arg)';  // If the last "name" read is EQ to arg -- no more entries
	
	LET arg=peek('UserName');	// Set the arg to the last "name" read
LOOP

This works fine! And i have the following code:

LET arg=chr(01);	// Set search start name to a very low value
DO 
	ADGroups:
	LOAD Distinct
		name As GroupName,
		distinguishedName As GroupDN,
		info As GroupInfo
	;
	SQL SELECT 
		name, 
		distinguishedName,
		info
	FROM 'LDAP://$(RootDse)'  
	WHERE objectCategory='group'
		  AND name>'$(arg)' // Get rows where "name" is GT the arg
	Order by 
		name
	;	
	
	EXIT DO WHEN ScriptError > 1	// Stop loop if SELECT has error
	EXIT DO WHEN NoOfRows('nameTable') = 0;  // Stop loop if SELECT returns nothing
	EXIT DO WHEN peek('GroupName') = '$(arg)';  // If the last "name" read is EQ to arg -- no more entries
	
	LET arg=peek('GroupName');	// Set the arg to the last "name" read
LOOP

 Here i get the following script error:

EQ_QVX_SIZELIMIT_EXCEEDED: The size limit for this request was exceeded.
This query was stopped because it tried to read beyond a restricted limit. One of the tables in the query has a limit of maximum 5000 rows.
ADGroups:
LOAD Distinct
name As GroupName,
distinguishedName As GroupDN,
info As GroupInfo

Any ideas how I can fix that?

[AndyC]

The first script has 'relief valve' FIRST 1000 which stops the query from returning too many rows at once.

The code at the bottom is used to pass back the last vale into the select so that the DO loop keeps rolling.

AND name>'$(arg)'

I think you just need to include FIRST 1000 in your second script (same as the first).

[qlikconsultant]

Hi,

thanks, I put First 1000 in the code

LET arg=chr(01);	// Set search start name to a very low value
DO 
	ADGroups:
	First 1000		// Workaround for undefined SQL error. Don't forget to sort the entries, so the loop continues
	LOAD Distinct
		name As GroupName,
		distinguishedName As GroupDN,
		info As GroupInfo
	;

 but same Error message.