October 25, 2010 at 7:45 am
Hi, need some help with active directory. We have an old antequated active directory pull that we have been using that is cmd file based and it does the loop through active directory by letter of the alphabet with a bunch of handling for the 1000 records issue.
Well, I'm trying to bring this process out of the stone ages and make it a stored procedure. I had received a couple suggestions on stored procedures to get around the 1000 record limitation. But they aren't working... both methods basically create a stored procedure to do the ADO calls but when I actually run it to get the data back its not working.
The first method I tried just returns absolutely nothing saying "the command completed successfully" and the second method I have is actually returning an error that says:
Error SourceDescription
0x80040E14ProviderOne or more errors occurred during processing of command.
We have a linked server set up currently to the AD and I can query the AD with the linked server just fine...so I know my LDAP reference is correct...
The only thing I can think of is that when we have our linked server set up we have our own username and password to the Active Directory and these new methods I'm trying to use are not using a user name and password... would that be my culpret?
Any help on this would be fabulous. I really would like to get this working without having to do the annoying loop by letter thing... we're pulling down like 20k employees from AD and its not efficient.
I'm attaching the second method that I was trying.
November 9, 2010 at 8:52 am
Hi guys, still need help with this... had to shelf the process for our release cause I couldn't figure it out. Don't any of you pull active directory info into your databases? 🙂
November 16, 2010 at 12:52 pm
Still nobody? Is my question ambiguous? If I need to clarify anything to get some responses, please let me know.
November 16, 2010 at 1:20 pm
Amy, I flagged your post b/c I'd like to hear an answer, too. It's not a huge priority for me, b/c although we have AD setup here, our primary LDAP service is not AD.
I found this on Google, which I imagine you may have read too, but maybe it will help you:
Post back with your success or failure. There's gotta be an answer out there for this,
Rich
November 17, 2010 at 8:21 am
I checked your spQueryAD procedure and it's the same version I use successfully.
I noticed I get the exact same error if I just run a simple query trying to select users from AD.
What I do is do an insert into a temp table and then process all the records from there.
Try this code and see if you get any data back:
if OBJECT_ID('tempdb..#accounts') is not null
begin
drop table #accounts
end
go
create table #accounts(
sAMAccountName char(64),
displayName char(64)
)
go
insert into #accounts(sAMAccountName, displayName)
exec master..spQueryAD 'select sAMAccountName, displayName
from ''LDAP://dc=company,dc=com''
where objectCategory=''user'' and sAMAccountName = ''*'' and showInAddressBook = ''*'' ', 0
go
select * from #accounts
-jeff
November 18, 2010 at 6:53 am
Thanks, I will give that a try when I get some time.
Also question, does AD 2008 fix the 1000 record limitation issue? Or is it still present? I just found out they are upgrading to AD 2008 and was hopeful that I could just query it straight. 🙂
November 18, 2010 at 1:09 pm
The 1000 object limit is not really an AD limitation. The problem is more client side. LDAP defaults to 1000 objects for performance issues and to support older clients.
To get more than 1000 objects your method/client needs to support the paging option.
It is possible to modify Domain Controllers to return more than 1000 but it's not really recommended.
Read this if you want a better explanation on the issue:
http://jeftek.com/219/avoid-changing-the-maxpagesize-ldap-query-policy/
-jeff
January 4, 2011 at 12:30 pm
hodo (11/17/2010)
I checked your spQueryAD procedure and it's the same version I use successfully.I noticed I get the exact same error if I just run a simple query trying to select users from AD.
What I do is do an insert into a temp table and then process all the records from there.
Try this code and see if you get any data back:
if OBJECT_ID('tempdb..#accounts') is not null
begin
drop table #accounts
end
go
create table #accounts(
sAMAccountName char(64),
displayName char(64)
)
go
insert into #accounts(sAMAccountName, displayName)
exec master..spQueryAD 'select sAMAccountName, displayName
from ''LDAP://dc=company,dc=com''
where objectCategory=''user'' and sAMAccountName = ''*'' and showInAddressBook = ''*'' ', 0
go
select * from #accounts
-jeff
Hi there, I am just starting to work on this again. 🙂
I tried your code... and I got this error returned:
Error SourceDescription
0x80040E37ProviderTable does not exist.
January 6, 2011 at 7:26 am
bump.
February 16, 2011 at 10:19 am
bumping this again, still have not found a resolution.
February 20, 2011 at 8:37 am
i would use a csvde query to dump to CSV file and then import this into a sql server table, bit convoluted but in my opinion much better all round
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply