Unexpected Nulls

Hi - I found this posted a couple of times, but with no useful response,
so I am bringing it up again. I need, badly, to be able to query
eDirectory from SQL Server. I've tried two routes unsuccessfully:

1. Using ADSI to run LDAP queries against eDirectory, like:

SELECT mail
FROM OPENROWSET(
'AdsDsoObject'
,'User id=;Password=;ADSI Flag=0x11;Page Size=10000'
,'<LDAP://server/o=org>;(sn=aldrich);mail;subtree'
)

This 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 0x80040e21:  Data status returned from the provider:
[COLUMN_NAME=mail STATUS=DBSTATUS_E_CANTCONVERTVALUE]].

2. Using the ODBC driver. A linked server can be created in SQL Server
against the ODBC driver, and queries work EXCEPT if there's *any* empty
field. Rather than returning null correctly, one gets this error:

Server: Msg 7342, Level 16, State 1, Line 1
Unexpected NULL value returned for column '[MSDASQL].Given Name' from the
OLE DB provider 'MSDASQL'. This column cannot be NULL.
OLE DB error trace [Non-interface error:  Unexpected NULL value returned
for the column:  ProviderName='MSDASQL', TableName='[MSDASQL]',
ColumnName='Given Name'].

I saw a response comment in another thread to the effect that "that's OK
because NULL is a valid value for the driver to return." While perhaps
technically true, that is not of much use in solving the problem. 

I can find no coalesce() or isnull() function that works within the query
to the Novell driver to substitute another value for the nulls, nor can I
find a way to get the SQL Server to accept them.

Does anyone know a way to make this connection?

No joy in SQL server land. The three strategies I've found so far for
reading eDirectory data into SQL Server are not panning out. It's quite
frustrating.

I've tried LDAP queries using ADSI (I don't know of another way to create
LDAP queries directly in T-SQL). No joy. The linked server using LDAP
gives only the error DBSTATUS_E_CANTCONVERTVALUE for any fields.

I tried hooking up the ODBC driver from Novell as a linked server; no joy
due to the Unexpected Nulls problem reported here several times. I've not
discovered any concrete reason for that issue other than a vague reference
to "ODBC Compliance Levels" and how they might be different.

I tried using DTS to hook into the Novell ODBC driver, but, the times it
doesn't crash Enterprise Manager (about 60%) it will not allow one to
authenticate with any other rights than those of the user on a
workstation, so it's rather impractical to run as an automated job on a
server. One would have to manually log on with the credentials that can
"see" the eDirectory data, then manually run the DTS package. We need
a
more automated solution.

Has anyone out there actually made this work? I'll be eternally grateful
for any suggestions. The only reliable thing I have accomplished so far is
that I can import into Excel with the credentials of a user sitting at the
machine. Handy once in a while, but we can't run our "enterprise" on
that!

Hi

I have resubmitted the "null values" issue to engineering in the form
of an
enhancement request.  As stated in the other posts that you saw, we don't
see this as a non conformance to the sql standard.  However, I understand
it's frustrating that SQL server doesn't use them... have you contacted
Microsoft on this?

In regards to ADSI/LDAP, there are a couple of possible issues.  Older
versions of ADSI had problems with the Novell vendor specific schema
extensions, which conform to ldap spec.  There is an option in the ldap
server object "enable old adsi and Netscape schema output".  You might
try
that.  Additionally, there was a problem with ADSI reading the schema at
all, since it wouldn't cache.  Again, this was a non standard requirement.
However, eDirectory 86.x and later resolves the issue by providing
modification timestamp on the schema entry.  If neither of these is the
problem, there are still some lingering incompatibilities between what ADSI
expects for schema definitions and eDirectory standard ldap definitions.
Those can be coded around, but I don't know that you can explicitly define
schema types in SQL Server.  If you can post step-by-step instructions
(don't assume I know anything) for setting up the query, I've downloaded a
copy of SQL server and would be interested in reproducing the problem.

The ODBCNDS driver does use the current client state for its authentication,
and does not allow for a DSN authentication separate from the current client
settings.

Thank you
Susan

Thanks for getting back to me, Susan! I did subsequently find the section
of the eDirectory documentation with the settings for "enable non-standard
output"; however, I do not administer our netork (I'm a DBA); the solution
I ultimately settled on is a small .NET application I wrote that uses the
NWIDir active X control to seach the directory. While that is working,
it's not ideal, and is likely out of the reach of many DBAs who are not
.NET developers.

So, if you can test against SQL Server, you'll probably make at least some
people happier :-).

Here are the steps to try in SQL server. This is a doc from MS, which does
a better job explaining it than I could:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/distr
ibuted_query.asp

For the items in that page, you'd just want to set it to connect to your
eDirectory server instead of to Active Directory; ideally it seems like it
should work for either.

This should work in SQL Server 2000 -- I don't see anything in the doc
that is specific to 7.

To test the Novell ODBC driver, the steps are a little different, though
it's the same basic idea:

1. Create a Linked Server connected to the ODBC driver:

a. Open SQL Server Enterprise Manager
b. Navigate in the tree on the left to Security, Linked Servers
c. Right-click on Linked Servers and choose New Linked Server
d. Put in a name of your choice
e. Set the Provider Name to Microsoft OLE DB Provider for ODBC Driver
f. In the Data Source field, type in the name of the Novell ODBC Driver
Data Source installed on your machine.

2. Query against the linked server using Query Analyser. For example:

select nds_context
, nds_name
, [Given Name]
, Surname
, [internet email address] 
from openquery(
your_linked_server
, 'select nds_context, nds_Name, "Given Name", Surname, "internet
email
address" 
from UserNDS 
')

(replace "your_linked_server" with the name of the linked server you
created above)