How to query Active Directory to fetch more than 1000 rows using Sql Server–Part I

| 14 Comments

Recently there was a requirement from one of my customers to query Active Directory and load the data into a table in sql server. To be honest I am not developer so I started researching on the topics over the net and took help of some of the internal resources which I had access to. So in this Blog post I am listing the different ways to query AD from sql server.

First and the most easiest way of querying AD is by creating a Linked Server.

Using Linked Server
===============

We create a Linked Server from Sql Server to connect to the Active Directory and then we can use the Linked server to perform OpenQuery. The complete method is described in Books Online and the same can be found at the following link

http://msdn.microsoft.com/en-us/library/aa772380(VS.85).aspx

The above approach is good but it works only when the number of rows returned from AD is less than 1000. If we want to query more than 1000 rows the above approach wont work because of the limitation of pagesize in AD which Is set to 1000 by default.

We can change the PageSize at the AD but there can be performance implications and hence we did not take that approach.

In our case the Linked server approach did not work since we wanted to query more than 1000 rows. So we researched over the internet and found the following Stored Procedure in one of the Forums.

Use COM Objects
===============

I would like to share the Stored Procedure which uses COM objects within sql server using the system sps viz sp_Oacreate, sp_OAMethod, sp_OAgetproperty and sp_OAsetproperty.

I could not find the Author of the Stored Proc so I apologize as I cannot provide credit to right and deserving person.

Note: In order to use the below Stored Procedure we need to enable “Ole Automation Procedures’ using sp_configure as shown

sp_configure ‘Ole Automation Procedures’,1
reconfigure

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spQueryAD]    Script Date: 01/13/2010 09:14:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spQueryAD] (@LDAP_Query varchar(255)=”, @Verbose bit=0)
as

–verify proper usage and display help if not used properly
if @LDAP_Query =” –argument was not passed
BEGIN
Print ”
Print ‘spQueryAD is a stored procedure to query active directory without the default 1000 record LDAP query limit’
Print ”
Print ‘usage — Exec spQueryAD ”_LDAP_Query_”, Verbose_Output(0 or 1, optional)’
Print ”
Print ‘example: Exec spQueryAD ”SELECT EmployeeID, SamAccountName FROM ””LDAP://dc=domain,dc=com”” WHERE objectCategory=””person”” and objectclass=””user”””, 1’
Print ”
Print ‘spQueryAD returns records corresponding to fields specified in LDAP query.’
Print ‘Use INSERT INTO statement to capture results in temp table.’
Return –‘spQueryAD aborted’
END

–declare variables
DECLARE @ADOconn INT — ADO Connection object
, @ADOcomm INT — ADO Command object
, @ADOcommprop INT — ADO Command object properties pointer
, @ADOcommpropVal INT — ADO Command object properties value pointer
, @ADOrs INT — ADO RecordSet object
, @OLEreturn INT — OLE return value
, @src varchar(255) — OLE Error Source
, @desc varchar(255) — OLE Error Description
, @PageSize INT — variable for paging size Setting
, @StatusStr char(255) — variable for current status message for verbose output
SET @PageSize = 1000 — IF not SET LDAP query will return max of 1000 rows

–Create the ADO connection object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Create ADO connection…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OACreate ‘ADODB.Connection’, @ADOconn OUT
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the provider property to ADsDSOObject to point to Active Directory
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ADO connection to use Active Directory driver…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OASETProperty @ADOconn , ‘Provider’, ‘ADsDSOObject’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Open the ADO connection
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Open the ADO connection…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OAMethod @ADOconn , ‘Open’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Create the ADO command object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Create ADO command object…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OACreate ‘ADODB.Command’, @ADOcomm OUT
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the ADO command object to use the connection object created first
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ADO command object to use Active Directory connection…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘ActiveConnection’, ‘Provider=”ADsDSOObject”’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Get a pointer to the properties SET of the ADO Command Object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Retrieve ADO command properties…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OAGetProperty @ADOcomm, ‘Properties’, @ADOcommprop out
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the PageSize property
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ”PageSize” property…’
Print @StatusStr
END
IF (@PageSize IS NOT null) — If PageSize is SET then SET the value
BEGIN
EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Page Size’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,’1000′
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the SearchScope property to ADS_SCOPE_SUBTREE to search the entire subtree
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ”SearchScope” property…’
Print @StatusStr
END
BEGIN
EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘SearchScope’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,’2′ –ADS_SCOPE_SUBTREE
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the Asynchronous property to True
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Set ”Asynchronous” property…’
Print @StatusStr
END
BEGIN
EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Asynchronous’
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,True
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Create the ADO Recordset to hold the results of the LDAP query
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Create the temporary ADO recordset for query output…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OACreate ‘ADODB.RecordSET’,@ADOrs out
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Pass the LDAP query to the ADO command object
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Input the LDAP query…’
Print @StatusStr
END
EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘CommandText’, @LDAP_Query
IF @OLEreturn <> 0
BEGIN — Return OLE error
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END
IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Run the LDAP query and output the results to the ADO Recordset
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Execute the LDAP query…’
Print @StatusStr
END
Exec @OLEreturn = sp_OAMethod @ADOcomm, ‘Execute’ ,@ADOrs OUT
IF @OLEreturn <> 0
BEGIN — Return OLE error
print ‘Error in Execute clause of SP_OAMethod’
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END

–Return the rows found
IF @Verbose=1
BEGIN
Set @StatusStr = ‘Retrieve the LDAP query results…’
Print @StatusStr
END

DECLARE @pwdlastset varchar(8)

EXEC @OLEreturn = sp_OAMethod @ADOrs, ‘getrows’
IF @OLEreturn <> 0
BEGIN — Return OLE error
Print ‘Error in Getstring of getproperty’
EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
SELECT Error=CONVERT(varbinary(4),@OLEreturn), Source=@src, Description=@desc
RETURN
END

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

Using we create the above Stored Procedure. We can use the above stored procedure to query AD using the following query

exec spqueryad ‘SELECT networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,CN FROM ”LDAP://DC=CONTOSO, DC=com”WHERE objectClass=”computer”’,1

You can substitute your own query while calling the above stored procedure and it should return the desired output. However if try to query pwdlastset using the above Stored Procedure the above query will fail with following error

exec spqueryad ‘SELECT pwdlastset,networkAddress,OperatingSystemServicePack,OperatingSystem,DNSHostName,CN FROM ”LDAP://DC=CONTOSO, DC=com”WHERE objectClass=”computer”’,1

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

The above error occurs because sql server is unable to represent pwdlastset in the output as it is returned by AD in Integer8Date format which is a 64bit long integer. So we cannot use the above stored procedure when we query from AD filed like pwdlastset which are of Interger8Date format. For all the field the above stored proc works fine

So if your requirement is to query AD with more than 1000 rows and pwdlastset is one of the fields you wish to query from AD please follow my next post which is

http://www.sqlserverfaq.net/2010/09/19/how-to-query-active-directory-to-fetch-more-than-1000-rows-using-sql-serverpart-i/

Updated Code From Jonathan Mears
===========================

ALTER procedure [dbo].[pr_QueryAD] 
( @LDAP_Query varchar(255)=”,     
  @Verbose bit=0,     
  @Error_Message varchar(250)=null output ) 
as 
begin 
/* from http://www.sqlserverfaq.net/2010/09/20/how-to-query-active-directory-to-fetch-more-than-1000-rows-using-sql-serverpart-i-2/ */     

set @Error_Message = null;

--verify proper usage and display help if not used properly     
if @LDAP_Query = ” –argument was not passed    
 begin   Print ” Print ‘spQueryAD is a stored procedure to query active directory without the default 1000 record LDAP query limit’ Print ” Print ‘usage — Exec spQueryAD ”_LDAP_Query_”, Verbose_Output(0 or 1, optional)’ Print ” 
Print ‘example: Exec spQueryAD ”SELECT EmployeeID, SamAccountName FROM ””LDAP://dc=domain,dc=com”” WHERE objectCategory=””person”” and objectclass=””user”””, 1? 
Print ” Print ‘spQueryAD returns records corresponding to fields specified in LDAP query.’ 
Print ‘Use INSERT INTO statement to capture results in temp table.’ Return 1    
end

--declare variables     
declare @i int; –-Misc     
declare @ADOconn INT — ADO Connection object     
declare @ADOcomm INT — ADO Command object     
declare @ADOcommprop INT — ADO Command object properties pointer     
declare @ADOcommpropVal INT — ADO Command object properties value pointer     
declare @ADOrs INT — ADO RecordSet object     
declare @OLEreturn INT — OLE return value     
declare @src varchar(255) — OLE Error Source     
declare @desc varchar(255) — OLE Error Description     
declare @PageSize varchar(6) — variable for paging size Setting     
declare @StatusStr char(255) — variable for current status message for verbose output

set @PageSize = ’1000?; — IF not SET LDAP query will return max of 1000 rows; page size is the amount of data returned per “PAGE”, not the total number of rows

–Create the ADO connection object     
IF @Verbose=1     
BEGIN 
        Set @StatusStr = ‘Create ADO connection…’ 
        Print @StatusStr  
END

EXEC @OLEreturn = sp_OACreate ‘ADODB.Connection’, @ADOconn OUT     
IF @OLEreturn  0     
     BEGIN — Return OLE error         
     EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
     goto OA_ERROR 
END

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the provider property to ADsDSOObject to point to Active Directory
IF @Verbose=1     
    BEGIN         
      Set @StatusStr = ‘Set ADO connection to use Active Directory driver…’ 
      Print @StatusStr
    END

EXEC @OLEreturn = sp_OASETProperty @ADOconn , ‘Provider’, ‘ADsDSOObject’ 
IF @OLEreturn  0     
  BEGIN — Return OLE error         
  EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT         
  goto OA_ERROR      
END      

IF @Verbose=1 
   Print Space(len(@StatusStr)) + ‘done.’
–Open the ADO connection     

IF @Verbose=1     
  BEGIN          
 Set @StatusStr = ‘Open the ADO connection…’ 
 Print @StatusStr     
END     

EXEC @OLEreturn = sp_OAMethod @ADOconn , ‘Open’ 
  IF @OLEreturn  0     
BEGIN — Return OLE error         
  EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT         
  goto OA_ERROR     
END     

IF @Verbose=1 
  Print Space(len(@StatusStr)) + ‘done.’
  –Create the ADO command object     

IF @Verbose=1     
      BEGIN         
      Set @StatusStr = ‘Create ADO command object…’ 
      Print @StatusStr     
      END     

     EXEC @OLEreturn = sp_OACreate ‘ADODB.Command’, @ADOcomm OUT     
    IF @OLEreturn  0     
      BEGIN — Return OLE error         
      EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT         
      goto OA_ERROR     
    END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the ADO command object to use the connection object created first
IF @Verbose=1
     BEGIN         
     Set @StatusStr = ‘Set ADO command object to use Active Directory connection…’ 
     Print @StatusStr     
     END

EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘ActiveConnection’, ‘Provider=”ADsDSOObject”’ 

IF @OLEreturn  0         
    BEGIN — Return OLE error         
    EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
    goto OA_ERROR     
    END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Get a pointer to the properties SET of the ADO Command Object 

IF @Verbose=1     
  BEGIN         
  Set @StatusStr = ‘Retrieve ADO command properties…’ Print @StatusStr     
  END     
  EXEC @OLEreturn = sp_OAGetProperty @ADOcomm, ‘Properties’, @ADOcommprop out     
IF @OLEreturn  0     
  BEGIN — Return OLE error         
  EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT         
  goto OA_ERROR     
END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the PageSize property 
IF @Verbose=1     
  BEGIN         
  Set @StatusStr = ‘Set ”PageSize” property…’ 
  Print @StatusStr     
END     

IF (@PageSize IS NOT null) — If PageSize is SET then SET the value     
 BEGIN         
 EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Page Size’ 
  IF @OLEreturn  0         
      BEGIN — Return OLE error
             EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
             goto OA_ERROR
      END    
     EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,@PageSize
         IF @OLEreturn  0
         BEGIN — Return OLE error 
            EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
            goto OA_ERROR
         END

exec @OLEreturn = sp_OADestroy @ADOcommpropVal; 
END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–SET the SearchScope property to ADS_SCOPE_SUBTREE to search the entire subtree     
IF @Verbose=1     
    BEGIN         
    Set @StatusStr = ‘Set ”SearchScope” property…’ 
    Print @StatusStr     
    END

