Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Querying ADSI Expand / Collapse
Author
Message
Posted Thursday, November 30, 2006 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:07 PM
Points: 19, Visits: 425

I am having trouble querying one particular active directory attribute, managedObjects.  The error message that I'm getting is:

Msg 7346, Level 16, State 2, Line 1

Cannot get the data of the row from the OLE DB provider "ADSDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.

The query runs fine if that field is NULL.  I found a kb article on Microsoft's site stating that it is an issue, but we have a later version of the file listed (activeds.dll).  The cause listed is as follows:

The ADSI OLE DB provider represents I8 fields by using a DBTYPE_VARIANT(IDispatch) data type and does not convert the I8 data type to a string. SQL Server requires the provider to convert a VARIANT data type to a WSTR data type.

I've tried converting the field to nvarchar, nchar, text and none of them works.  Here's my query:

SELECT Manager, Name, sAMAccountName, userPrincipalName, convert(nvarchar, ManagedObjects)

FROM OPENQUERY(ADSI,

'SELECT Name, Manager, sAMAccountName, userPrincipalName, DisplayName, ManagedObjects

FROM ''LDAP://OU=xxxx,DC=xxxx,DC=com''

WHERE objectCategory = ''User''

ORDER BY Name')

Does it need to be converted within the openquery statement, or is there something else that I can try?

Thanks!

Post #326838
Posted Monday, December 4, 2006 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Post #327396
Posted Wednesday, September 12, 2012 5:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 10, 2012 6:39 AM
Points: 1, Visits: 11
I am having a similar issue. If I run the following query in SQL 2005 it runs fine but gives an error in SQL 2008.

SELECT *
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, ipPhone, department,
displayName, homePhone, mail, mobile, company,
sn, l, telephoneNumber, givenName, title,
physicalDeliveryOfficeName
FROM ''LDAP://OU=xxx,DC=xxx,DC=xxx'' WHERE
objectCategory = ''Person'' AND objectClass = ''user'' ')
where company is not null

The error is -
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "ADSI".

It seems to error after a certain number of rows (which is different everyday). If I amend the query to include the Top N clause it runs fine. That is not a solution, searching on the internet has yielded no clues.

Any thoughts anyone.
Post #1357891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse