ADSI problem

Question

ADSI problem

Kris-155042

Hall of Fame

Points: 3460
More actions
May 19, 2005 at 5:44 pm

#86549

I'm trying to get the member info out of Active Directory all I need is the login name and the groups they are a member of and I keep getting the following error:
Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow.
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' IRowset::GetData returned 0x40eda: Data status returned from the provider: [COLUMN_NAME=adspath STATUS=DBSTATUS_S_OK], [COLUMN_NAME=memberOf STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=department STATUS=DBSTATUS_S_OK], [COLUMN_NAME=mail STATUS=DBSTATUS_S_OK], [COLUMN_NAME=title STATUS=DBSTATUS_S_OK], [COLUMN_NAME=company STATUS=DBSTATUS...
SELECT *
INTO TMP_AD
FROM OpenQuery(
ADSI,'<;(&(objectCategory=Group">LDAP://chad/DC=companyname,DC=com,DC=au>;(&(objectCategory=Group));
displayName, description;subtree')
Is there a way I can get this info from AD?
Thanks for your help in advance.
Thanks,
Kris

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

The "[COLUMN_NAME=memberOf STATUS=DBSTATUS_E_CANTCONVERTVALUE]," is the cause of the error here. The data seems to be stored in weird formats for some fields in Active Directory. How you go about converting the information is beyond me. If you find out I'd like to know as well.

--------------------
Colt 45 - the original point and click interface

Cindy Hallum SSCommitted Points: 1808 More actions · Answer 2

I use the following vbscript that is scheduled to run nightly and it fills SQL tables with data from ADSI. Then our webpage can display it out of the SQL tables. Don't know if this will help, but here you go:

' VBScript to output to SQL Tables data from the Active Directory:
' tblDistGroups =========> GroupName, EmailAddress
' tblDistGroupMembers ===> GroupName, LoginName
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' started with vbscript (groupdump.vbs) authored by:
' Neil Hobson (neil.hobson@silversands.co.uk)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CWH 05.09.05 Adapted to write data to SQL tables. Will be
' scheduled to run nightly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim sArray(10000,2)

Dim sEmailArray(1000,2)

Dim iCount

Dim iCountEmail

Dim objConn

On Error Resume Next

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' set up connection to Active Directory

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set ADSIRootDSE = GetObject("LDAP://RootDSE")

ADSINamingNC = ADSIRootDSE.Get("rootDomainNamingContext")

Set ADSIConnection = CreateObject("ADODB.Connection")

ADSIConnection.Provider = "ADsDSOObject"

ADSIConnection.Open "ADs Provider"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' get recordset of all Groups in Active Directory

' this will get Distribution Groups & Security Groups

' but we'll bypass Security Groups below

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

ADSIQueryText = "<LDAP://" & ADSINamingNC & ">;(&(objectCategory=group));name,distinguishedName,groupType,mail;subtree"

Set ADSICommand = CreateObject("ADODB.Command")

Set ADSICommand.ActiveConnection = ADSIConnection

ADSICommand.CommandText = ADSIQueryText

ADSICommand.Properties("Page Size") = 100

ADSICommand.Properties("Timeout") = 60

ADSICommand.Properties("searchscope") = 2

ADSICommand.Properties("Cache Results") =

False

Set ADSIResult = ADSICommand.Execute

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' loop through list of Groups

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

iCount = 0

iCountEmail = 0

Do While not ADSIResult.EOF

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' get the name,distinguishedName,groupType attributes

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

sGroupName = ADSIResult.Fields("name").Value

sDistinguishedName = ADSIResult.Fields("distinguishedName").Value

iGroupType = ADSIResult.Fields("groupType").Value

sEmailAddress = ADSIResult.Fields("mail").Value

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' we only want Distribution Groups (exclude Security Groups)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If iGroupType > 0 Then

'''''''''''''''''''''''''''''''''''''''''''''''''''''

' put data into array to write to tblDistGroups

'''''''''''''''''''''''''''''''''''''''''''''''''''''

sEmailArray(iCountEmail,0) = sGroupName

sEmailArray(iCountEmail,1) = sEmailAddress

iCountEmail = iCountEmail + 1

'''''''''''''''''''''''''''''''''''''''''''''''''''''

' get all members of the Group

'''''''''''''''''''''''''''''''''''''''''''''''''''''

Set GetDN = GetObject("LDAP://" & sDistinguishedName)

strAllValues = GetDN.GetEx("member")

For each strValue in strAllValues

If Len(strValue) = 0 Then

'''''''''''''''''''''''''''''''''''''''''''''

' skip it since there are no members in this group

'''''''''''''''''''''''''''''''''''''''''''''

Else

'''''''''''''''''''''''''''''''''''''''''''''

' get the User's LoginName from sAMAccountName

'''''''''''''''''''''''''''''''''''''''''''''

Set objUser = GetObject( "LDAP://" & strValue )

sLoginName = objUser.sAMAccountName

Set objUser = Nothing

'''''''''''''''''''''''''''''''''''''''''''''

' and populate sArray with sGroupName,sLoginName

'''''''''''''''''''''''''''''''''''''''''''''

sArray(iCount,0) = sGroupName

sArray(iCount,1) = sLoginName

iCount = iCount + 1

End If

ADSIResult.MoveNext

Loop

ADSIConnection.Close

Set ADSIConnection = Nothing

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' open connection to SQL Server WEB database and clear tables

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set objConn = CreateObject("ADODB.Connection")

objConn.Open "Provider=sqloledb;Data Source=CTS-SYS\CTS;Initial Catalog=WEB;Integrated Security=SSPI;"

objConn.Execute("TRUNCATE TABLE dbo.tblDistGroups; ")

objConn.Execute("TRUNCATE TABLE dbo.tblDistGroupMembers; ")

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' put data from sEmailArray into SQL table tblDistGroups

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

For i = 0 to iCountEmail - 1

sGroupName = sEmailArray(i,0)

sEmailAddress = LCASE(sEmailArray(i,1))

sSQL = "IF NOT EXISTS (SELECT GroupName FROM dbo.tblDistGroups "

sSQL = sSQL & "WHERE GroupName = '" & sGroupName & "') "

sSQL = sSQL & "INSERT INTO dbo.tblDistGroups ( "

sSQL = sSQL & "GroupName, EmailAddress) VALUES ("

sSQL = sSQL & "'" & sGroupName & "', "

sSQL = sSQL & "'" & sEmailAddress & "'); "

objConn.Execute(sSQL)

' put data from sArray into SQL table tblDistGroupMembers

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

For i = 0 to iCount - 1

sGroupName = sArray(i,0)

sLoginName = sArray(i,1)

sSQL = "IF NOT EXISTS (SELECT GroupName FROM dbo.tblDistGroupMembers "

sSQL = sSQL & "WHERE GroupName = '" & sGroupName & "' "

sSQL = sSQL & "AND LoginName = '" & sLoginName & "') "

sSQL = sSQL & "INSERT INTO dbo.tblDistGroupMembers ( "

sSQL = sSQL & "GroupName, LoginName) VALUES ("

sSQL = sSQL & "'" & sGroupName & "', "

sSQL = sSQL & "'" & sLoginName & "'); "

objConn.Execute(sSQL)

' close up and get outahere

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

objConn.Close

Set objConn = Nothing

'MsgBox("finished and do not forget to get rid of this MsgBox and reenable On Error Resume Next")

Stop

Meu Email Dois SSC Rookie Points: 39 More actions · Answer 3

another solution:

Get group distinguishedname:

SELECT name,distinguishedname

FROM OPENQUERY( ADSI,

'SELECT Name, distinguishedname

FROM ''LDAP://xxxxxxx/DC=xxxx,DC=COM,DC=BR''

WHERE objectCategory = ''GROUP'' and CN = ''*FILTER*''')

order by name

Get group membership:

SELECT samaccountname,Name

FROM OPENQUERY( ADSI,

'SELECT samaccountname,Name

FROM ''LDAP://xxxxxxx/DC=xxxx,DC=COM,DC=BR''

WHERE objectCategory = ''user'' and memberof = ''group distinguishedname'' ')

order by name

[]s

Larangeira

Gabriel Rizzuti SSC Enthusiast Points: 138 More actions · Answer 4

look at this microsoft´s article

http://support.microsoft.com/kb/892098

Leon Chakmakian SSC Journeyman Points: 76 More actions · Answer 5

Meu Email Dois (7/21/2005)
another solution:
Get group distinguishedname:
SELECT name,
FROM OPENQUERY( ADSI,
'SELECT Name, distinguishedname
FROM ''LDAP://
WHERE objectCategory = ''GROUP'' and CN = ''* *''')
order by name
Get group membership:
SELECT samaccountname,Name
FROM OPENQUERY( ADSI,
'SELECT samaccountname,Name
FROM ''LDAP://
WHERE objectCategory = ''user'' and memberof = '' '' ')
order by name
[]s
Larangeira

Just wanted to say that this was the resolution for me. I had read the MS KB article before and thought I was going to have to go down that route. I setup a view that returns members of a specified CN. Thanks for your advice.