SQLZealot - Blog - Incremental DL Porting in SQL Server - Querying LDAP to get the users belongs to a DL group in SQL Server.

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File

My Experiences in SQL Server


	Like

Invite Friends

Browse by Tags · View All

SQL Server - Replication

Archive · View All

February 2013	1
July 2012	1
June 2012	1

SQLZealot - Blog

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (43) · Preferences

Incremental DL Porting in SQL Server - Querying LDAP to get the users belongs to a DL group in SQL Server.

Jun 27 2012 12:00AM by Latheesh NK

Querying LDAP to get the users belongs to a DL group in SQL Server (Incremental Loading)

This has been a long process for SQL Developers to find out the exact queries to get the user list for a DL group. This is extremely useful for some scenarios that the DL List permission allows/integrates an application access.

From the analysis, the LDAP is not really storing the data in a normalized way. Hence its totally a big challenge to get it in SQL server. Here below, I tried to get the details in SQL Server.

A special scenario that challenged is consolidating the users under different child DLs to map to the Parent DL. I have come up with 3 tables to connect this in SQL server.

Table_Name Description
DL_List -Contains all DL names and its ADSPath details.
DL_SubDLList -Contains the DL mapping(Parent->Child relationship)
DL_UserList -Contains the DL name and the users associated with each DL.

Once we filled the details, I guess we can easily get all the users mapped to DL irrestive of it heirarchy(Parent or child).

To be more clear with the configuration please follw the steps below:

1. Check LDAP connectivity to the server
2. Ole Automation Procedures should be enabled
3. The stored procedure master.dbo.spQueryAD has to be created in master databse

Ok, Now lets look at few of snippets to populate the data to each table.

ALTER Proc [dbo].[Sp_DLPorting]    
As     
Begin    
/************************************************************************************************          
           
 Author			: SQLZealot          
 Created Date	: NA          
           
 Revision History          
 -----------------          
 Version No:    Modified By:   Modified Date:    Comments:          
 ****************************************************************************************          
 ----------------------------------------------------------------------------------------          
 V10  SQLZealot   12th Jun 2012  
                             
 ----------------------------------------------------------------------------------------                              
*************************************************************************************************/              
             
    
                
 Create Table #DL_List    
 (    
  DL_ID int Identity(1,1) ,    
  s_dl_name Varchar(5000),    
  ADS_Path Varchar(5000),    
  MailID Varchar(5000),    
  --Last_Port_Date DateTime,    
  --Is_Active Bit,    
  Created_By Varchar(1000),    
  Created_Date DateTime,    
  --Updated_By Varchar(100),    
  Updated_Date DateTime    
 )    
           
   Create Table #Temp_DL_Processing_Rec    
 (    
  DL_ID int Identity(1,1) ,    
  s_dl_name Varchar(5000),    
  ADS_Path Varchar(5000),    
  MailID Varchar(5000),    
  --Last_Port_Date DateTime,    
  --Is_Active Bit,    
  Created_By Varchar(1000),    
  Created_Date DateTime,    
  --Updated_By Varchar(100),    
  Updated_Date DateTime    
 )    
      
        
    Create Table #DL_UserList_Intermediate    
   (          
 User_ID Varchar(2000),      
    ADS_Path Varchar(4000),    
    MailID Varchar(5000),    
    Created_Date DateTime,    
    Updated_Date DateTime    
    )    
        
    Create Table #DL_UserList            
   (          
 User_ID Varchar(2000),      
    ADS_Path Varchar(4000),    
    s_dl_name Varchar(4000),    
    DL_ADS_Path Varchar(4000),    
    MailID Varchar(5000),    
    Created_Date DateTime,    
    Updated_Date DateTime    
    )    
        
    Create Table #DL_SubDLList_Intermediate    
   (          
 sub_dl_name Varchar(4000),      
    ADS_Path Varchar(4000),    
    )    
        
    Create Table #DL_SubDLList            
   (          
 sub_dl_name Varchar(4000),    
    ADS_Path Varchar(4000),    
    s_dl_name Varchar(4000),    
    DL_ADS_Path Varchar(4000)    
    )    
    DECLARE @QueryDL NVARCHAR(4000)       
    
        
   DECLARE @RC int             
   DECLARE @LDAP_Query varchar(4000)              
   DECLARE @Verbose bit    
   SET @LDAP_Query = 'SELECT  displayName,ADsPath,whencreated,whenchanged,managedby,Mail FROM ''LDAP://LDAPServer.com'' where objectCategory=''group'''            
       
   SET @Verbose = 1              
   Insert into #DL_List(MailID,Created_By,Updated_Date ,Created_Date ,ADS_Path,s_dl_name)    
     EXECUTE @RC = master.dbo.spQueryAD  @LDAP_Query ,@Verbose    
        
          
   Delete From #DL_List Where s_dl_name is null    
   Delete From #DL_List Where MailID is null    
       
     IF EXiSTS(select 1 from #DL_List)/*If LADP fails this Statement will prevent*/  
     BEGIN        
  
       
   update #DL_List             
    Set Created_By = ltrim(rtrim(substring(Created_By,4,charindex(',ou',Created_By)-4)))            
    where Created_By is not null and  Len(substring(Created_By,4,Charindex(',OU',Created_By)))>4       
       
    Declare @Last_Port_Date Datetime    
 Select @Last_Port_Date = MAX(updated_date) From DL_List    
         
 Insert into DL_List(ADS_Path,s_dl_name,MailID,Last_Port_Date,Is_Active,created_by,Created_date,Updated_date)     
   Select A.ADS_Path,A.s_dl_name,A.MailID,Getdate(),1,A.Created_By,A.Created_Date ,A.Updated_Date     
   From #DL_List A    
   Left join DL_List B On A.ADS_Path = B.ADS_Path where B.ads_path is null    
      
     
 -- To make the old entries(deleted Dls since last port date)inactive.    
 Update A Set Is_Active = 0,Last_Port_Date=GETDATE() From DL_List A    
 Left Join #DL_List B On A.ADS_Path = B.ADS_Path where B.ads_path is  null    
     
 delete From DL_List Where CHARINDEX(')',ADs_Path,0) <> 0 and CHARINDEX('(',ADs_Path,0) = 0      
      
 Insert into #Temp_DL_Processing_Rec(s_dl_name,ADS_Path,MailID,Created_By,Created_Date,Updated_Date) select s_dl_name,ADS_Path,MailID,Created_By,Created_Date,Updated_Date    
  From #DL_List Where Updated_Date > Isnull(@Last_Port_Date,'1990-01-01 01:42:08.243')    
      
  Update  A SET A.updated_date=B.Updated_Date ,A.MailID =B.MailID,A.Created_By =B.Created_By,Last_Port_Date=GETDATE()   
  from DL_List A INNER JOIN #Temp_DL_Processing_Rec B on A.s_dl_name =B.s_dl_name and a.ADS_Path =B.ADS_Path   
   
 --To fetch the Sub DLs and Users    
 Declare @CN_Name Varchar(4000),@DL_ADS_Path varchar(4000),@dl_Name varchar(4000)    
 Declare @Max int, @Counter int    
 Set @Counter = 1    
 Select @Max = COUNT(1) From #Temp_DL_Processing_Rec    
  Begin  
  LABEL1:  
 While(@Counter<=@Max)    
       
BEGIN TRY  
--print 'counter'+convert(varchar,@Counter)  
  Select @DL_ADS_Path = ADS_Path,@dl_Name = s_dl_name From #Temp_DL_Processing_Rec where DL_ID = @Counter    
      
  Select @CN_Name = Substring(ADS_Path,CHARINDEX('CN=',ADS_Path,0)+3,LEN(ADS_Path)) From #Temp_DL_Processing_Rec where DL_ID = @Counter    
      
  Select @CN_Name,@DL_ADS_Path    
    
   SET @QueryDL = 'Insert into #DL_UserList_Intermediate(ADS_Path,User_Id,Created_Date,Updated_Date,MailId)  SELECT ADsPath,samAccountName,WhenCreated,WhenChanged,mail                             
        FROM OPENQUERY(ADSI, ''SELECT ADsPath,samAccountName,WhenCreated,WhenChanged,mail                             
        FROM ''''LDAP://LDAPServer.com''''     
             where memberOf=''''CN=' + Replace(@CN_Name,'''','''') + '''''  AND objectCategory=''''Person'''' AND objectClass = ''''User'''''')'                              
                 
        exec sp_executesql @QueryDL       
            
         SET @QueryDL = 'Insert into #DL_SubDLList_Intermediate(ADS_Path,sub_dl_name)  SELECT ADsPath,samAccountName                               
        FROM OPENQUERY(ADSI, ''SELECT ADsPath,samAccountName                             
        FROM ''''LDAP://LDAPServer.com''''     
             where memberOf=''''CN=' + Replace(@CN_Name,'''','''') + '''''  AND objectCategory=''''group'''''')'                              
                 
        exec sp_executesql @QueryDL       
            
  Insert into #DL_UserList   
 Select USER_ID,ADS_Path,@dl_Name,@DL_ADS_Path,MailID,Created_Date,Updated_Date From #DL_UserList_Intermediate    
      
  Insert into #DL_SubDLList   
 Select sub_dl_name,ADS_Path,@dl_Name,@DL_ADS_Path From #DL_SubDLList_Intermediate    
    
       
  Truncate table #DL_UserList_Intermediate    
  truncate table #DL_SubDLList_Intermediate    
  Set @CN_Name = ''      
  Set @Counter = @Counter + 1      
  END TRY  
    
  BEGIN CATCH           
     
  DECLARE @ERROR NVARCHAR (4000)           
          
  SET @ERROR= Error_message()           
          
  INSERT INTO DBO.errorlog_dl           
  VALUES      ('' + @CN_Name + '',           
      '' + @QueryDL + '',           
      '' + @ERROR + '',GETDATE())  
                 
        Set @Counter = @Counter + 1  
    
  GOTO LABEL1           
 END CATCH   
 End     
    
   
  
 Insert into DLUser_List(User_ID,s_dl_name,User_ADS_Path,DL_ADS_Path,MailID,Created_Date,Updated_Date,Is_Active,Ported_Date)    
 Select A.User_ID,A.s_dl_name ,A.ADS_Path,A.DL_ADS_Path,A.MailID,A.Created_Date,A.Updated_Date,1,Getdate()     
 From #DL_UserList A    
 Left join DLUser_List B On A.User_ID = B.User_ID And A.DL_ADS_Path = B.DL_ADS_Path    
 where B.User_ads_path is null    
    
  Update A SET A.Is_Active =1,Ported_Date=Getdate() From DLUser_List A   
 Inner Join #Temp_DL_Processing_Rec C On A.DL_ADS_Path = C.ADS_Path  
 Inner join #DL_UserList B on A.User_ID =B.User_ID AND A.DL_ADS_Path =B.DL_ADS_Path where A.Is_Active =0  
   
 Update A SET A.Is_Active =0,Ported_Date=Getdate() From DLUser_List A   
 Inner Join #Temp_DL_Processing_Rec C On A.DL_ADS_Path = C.ADS_Path  
 Left join #DL_UserList B on A.User_ID =B.User_ID AND A.DL_ADS_Path =B.DL_ADS_Path   
 where B.User_ID Is Null  
   
     
 Update A SET A.MailID =B.MailID,A.Updated_Date =B.Updated_Date,Ported_Date=Getdate() From DLUser_List A   
 Inner Join #DL_UserList B on A.User_ID=B.User_id and  A.DL_ADS_Path = B.DL_ADS_Path And A.User_ADS_Path =B.ADS_Path   
      
   
     
 Insert into DLSubDL_List(Sub_dl_name,s_dl_name,SubDL_ADS_Path,DL_ADS_Path,Is_Active,Ported_Date)    
 Select A.sub_dl_name,A.s_dl_name ,A.ADS_Path,A.DL_ADS_Path,1,Getdate()     
 From #DL_SubDLList A    
 Left join DLSubDL_List B On A.ADS_Path = B.SubDL_ADS_Path And A.DL_ADS_Path = B.DL_ADS_Path    
 where B.SubDL_ADS_Path is null    
    
  Update A Set A.Is_Active =1,Ported_Date=Getdate()     
 From DLSubDL_List A    
 Inner Join #Temp_DL_Processing_Rec C on A.DL_ADS_Path = C.ADS_Path  
 Inner Join #DL_SubDLList B On B.ADS_Path = A.SubDL_ADS_Path And B.DL_ADS_Path = A.DL_ADS_Path    
 where A.Is_Active =0   
      
 Update A Set A.Is_Active =0,Ported_Date=Getdate()     
 From DLSubDL_List A    
 Inner Join #Temp_DL_Processing_Rec C on A.DL_ADS_Path = C.ADS_Path  
 Left Join #DL_SubDLList B On B.ADS_Path = A.SubDL_ADS_Path And B.DL_ADS_Path = A.DL_ADS_Path    
 where B.ads_path is null    
   
   
  /*Drop the Temporary Table*/  
   Drop table #DL_List      
      Drop table #Temp_DL_Processing_Rec    
      Drop table #DL_UserList_Intermediate    
      Drop table #DL_UserList    
      Drop table #DL_SubDLList_Intermediate    
      Drop table #DL_SubDLList    
        
          END --IF Closed Here   
 END

As Always, Please share your thoughts about this blog.

Tags:

Previous:
Next: Full Text Search Population is not working

Latheesh NK

55 · 3% · 1115

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

2 Comments

Can you please post code for the "spQueryAD" stored procedure and other tables that are referenced here?

commented on Jul 5 2012 7:02AM

brianhuber

2891 · 0% · 2

Hi Brian,

Thank you for showing interest. Please find the code for spQueryAd.The tables referenced are to be created in line with your requirement.

CREATE PROCEDURE [dbo].[Spqueryad] (@LDAPQuery VARCHAR(255)='',

                            @Verbose    BIT=0)

AS

/*

DECLARE @RC int

DECLARE @LDAPQuery varchar(255)

DECLARE @Verbose bit   

-- TODO: Set parameter values here.    

SET @LDAP_Query = 'SELECT samAccountName FROM ''LDAP://DC=DOMAIN,DC=ORG'' WHERE objectCategory=''person'' and  objectclass=''user'''   

SET @Verbose = 1    

EXECUTE @RC = dbo.spQueryAD    

@LDAP_Query    

,@Verbose    

GO   

*/

  --declare variables

  DECLARE @ADOconn        INT ,

          @ADOcomm        INT ,

          @ADOcommprop    INT ,

          @ADOcommpropVal INT ,

           @ADOrs          INT ,

           @OLEreturn      INT ,

           @src            VARCHAR(255) ,

           @desc           VARCHAR(255) ,

           @PageSize       INT ,

           @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 ADSSCOPESUBTREE 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 = Spoasetproperty

    @ADOcomm,

    'CommandText',

    @LDAPQuery   

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

        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.'   

--Return the rows found

  IF @Verbose = 1

    BEGIN

        SET @StatusStr = 'Retrieve the LDAP query results...'   

    PRINT @StatusStr   
END


EXEC @OLEreturn = Sp_oagetproperty

    @ADOrs,

    'getrows'   

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


EXEC @OLEreturn = spOADestroy @ADOconn

IF @OLEreturn <> 0

BEGIN

   EXEC spOAGetErrorInfo @ADOconn

    RETURN

END  

IF @Verbose = 1

    PRINT Space(Len(@StatusStr)) + 'done.'

commented on Jul 6 2012 12:13AM

Latheesh NK

55 · 3% · 1115

Your Comment

Sign Up or Login to post a comment.

"Incremental DL Porting in SQL Server - Querying LDAP to get the users belongs to a DL group in SQL Server." rated 5 out of 5 by 2 readers