Solved

ADSI into a stored procedure

Posted on 2004-09-13
5
336 Views
Last Modified: 2012-06-21
Hello everybody,

I've done a stored procedure which collect the objectSID, the name and the sAMAccount of all the users into the domain. Now the system engineer added 10 new (not standard) categories into the Active Directories Objects. I have to take a value stored in a new object called "aimWfGalaxyID".
Everytime I try to call this new object from the recordset an error occurs. The data and the object exist because I checked more than 10 times...

everyone knows how to solve my problem?

thanxs in advance

nick
0
Comment
Question by:nschwend
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:ispaleny
ID: 12049490
What error?
Can you post more about your issue?
0
 

Author Comment

by:nschwend
ID: 12051733
Hi,
this is a small part of my source:


...
exec @error = sp_oaSetProperty @conn, 'Provider', 'ADSDSOObject;'
exec @error = sp_oaMethod @conn, 'Open'
exec @error = sp_oaSetProperty @recordset, 'Source', 'SELECT objectSID, aimWfGalaxyID, name FROM ''LDAP://DC=webdew,DC=net'' WHERE ObjectClass = ''user'' and ObjectCategory = ''Person'' ORDER BY name'
exec @error = sp_oaSetProperty @recordset, 'ActiveConnection', @conn
exec @error = sp_oaMethod @recordset, 'Open'
...


when I print the last @error it gives to me the following code: -2147467259

the error is a run-time error.

I hope it can helps you...

Thanx again

Nick
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12099664
You don't have to use sp_oa to connect to ADSI.  You can create a linked server and reference it directly in T-SQL. See if this link helps.  http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20899910.html
0
 

Author Comment

by:nschwend
ID: 12099742
I tried using the procedure that you linked but it gives me always the same error that received 'klax'. I also tried changing the LDAP query but doesn't work either...


I don't know what to do... Now I'm looking if I've setted all the permissions on the new objects...
0
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 12099750
This works for me. Try it out:

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

-- Change yourusername and yourpassword to a valid domain login and password. Change yourdomain to your domain and com to your root domain if not com
sp_addlinkedsrvlogin ADSI, FALSE, 'yourusername', 'CN=yourusername,CN=Users,DC=yourdomain,DC=com', 'yourpassword'

--Select all users with names beginning with a. This will only return up to 1000 rows so you may need to retrieve the entire list in batches for a large orginization.
SELECT CN, ADsPath FROM OpenQuery
( ADSI, 'SELECT CN, ADsPath FROM ''LDAP://DC=eprod,DC=COM'' WHERE objectCategory=''person'' AND objectClass=''user'' AND CN = ''a*'' ORDER BY CN')
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question