Solved

ADSI into a stored procedure

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 31
Refresh Dev server with Production database 8 28
Need some help to cast ntext to nvarchar SQL 2000 7 33
Sql Server group by 10 27
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

778 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