Solved

ADSI into a stored procedure

Posted on 2004-09-13
5
328 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now