Query against linked LDAP server fails

[David Hubbell]

We have setup a linked server on a SQL Server 2000 SP3
server to an iPlanet Directory server version 5.1. We
can query for adsPath but cannot query for any other LDAP
attribues. The query:

SELECT * FROM
OpenQuery
(ADSI,'<LDAP://ldap.sierras.com/OU=People,O=sierras.com>;
(&(objectclass=person) (sn=dhubb));adspath, cn ;subtree')

fails with:
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=cn
STATUS=DBSTATUS_E_CANTCONVERTVALUE], [COLUMN_NAME=adspath
STATUS=DBSTATUS_S_OK]].

If I remove the request for "cn" the query works. Any
suggestions?

[David Portas]

Unfortunately I can't answer your question.

But I'm interested in how to link to an LDAP server from SQL. I've
considered doing it in the past. Is there an OLEDB provider available? Where
from?

--
David Portas
------------
Please reply only to the newsgroup
--
"David Hubbell" <David....@sierras.com> wrote in message
news:003d01c2cff3$7c445210$a001...@phx.gbl...

[David Hubbell]

We use the Active Directory Service provider included in Windows 2000
and XP. It doesn't do us much good if it doesn't work correctly. :-(

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Scot Rose [MSFT]]

Hi David, I am looking into this and will let you know what I can find out.

Want to know more? Check out the MSDN Library at http://msdn.microsoft.com or the Microsoft Knowledge Base at http://support.microsoft.com

Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : sc...@online.microsoft.com <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.

[Scot Rose [MSFT]]

Hi David,

Try removing the Space before the CN in the string 'adspath, cn' should be 'adspath,cn'

The Parser might be hiccuping on the space... Let me know if that does the trick or not...

[David Hubbell]

Scott,

That's not it. I found that I can select any LDAP attribute that is a
single-value attribute but not those that have multiple values for the
attribute, such as cn.

[Scot Rose [MSFT]]

Hi David...

I have been digging into this a bit more (After going through a dozen case logs I got a little bleary eyed) but the upshot I have found is that basically the ADSI
provider is not meant to be called from SQL server, SQL server can not handle multiple columns returned from the provider.

The solution I was able to find is to use ADO code to call ADSI to retrieve the data

A request for this capability has been forwarded to the SQL Group and perhaps at some future version it will be allowed..

[Carlos Rodriguez]

Try using OLE DB Provider for Microsoft Directory Services

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:upAFikD0CHA.2736@TK2MSFTNGP09...

[Rupert Martin]

I've followed the instructions at the following link, though I cannot get
this to work:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/netdir/adsi
/distributed_query.asp

When I try and execute a modified version of the query from that page, I get
the following error message from SQL Query Analyzer:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'ADSDSOObject'.

I'm using:
Windows 2000 SP3
SQL 2000 SP3

Anybody have any ideas as to why this is happening?

Rupert Martin.

"Carlos Rodriguez" <crodr...@devonyx.com> wrote in message
news:uwh5ZkE1CHA.2592@TK2MSFTNGP10...