2

I have created a linked server to AD in SQL Server, and tried some logic but I'm not able to retrieve more than 901 records from AD.

This is my query - I have used top 900 so that the query doesn't throw errors + records

SELECT TOP 900 
   UPPER(sAMAccountName)[EmpCode], 
   givenName [FirstName],
   ISNULL(sn, '') [LastName],
   ISNULL(mail, 'NA') [Email],
   telephoneNumber [Telephone], cn [DisplayName],
   Mobile, title [Designation],
   CASE 
       WHEN UserAccountControl = 512 THEN 'Active' 
       WHEN UserAccountControl = 514 THEN 'In-Active' 
       WHEN UserAccountControl = 66048 THEN 'Active, password never expires' 
       WHEN UserAccountControl = 66050 THEN 'In-Active, password never expires'  
       ELSE 'NA' 
   END [Status],
   UserAccountControl,
   UPPER(ISNULL(physicalDeliveryOfficeName, 'NA')) [Office]
--into #temp 
FROM 
    OPENQUERY(ADSI, 
              'SELECT sAMAccountName, givenName, sn, mail, l, St, postalCode, co, telephoneNumber, cn, Mobile, title, UserAccountControl, physicalDeliveryOfficeName
               FROM ''LDAP://domain.local/OU=Company,DC=domain,DC=local'' where objectClass = ''User'' ')

-- I added this line to get records apart from records I already have
WHERE 
    sAMAccountName NOT IN (SELECT code FROM tbl_usermaster)

Any loop or any other logic will be helpful. Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
spruless
  • 21
  • 3

0 Answers0