AD query from sql server 2008 Description field gives error
I am trying to query Active Directory with the following SQL query which works fine.
SELECT * FROM OpenQuery(ADSI,
'SELECT title, displayName, sAMAccountName, givenName,
telephoneNumber, facsimileTelephoneNumber, sn,
mail, physicalDeliveryOfficeName
FROM ''LDAP://DC=mydomain,DC=org''
WHERE sAMAccountName = ''myUser''')
When I add the attribute description
, I get the following error:
Msg 7346, Level 16, State 2, Line 6
Unable to get row data from OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert data value for reasons other than sign mismatch or overflow.
The SQL / ADO query capabilities in Active Directory are very limited - I will try to avoid using them if possible.
The reason is that an attribute description
in Active Directory is multivalued - it can potentially contain multiple values (which is not possible in a relational database model).
Hence, the SQL / ADO Query Provider cannot read any of these multivalued attributes from LDAP - there is no way or option or checkbox to enable this - it just isn't possible.
I have the same problem where I get a field Description
from AD when trying to use ADsDSOObject
and ADO
.
you need to take measurements using instructions like For Each
or Do until ... EOF
.
Here's a good example