Active Directory as Linked Server no data being returned

We're having problems establishing a Linked Server that points at
Active Directory in SQL Server 2000. We've executed
sp_addlinkedserver and have created a view of active directory users.
However, when querying the view, no data is returned. Are we maybe
not connecting correctly? We are not receiving any error messages.
Any help would be appreciated.
Here is how we created our view:
CREATE VIEW AD_USERS AS
SELECT * FROM OpenQuery(
ADSI,'<LDAP://DC=domain,DC=com>;(& (objectCategory=Person)(objectClass=user
))
;sn,
name, adspath,title;subtree')
(except domain is replaced with our domain name)
Thanx in advance for any help!!Try querying the same OU etc. from a command line tool to make sure that
the query is returning data.
The command line tool is a Windows tools called ldifde.
Example:
ldifde -d "CN=Users,DC=<domain>" -l servicePrincipalName -F
<NewoutputUsers>.txt This syntax creates a file named NewoutputUsers.txt
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We get data returned with the command line tool when
replacing <domain> in your example with our domain name.
What do we need to change in our OpenQuery statement to
get results with SQL Server?

quote:

---

>--Original Message--
>Try querying the same OU etc. from a command line tool

---

to make sure that

quote:

---

>the query is returning data.
>The command line tool is a Windows tools called ldifde.
>Example:
>ldifde -d "CN=Users,DC=<domain>" -l

---

servicePrincipalName -F

quote:

---

> <NewoutputUsers>.txt This syntax creates a file

---

named NewoutputUsers.txt

quote:

---

>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

---

confers no rights.

quote:

---

>
>.
>

---

|||So, I tried to create a view to use for querying Active
Directory:
CREATE VIEW AD_USERS AS
SELECT *
FROM OPENQUERY( ADSI,
'SELECT *
FROM ''LDAP://CN=users,DC=<domain>,DC=com''')
When I query the AD_USERS view using query analyzer on
a client workstation running Enterprise Manager, there
are errors produced. When I query the same view using
query analyzer on the SQL Server server, no errors are
produced. However, even though I am selecting * from the
view, the only field to come back is adsPath. When I try
to specify another field name in the query that creates
the view, I receive the following error:
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=cn
STATUS=DBSTATUS_E_CANTCONVERTVALUE]].
Is there something that needs to be changed in the query?
Help, please.

quote:

---

>--Original Message--
>Try querying the same OU etc. from a command line tool

---

to make sure that

quote:

---

>the query is returning data.
>The command line tool is a Windows tools called ldifde.
>Example:
>ldifde -d "CN=Users,DC=<domain>" -l

---

servicePrincipalName -F

quote:

---

> <NewoutputUsers>.txt This syntax creates a file

---

named NewoutputUsers.txt

quote:

---

>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

---

confers no rights.

quote:

---

>
>.
>

---

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)