How to import Active Directory data into a SQL Server table using query?

Question

CREATE TABLE T_AD_Data
(
    lab_sAMAccountName varchar(100),
    lab_displayName varchar(100),
    lab_department varchar(100),
    lab_physicalDeliveryOfficeName varchar(100), 
);

INSERT INTO T_AD_Data
   SELECT * 
   FROM OpenQuery (ADSI,  
                   'SELECT sAMAccountName, displayName, department, physicalDeliveryOfficeName 
                    FROM ''LDAP://lab.com/DC=lab,DC=com'' 
                    WHERE objectClass =  ''User'' ') AS tblADSI

Error:

Msg 7330, level 16, state 2, Line 1
Cannot fetch a row from OLE DBprovider "ADSDSOObject" for linked server "ADSI"

Answer 1

I know this is a very old question, but the problem you've hit is caused by the ADSI query returning more than 901 records. Prior to SQL 2008 this didn't cause a problem, the number of records was just limited. But on SQL 2008 or later, when you hit this limit instead of the results being truncated, a runtime error occours.

Work around this problem with some sort of paging solution that uses multiple queries, like in my answer here: https://stackoverflow.com/a/43057890/197090