SQL Server 2005 - Get LDAP (Lightweight Directory Access Protocol) Informtion

How can we retrieve more than 1000 records from LDAP?

This script will allow you to query AD and return more than 1,000 rows.  It utilizes two custom UDF's and will create them in the master database by default, so please edit the script if you want them created elsewhere, remembering to update the UDF calls accordingly.  Enjoy :)

USE [master]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnSprintf]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnSprintf]
GO

CREATE FUNCTION [dbo].[fnSprintf] (@s VARCHAR(8000), @params VARCHAR(8000), @separator CHAR(1) = ',')
RETURNS VARCHAR(8000)
AS
BEGIN
 DECLARE @p VARCHAR(8000)

 SET @params = @params + @separator
 WHILE NOT @params = ''
 BEGIN
  SET @p = LEFT(@params + @separator, CHARINDEX(@separator, @params) - 1)
  SET @s = STUFF(@s, CHARINDEX('%s', @s), 2, @p)
  SET @params = SUBSTRING(@params, LEN(@p) + 2, 8000)
 END
 RETURN @s
END
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_ConvertInteger8DateTime]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_ConvertInteger8DateTime]
GO

CREATE FUNCTION [dbo].[fn_ConvertInteger8DateTime]
(
    @ConversionInt8 NUMERIC(38,0)
)
RETURNS DATETIME
AS
BEGIN

 --This will hold the number of days from 1-1-1901 to whatever day passed in
 DECLARE @NumDaysConvDate BIGINT

 --This will be the # days from 1-1-1901 multiplied to get 100 nanosecond units
 DECLARE @ConvDateNanoSeconds NUMERIC(18,0)

 --There are 86400000000000 nanoseconds in a day but Integer8 is 100 nanosecond units so divide by 100
 DECLARE @NanoSecondsDay BIGINT
 SET @NanoSecondsDay = 864000000000
 
 --Number of Nanosecond units between two epoch dates 1-1-1601 & 1-1-1901 (299 Years * 365.242199 days in a year) * 864000000000 (nanosecond units in a day)
 DECLARE @EpochDatesNanoSeconds NUMERIC(18,0) 
 SET @EpochDatesNanoSeconds = 94355208720864000
 
 --First for some reason there are differing epoch dates in SQL and Integer8 (Active Directory)
 --So we subtract the nanosecond units from from above to a precalculated quantity which is the delta of 1-1-1601 to 1-1-1901
 --This will "jump us forward in time to 1-1-1901
 SET @ConvDateNanoSeconds = @ConversionInt8 - @EpochDatesNanoSeconds
 
 --Second we will calculate the number of 100 nanosecond units that is equal to days since SQL epoch
 --This will tell us how many complete days have elapsed since 1-1-1901
 SET @NumDaysConvDate = @ConvDateNanoSeconds / @NanoSecondsDay
 --SELECT @ConvDateNanoSeconds AS '@ConvDateNanoSeconds'
 
    RETURN
    (
 
  --Finally we will convert our number days since 1-1-1901 into a SQL Date time.
  SELECT CONVERT(DATETIME, @NumDaysConvDate)

  --YEAH I know not 100% accurate to the second. Doesn't need to be for our use.
    )
END
GO

DECLARE @ADUsers TABLE (
      CommonName VARCHAR(255),
      DistinguishedName VARCHAR(512),
      LoginName VARCHAR(255) PRIMARY KEY,
      FullName VARCHAR(255),
      Email VARCHAR(255),
      City VARCHAR(255),
      State VARCHAR(100),
      Telephone VARCHAR(100),
      DirectExtension VARCHAR(100),
      MobilePhone VARCHAR(100),
      PersonalTitle VARCHAR(100),
      FirstName VARCHAR(100),
      Initials VARCHAR(100),
      LastName VARCHAR(100),
      JobTitle VARCHAR(255),
      ManagerName VARCHAR(512),
      StartDate DATETIME,
      AccountExpires BIGINT
      UNIQUE(LoginName, DistinguishedName))

DECLARE @sChar CHAR(1)
DECLARE @body VARCHAR(8000)
DECLARE @nAsciiValue SMALLINT

SELECT @nAsciiValue = 65

WHILE @nAsciiValue < 91
BEGIN
 SELECT @sChar= CHAR(@nAsciiValue)

 SET @body = (select master.dbo.fnSprintf('SELECT cn, distinguishedName, sAMAccountName, displayName, mail, l, st, telephoneNumber, ipPhone, mobile, personalTitle, givenName, initials, sn, title, manager, createTimeStamp, accountExpires FROM OPENROWSET(''ADSDSOObject'', ''adsdatasource;'',
                                         ''SELECT cn, distinguishedName, sAMAccountName, displayName, mail, l, st, telephoneNumber, ipPhone, mobile, personalTitle, givenName, initials, sn, title, manager, createTimeStamp, accountExpires FROM ''''LDAP://<your DC name here>/dc=<your domain name here>,dc=<your domain suffix here>''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @SChar +'*', default))

 INSERT @ADUsers
 EXEC( @body)

 SELECT @nAsciiValue = @nAsciiValue + 1
END

SELECT  CommonName = ISNULL(A.CommonName, ''),
   DistinguishedName = ISNULL(A.DistinguishedName, ''),
   LoginName = ISNULL(Upper(A.LoginName), ''),
   FullName = ISNULL(A.FullName, ''),
   Email = ISNULL(A.Email, ''),
   City = ISNULL(A.City, ''),
   State = ISNULL(A.State, ''),
   Telephone = ISNULL(A.Telephone, ''),
   DirectExtension = ISNULL(A.DirectExtension, ''),
   MobilePhone = ISNULL(A.MobilePhone, ''),
   PersonTitle = ISNULL(A.PersonalTitle, ''),
   FirstName = ISNULL(A.FirstName, ''),
   Initials = ISNULL(A.Initials, ''),
   LastName = ISNULL(A.LastName, ''),
   JobTitle = ISNULL(A.JobTitle, ''),
   ManagerFullName = ISNULL(SUBSTRING(A.ManagerName, 4, CHARINDEX('OU=', A.ManagerName) - 5), ''),
   ManagerLoginName = ISNULL(UPPER(AM.LoginName), ''),
   StartDate = ISNULL(CONVERT(VARCHAR, A.StartDate, 106), ''),
   EndDate = CASE WHEN A.AccountExpires <> 0 AND A.AccountExpires <> 9223372036854775807 THEN CONVERT(VARCHAR, [master].[dbo].[fn_ConvertInteger8DateTime](A.AccountExpires), 106) ELSE 'Never' END
FROM  @ADUsers A
LEFT JOIN @ADUsers AM ON  A.ManagerName = AM.DistinguishedName
ORDER BY LoginName