BEGIN
         EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘SearchScope’ 
         IF @OLEreturn  0
         BEGIN — Return OLE error 
            EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT             
            goto OA_ERROR
         END  
         EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,’2? –ADS_SCOPE_SUBTREE         

IF @OLEreturn  0         
   BEGIN — Return OLE error             
   EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT             
   goto OA_ERROR         
   END         

exec @OLEreturn = sp_OADestroy @ADOcommpropVal;     
END     

IF @Verbose=1 
Print Space(len(@StatusStr)) + ‘done.’

–SET the Asynchronous property to True     

IF @Verbose=1     
   BEGIN         Set @StatusStr = ‘Set ”Asynchronous” property…’ Print @StatusStr     
END

BEGIN         
 EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Asynchronous’ 
 IF @OLEreturn  0
         BEGIN — Return OLE error            
         EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
         goto OA_ERROR         
  END         

EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,True         

IF @OLEreturn  0         
    BEGIN — Return OLE error             
    EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT             
     goto OA_ERROR        
END         

exec @OLEreturn = sp_OADestroy @ADOcommpropVal;     
END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Create the ADO Recordset to hold the results of the LDAP query –This was in orignal code but other Web examples indicate that it is not required;  Execute will do this; –note that in orignal code @ADOrs is used as an output object twice /*     IF @Verbose=1    
 BEGIN         
Set @StatusStr = ‘Create the temporary ADO recordset for query output…’ 
Print @StatusStr     
END     

EXEC @OLEreturn = sp_OACreate ‘ADODB.RecordSET’,@ADOrs out     

IF @OLEreturn  0     BEGIN — Return OLE error         
 EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT         
 goto OA_ERROR     
 END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’ */

–Pass the LDAP query to the ADO command object     

IF @Verbose=1     
BEGIN         
Set @StatusStr = ‘Input the LDAP query…’ Print @StatusStr     
END     

EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘CommandText’, @LDAP_Query     
IF @OLEreturn  0     
BEGIN — Return OLE error         
EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT         
goto OA_ERROR     
END     

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

–Run the LDAP query and output the results to the ADO Recordset     IF @Verbose=1     BEGIN         Set @StatusStr = ‘Execute the LDAP query…’ Print @StatusStr     END     Exec @OLEreturn = sp_OAMethod @ADOcomm, ‘Execute’ ,@ADOrs OUT     IF @OLEreturn  0     BEGIN — Return OLE error         IF @Verbose=1 print ‘Error in Execute clause of SP_OAMethod’ EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT         goto OA_ERROR     END

–Return the rows found     IF @Verbose=1     BEGIN         Set @StatusStr = ‘Retrieve the LDAP query results…’ Print @StatusStr     END

DECLARE @pwdlastset varchar(8)

EXEC @OLEreturn = sp_OAMethod @ADOrs, ‘getrows’ IF @OLEreturn  0     BEGIN — Return OLE error         IF @Verbose = 1 Print ‘Error in Getstring of getproperty’ EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT         goto OA_ERROR     END

IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

DONE:     EXEC @i = sp_OAMethod @ADOrs, ‘Close’ if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar);     EXEC @i = sp_OAMethod @ADOconn, ‘Close’ if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar);

exec @i = sp_OADestroy @ADOcommprop;     if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar);     exec @i = sp_OADestroy @ADOrs;     if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar);     exec @i = sp_OADestroy @ADOcomm;     if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar);     exec @i = sp_OADestroy @ADOconn;     if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar);

return 0

OA_ERROR:     SET @Error_Message = ‘Error=’ + isnull(cast(@OLEreturn as varchar),’?') + ‘/Src=’ + isnull(@src,’?') + ‘/Description=’ + isnull(@desc,’?'); if @Verbose=1 Print @Error_Message;

EXEC @i = sp_OAMethod @ADOrs, ‘Close’ if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar);     EXEC @i = sp_OAMethod @ADOconn, ‘Close’ if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar);

exec @i = sp_OADestroy @ADOcommprop;     if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar);     exec @i = sp_OADestroy @ADOrs;     if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar);     exec @i = sp_OADestroy @ADOcomm;     if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar);     exec @i = sp_OADestroy @ADOconn;     if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar);

return @OLEreturn

END;

Parikshit Savjani
Premier Field Engineer,Microsoft Services

14 Comments

  1. Pingback: Joining Active Directory Data with SQL Server Data: Cannot fetch a row from OLE DB provider “YourProvider” for linked server “YourLinkedServer” « Web Active Directory Blog

  2. Hello excellent info, i have a question, Is possible to create users in active directory since the information of a table’s database mssql?

  3. Hi,
    This sp looks great, and I was attempting to get it to work. The one problem I noticed is that it looks like the following line is incomplete from above:
    EXEC @OLEreturn = sp_OAMethod @ADOrs, ‘getrows’, —-

    I’d love to give this sp a try, but I can’t seem to figure what the missing code is for that line. Could you help?

  4. I have rectified this…you should be able to use it now without errors

  5. Parikshit,
    I really appreciated find this code example. I started running it with many calls within a single SQL command execution and started getting failures that I tied back to too many OLE objects. I have added “garbage collection” to it and the errors are not gone. There were two objects obtained using @ADOrs; it seems to work fine without the sp_OACreate ‘ADODB.RecordSET’

    ALTER procedure [dbo].[pr_QueryAD] (
    @LDAP_Query varchar(255)=”,
    @Verbose bit=0,
    @Error_Message varchar(250)=null output
    )
    as
    begin
    /*
    from
    http://www.sqlserverfaq.net/2010/09/20/how-to-query-active-directory-to-fetch-more-than-1000-rows-using-sql-serverpart-i-2/
    */
    set @Error_Message = null;

    –verify proper usage and display help if not used properly
    if @LDAP_Query = ” –argument was not passed
    begin
    Print ”
    Print ‘spQueryAD is a stored procedure to query active directory without the default 1000 record LDAP query limit’
    Print ”
    Print ‘usage — Exec spQueryAD ”_LDAP_Query_”, Verbose_Output(0 or 1, optional)’
    Print ”
    Print ‘example: Exec spQueryAD ”SELECT EmployeeID, SamAccountName FROM ””LDAP://dc=domain,dc=com”” WHERE objectCategory=””person”” and objectclass=””user”””, 1’
    Print ”
    Print ‘spQueryAD returns records corresponding to fields specified in LDAP query.’
    Print ‘Use INSERT INTO statement to capture results in temp table.’
    Return 1
    end

    –declare variables
    declare @i int; –Misc
    declare @ADOconn INT — ADO Connection object
    declare @ADOcomm INT — ADO Command object
    declare @ADOcommprop INT — ADO Command object properties pointer
    declare @ADOcommpropVal INT — ADO Command object properties value pointer
    declare @ADOrs INT — ADO RecordSet object
    declare @OLEreturn INT — OLE return value
    declare @src varchar(255) — OLE Error Source
    declare @desc varchar(255) — OLE Error Description
    declare @PageSize varchar(6) — variable for paging size Setting
    declare @StatusStr char(255) — variable for current status message for verbose output

    set @PageSize = ‘1000’; — IF not SET LDAP query will return max of 1000 rows; page size is the amount of data returned per “PAGE”, not the total number of rows

    –Create the ADO connection object
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Create ADO connection…’
    Print @StatusStr
    END

    EXEC @OLEreturn = sp_OACreate ‘ADODB.Connection’, @ADOconn OUT
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
    goto OA_ERROR
    END

    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –SET the provider property to ADsDSOObject to point to Active Directory
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Set ADO connection to use Active Directory driver…’
    Print @StatusStr
    END

    EXEC @OLEreturn = sp_OASETProperty @ADOconn , ‘Provider’, ‘ADsDSOObject’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –Open the ADO connection
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Open the ADO connection…’
    Print @StatusStr
    END
    EXEC @OLEreturn = sp_OAMethod @ADOconn , ‘Open’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOconn , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –Create the ADO command object
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Create ADO command object…’
    Print @StatusStr
    END
    EXEC @OLEreturn = sp_OACreate ‘ADODB.Command’, @ADOcomm OUT
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –SET the ADO command object to use the connection object created first
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Set ADO command object to use Active Directory connection…’
    Print @StatusStr
    END

    EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘ActiveConnection’, ‘Provider=”ADsDSOObject”’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –Get a pointer to the properties SET of the ADO Command Object
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Retrieve ADO command properties…’
    Print @StatusStr
    END
    EXEC @OLEreturn = sp_OAGetProperty @ADOcomm, ‘Properties’, @ADOcommprop out
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –SET the PageSize property
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Set ”PageSize” property…’
    Print @StatusStr
    END
    IF (@PageSize IS NOT null) — If PageSize is SET then SET the value
    BEGIN
    EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Page Size’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
    goto OA_ERROR
    END
    EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,@PageSize
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
    goto OA_ERROR
    END

    exec @OLEreturn = sp_OADestroy @ADOcommpropVal;
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –SET the SearchScope property to ADS_SCOPE_SUBTREE to search the entire subtree
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Set ”SearchScope” property…’
    Print @StatusStr
    END

    BEGIN
    EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘SearchScope’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
    goto OA_ERROR
    END
    EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,’2′ –ADS_SCOPE_SUBTREE
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
    goto OA_ERROR
    END
    exec @OLEreturn = sp_OADestroy @ADOcommpropVal;
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –SET the Asynchronous property to True
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Set ”Asynchronous” property…’
    Print @StatusStr
    END

    BEGIN
    EXEC @OLEreturn = sp_OAMethod @ADOcommprop, ‘Item’, @ADOcommpropVal out, ‘Asynchronous’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcommprop , @src OUT, @desc OUT
    goto OA_ERROR
    END
    EXEC @OLEreturn = sp_OASETProperty @ADOcommpropVal, ‘Value’,True
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcommpropVal , @src OUT, @desc OUT
    goto OA_ERROR
    END
    exec @OLEreturn = sp_OADestroy @ADOcommpropVal;
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –Create the ADO Recordset to hold the results of the LDAP query
    –This was in orignal code but other Web examples indicate that it is not required; Execute will do this;
    –note that in orignal code @ADOrs is used as an output object twice
    /*
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Create the temporary ADO recordset for query output…’
    Print @StatusStr
    END
    EXEC @OLEreturn = sp_OACreate ‘ADODB.RecordSET’,@ADOrs out
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’
    */

    –Pass the LDAP query to the ADO command object
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Input the LDAP query…’
    Print @StatusStr
    END
    EXEC @OLEreturn = sp_OASETProperty @ADOcomm, ‘CommandText’, @LDAP_Query
    IF @OLEreturn 0
    BEGIN — Return OLE error
    EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
    goto OA_ERROR
    END
    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    –Run the LDAP query and output the results to the ADO Recordset
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Execute the LDAP query…’
    Print @StatusStr
    END
    Exec @OLEreturn = sp_OAMethod @ADOcomm, ‘Execute’ ,@ADOrs OUT
    IF @OLEreturn 0
    BEGIN — Return OLE error
    IF @Verbose=1 print ‘Error in Execute clause of SP_OAMethod’
    EXEC sp_OAGetErrorInfo @ADOcomm , @src OUT, @desc OUT
    goto OA_ERROR
    END

    –Return the rows found
    IF @Verbose=1
    BEGIN
    Set @StatusStr = ‘Retrieve the LDAP query results…’
    Print @StatusStr
    END

    DECLARE @pwdlastset varchar(8)

    EXEC @OLEreturn = sp_OAMethod @ADOrs, ‘getrows’
    IF @OLEreturn 0
    BEGIN — Return OLE error
    IF @Verbose = 1 Print ‘Error in Getstring of getproperty’
    EXEC sp_OAGetErrorInfo @ADOrs , @src OUT, @desc OUT
    goto OA_ERROR
    END

    IF @Verbose=1 Print Space(len(@StatusStr)) + ‘done.’

    DONE:
    EXEC @i = sp_OAMethod @ADOrs, ‘Close’
    if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar);
    EXEC @i = sp_OAMethod @ADOconn, ‘Close’
    if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar);

    exec @i = sp_OADestroy @ADOcommprop;
    if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar);
    exec @i = sp_OADestroy @ADOrs;
    if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar);
    exec @i = sp_OADestroy @ADOcomm;
    if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar);
    exec @i = sp_OADestroy @ADOconn;
    if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar);

    return 0

    OA_ERROR:
    SET @Error_Message = ‘Error=’ + isnull(cast(@OLEreturn as varchar),’?’) +
    ‘/Src=’ + isnull(@src,’?’) +
    ‘/Description=’ + isnull(@desc,’?’);
    if @Verbose=1 Print @Error_Message;

    EXEC @i = sp_OAMethod @ADOrs, ‘Close’
    if @Verbose = 1 print ‘@ADOrs close result = ‘ + cast(@i as varchar);
    EXEC @i = sp_OAMethod @ADOconn, ‘Close’
    if @Verbose = 1 print ‘@ADOconn close result = ‘ + cast(@i as varchar);

    exec @i = sp_OADestroy @ADOcommprop;
    if @Verbose = 1 print ‘@ADOcommprop destroy result = ‘ + cast(@i as varchar);
    exec @i = sp_OADestroy @ADOrs;
    if @Verbose = 1 print ‘@ADOrs destroy result = ‘ + cast(@i as varchar);
    exec @i = sp_OADestroy @ADOcomm;
    if @Verbose = 1 print ‘@ADOcomm destroy result = ‘ + cast(@i as varchar);
    exec @i = sp_OADestroy @ADOconn;
    if @Verbose = 1 print ‘@ADOconn destroy result = ‘ + cast(@i as varchar);

    return @OLEreturn

    END;

  6. Parikshit,
    …… and the errors are NOW gone…..

  7. Thanks Jonathan, for your efforts to rectify the code…I will update the code in the blog 🙂

  8. I get an error message
    “The identifier ‘Select samAccount name FROM “LDAP://……… is too long. Maxium length is 128.

    Any ideas on how to get past this?

  9. I get the error “Error=-2147211470/Src=ODSOLE Extended Procedure/Description=Output values of type Object are not allowed in result sets.” when I include the field accountexpires in my query.

    Any ideas on what I can do to get past this?

  10. So…is it just me or are all the quotes replaced with “smart quotes” and the double hyphen replaced with the single large hyphen? It’s like the code was auto-corrected with word. Because of this it’s not runnable by copying and pasting as is. Is there a link somewhere to the un-smartquoted SQL code?

    • Can someone provide the “runnable” code? There is something wrong with the quotes in stored procedure.

      thanks

  11. When i tried to retrived objectCategory = group and this group is memberof another group, no data was retrieved
    Query like:

    ‘SELECT samAccountName FROM ”LDAP://DomainName” WHERE objectCategory = ”Group” AND memberof=”CN=ParentGroup Office,OU=X,OU=Y,OU=Z,DC=A,DC=B”’

  12. I was able to create the SPs with no issue. When I execute the sp, (I used the proper LDAP string)
    “exec spqueryad ‘SELECT smaaccountname FROM ”LDAP://DC=MyDomain, DC=COM” WHERE objectClass = ”User”’, 1

    I get the result, “0x80004005 Provider Unspecified error” and the messages:

    Create ADO connection… done.
    Set ADO connection to use Active Directory driver… done.
    Open the ADO connection… done.
    Create ADO command object… done.
    Set ADO command object to use Active Directory connection… done.
    Retrieve ADO command properties… done.
    Set ‘PageSize’ property… done.
    Set ‘SearchScope’ property… done.
    Set ‘Asynchronous’ property… done.
    Create the temporary ADO recordset for query output… done.
    Input the LDAP query… done.
    Execute the LDAP query… Error in Execute clause of SP_OAMethod
    (1 row(s) affected)

    Any idea where I am going wrong? Some searching shows the query may be incorrect. I have used the same query, limited to 100 rows, through a linked server, and it worked.

  13. Jonathan and Parikshit, thank you both very much. This has been tremendously helpful. I noticed that the resulting column names are “Column0”, “Column1”, “Column2” and so on, and moreover they appear to be in reverse order as compared to the select list supplied in the LDAP query.

    Do you have any guidance on getting the column names (or field names, as it were) into the returned recordset? If not, can we be confident of the consistency of that reversed order of columns as queried?

Leave a Reply

Required fields are marked *.