October 23, 2008 at 10:49 am
I added a manager's email field to an employee table and I want to populate and update it from Active Directory. I added a linked server using:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
The link checks out OK from SSMS, but I can't get any of my queries to work. i.e.
SELECT givenName AS FirstName, sn AS LastName, uid as UserEmail, manager AS ManagerEmail
FROM OPENQUERY (ADSI, 'SELECT givenName,sn,uid,manager FROM LDAP://LDAP.HP.com:389/ou=People,DC=hp,DC=com WHERE hpOrganizationChart=Procurve*')
/*
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT givenName,sn,manager FROM LDAP://LDAP.HP.com:389/ou=People,DC=hp,DC=com" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
*/
From the error message I was guessing that the issue is with the OLE DB provider "ADSDSOObject" which I am assuming is a COM object. I went to the Surface Area Configuration tool and enabled both OLE Automation and CLR integration, the later for a possible CLR stored procedure using the System.DirectoryServices assembly. Still no joy from the query.
Does anyone have some suggestions?
October 23, 2008 at 11:22 am
Try this:
SELECT givenName AS FirstName, sn AS LastName, uid as UserEmail, manager AS ManagerEmail
FROM OPENQUERY (ADSI, 'SELECT givenName,sn,uid,manager FROM ''LDAP://LDAP.HP.com:389/ou=People,DC=hp,DC=com'' WHERE hpOrganizationChart=''Procurve*''')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply