Russ,
This is a limitation of your LDAP server. It can be changed yet the network admins probably would not want to increase the limit on the LDAP server because of performance. However, there is always another way... Although you are limited to 500 records from LDAP you are not limited to the number of queries. By using a SQL View you can return every record from LDAP. Attached is a view which I used at one point. It breaks down the query into 26 sub queries - each by the first letter of the user's names. Because you have 60K records you would need to further refine the sub queries to never match more than 500 records. Big caveat: You should only use this type of query sparingly and with 60K records, probably only after hours. This is because the SQL View will first return all 60K records to your SQL Server then extract out the matching records (WHERE clause) and return them to your query screen (or ARS View form). Example: You want to see a list of the people whose login names start with the letter R. You run the following command: SELECT sAMAccountName FROM udv_LDAP WHERE sAMAccountName LIKE 'R%' SQL Server will first retrieve all 60,000 records from LDAP (ouch). Then extract out the 100 login names that match your WHERE clause and display them. Just wanted you to be aware. One situation where it is beneficial to retrieve all records is when you need to initially populate an ARS form with LDAP data. If you need to do this every day then you will likely want to schedule this to run when no-one is on the system and the backups are not running (depending upon your topology). Windows Server 2003 Active Directory SQL Server 2000 Stephen Remedy Skilled Professional ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Russ Grant Sent: Wednesday, January 09, 2008 9:46 AM To: arslist@ARSLIST.ORG Subject: LDAP Question Greetings ARList! I'm connecting to a LDAP server using ARDBC LDAP plug-in and then creating a vendor form for workflow. For some reason when I query the vendor form I can only get the first 500 records returned and there should be well over 60k records. There is some history here, I've been using ARDBC LDAP plug-in to connect to a local LDAP server for some time now and I haven't had any problems, I'm able to perform a query on the vendor form and get the correct number of records returned. One of the applications that we use locally now needs to expand to other sites so I need to connect to a global LDAP server instead of the local LDAP server (which doesn't include employees outside our site). This is were I'm having a problem, connecting to the global LDAP server using ARDBC only returns the top 500 records. I realize this probably isn't a Remedy problem but I wanted to see if there were any LDAP gurus that might have a suggestion. Thanks in advance! Russ __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
CREATE view [dbo].[udv_LDAP] as SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''A*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''B*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''C*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''D*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''E*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''F*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''G*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''H*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''I*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''J*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''K*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''L*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''M*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''N*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''O*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''P*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''Q*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''R*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''S*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''T*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''U*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''V*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''W*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''X*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''Y*''') UNION SELECT * FROM OpenQuery(ADSI, 'SELECT PwdLastSet, GivenName, sn, sAMAccountName, cn, mail, displayName, department, telephoneNumber, streetAddress, st, distinguishedName, physicalDeliveryOfficeName, userPrincipalName, mailNickname, l, postalCode, msExchHomeServerName, whenCreated, whenChanged, userAccountControl FROM ''LDAP://YOURDCSERVER'' WHERE objectClass=''User'' AND objectCategory=''Person'' AND sn=''Z*''')
