March 20, 2008 at 11:48 am
Hi
I have a linked server(Active Dir) to my SQl Server2005 . When I run the following it runs fine..
The only problem I have is the page size.It returns only 1000 rows. I am not a heavy TSQL person.
Can some one show me how I can retrieve all the records and not just 1000.
Thanks
SELECT [Name], SN [Last Name], ST State,SAMAccountName
FROM OPENQUERY( ADSI,
'SELECT Name, SN, ST, SAMAccountName
FROM ''LDAP://mycom.com''
WHERE objectCategory = ''Person'' ')
March 20, 2008 at 1:48 pm
Either this is an LDAP issue or you've got SET ROWCOUNT on.
Try doing SET ROWCOUNT 0 before your query.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 21, 2008 at 8:04 am
No difference in numbers of rows returned (1000) with SET ROWCOUNT 0.
This is an LDAP issue..where the page size is set to 1000. Looking for a way to overcome this.
September 11, 2008 at 9:00 am
I run the following query and it will return 17,000 plus records:
SELECT
sAMAccountName
,DistinguishedName
,dbo.UTC2date(accountExpires)
,employeeID
,cn
FROM
OpenQuery(ADSI,
'SELECT
sAMAccountName
,DistinguishedName
,accountExpires
,employeeID
,cn
FROM ''LDAP://ou=Accounts,DC=mycom,DC=com''
where objectClass = ''User''')
My problem is that I want the description attribute.
When I try to run this query:
SELECT
sAMAccountName
,DistinguishedName
,dbo.UTC2date(accountExpires)
,employeeID
,cn
,description
FROM
OpenQuery(ADSI,
'SELECT
sAMAccountName
,DistinguishedName
,accountExpires
,employeeID
,cn
,description
FROM ''LDAP://ou=Accounts,DC=mycom,DC=com''
where objectClass = ''User''')
I get the following error:
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "ADsDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.
Any ideas?
Thanks Bill
Bill Soranno
MCP, MCTS, MCITP DBA
Database Administrator
Winona State University
Maxwell 143
"Quality, like Success, is a Journey, not a Destination" - William Soranno '92
September 12, 2008 at 5:23 am
This is a limitation of Active Directory. I'm afraid I've never accessed it using LDAP in SQL but the below is C# code that sets the PageSize property of the searcher. This creates a paged search which will return all results in an ArrayList.
I suggest looking up LDAP paged searches in SQL.
private static ArrayList ADGetHostNames()
{
ArrayList arResult = new ArrayList();
DirectoryEntry rootEntry = new DirectoryEntry("LDAP://DomainController.mycompany.com/OU=someOU,DC=mycompany,DC=com");
DirectorySearcher rootSearcher = new DirectorySearcher(rootEntry);
rootSearcher.Filter = "(&(objectClass=Computer))";
rootSearcher.PropertiesToLoad.Add("CN");
rootSearcher.PageSize = 500;
SearchResultCollection res = rootSearcher.FindAll();
foreach (SearchResult searchResults in res)
{
arResult.Add(searchResults.GetDirectoryEntry().Name.Substring(3));
}
rootEntry.Dispose();
rootSearcher.Dispose();
return arResult;
}
September 12, 2008 at 5:33 am
Just had a quick look.
Try setting this before the query.
ADS_SEARCHPREF_PAGESIZE = 500.
September 12, 2008 at 9:26 am
This will bring back every user account in AD (you can restrict it to certain OU where you would place your LDAP information) and put it in a table called ACCOUNTS.
I also have some code that will perform a recursive join to add manager information for each user, and some further stuff to add new people, update information for existing people and remove people no longer in AD. Let me know if you need that.
Just want to say I cannot take credit for the script below, people way smarter than me came up with that.
CREATE TABLE ACCOUNTS (
DName varchar(255)
, FirstName varchar(100)
, LastName varchar(100)
, LoginName varchar(50)
, Location varchar(500)
, JobTitle varchar(500)
, EmailAddress varchar(255)
, ManagerDName varchar(255)
, PhoneNumber varchar(255)
, MobileNumber varchar(255))
-- 10 minutes approx
set nocount on
declare @sql varchar(2000)
DECLARE @letters2 AS char(26)
SET @letters2= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @letterin2 char(1)
DECLARE @position2 int
DECLARE @letters AS char(26)
SET @letters= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @letterin char(1)
DECLARE @position int
-- this is the initial variable to incre
-- ment
SET @letterin = 'A'
-- find the letter in your preset string
--
SET @position = CharIndex(@letterin,@letters,1)
PRINT @position
-- increment to the next position desire
-- d
while @position < 27
begin
-- this is the initial variable to incre
-- ment
SET @letterin2 = 'A'
-- find the letter in your preset string
--
SET @position2 = CharIndex(@letterin2,@letters2,1)
-- PRINT @position
-- increment to the next position desire
-- d
while @position2 < 27
begin
select @sql = ' select
rtrim(distinguishedName) as DName
, rtrim(GivenName) as FirstName
, rtrim(sn) as LastName
, rtrim(samaccountname) as LoginName
, rtrim(physicalDeliveryOfficeName) as Location
, rtrim(Title) as JobTitle
, rtrim(mail) as EmailAddress
, rtrim(manager) as ManagerDName
, rtrim(telephonenumber) as PhoneNumber
, rtrim(Mobile) as MobileNumber from openquery(COS_Domain, ''SELECT
distinguishedName
, GivenName
, sn
, samaccountname
, physicalDeliveryOfficeName
, Title
, manager
, telephonenumber
, Mobile
FROM
''''LDAP://yourLDAP''''
WHERE
objectCategory=''''Person''''
and samaccountname = ''''' + @letterin + @letterin2 + '*'''''')'
insert into accounts exec (@SQL)
SET @position2 = @position2 + 1
-- get the new letter by the new positio
-- n number
SET @letterin2 = Substring(@letters2,@position2,1)
end
--PRINT @letterin
SET @position = @position + 1
-- get the new letter by the new positio
-- n number
SET @letterin = Substring(@letters,@position,1)
end
September 12, 2008 at 10:55 am
Ah - I guess using LDAP via SQL doesn't support paged searches then?
That code wouldn't work if you had more than 1000 people with a samAccountname starting with the same letter - though I can't imagine anybody would have that problem.
September 12, 2008 at 11:07 am
I actually searches the first two characters I belive, so the odds of running into that issue are even more remote.
February 11, 2009 at 1:38 am
Hi,
I need to retrieve more than 1000 rows from LDAP without modifying PageSize 1000 rows limit.
I don't think using an alphabetical script is a good practice as there could be more than 1000 users starting with the same letter in this AD. Furthermore, what a such script would do if the user starts with a foreign character like ? or ã, etc. ?
Is there a way to do this ? I am using SSIS from Business Intelligence Developement Studio.
a+, =)
-=Clement=-
Configuration :
SQL Server 2005
December 9, 2009 at 12:52 pm
Nice query, I am playing with it now and adapting to my needs. Seeing if I can take it to the level of overkill 😀
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 13, 2010 at 4:06 am
Use ODBC programming. Clue: Sp_oacreate
December 13, 2010 at 5:42 am
this article from Microsoft is what i have saved to get the list via PowerShell: I had this saved in my snippets, and the code I saved is unchanged from their site's version, so i won't bother posting that. I never did test it on our domain.
http://technet.microsoft.com/en-us/library/ff730967.aspx
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply