Solved

ADSI into a stored procedure

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

679 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