Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select from active directory with LDAP:// Paramete

Author  Topic 

Liat001
Starting Member

17 Posts

Posted - 2010-07-22 : 03:06:38
Hi All,
I need Help .
I try to select data from active directory.
with LDAP dinamic.
(@ADLDAP Parameter will Pass with the SP)
For now,Example:
DECLARE @ADLDAP varchar (50)

SET @ADLDAP ='OU=abc,OU=abc,DC=abc,DC=abc


DECLARE @SQL VARCHAR(MAX)

SET @SQL = ' SELECT sAMAccountName AS LogonName

INTO #UsersTmp
FROM OPENQUERY(ADSI,''SELECT name,sn,givenName,mail, sAMAccountName, mobile, telephoneNumber,facsimileTelephoneNumber, department,physicalDeliveryOfficeName,c,streetAddress,l,manager,wWWHomePage,postalCode,Company
FROM ''''' + 'LDAP://' + @ADLDAP + '''''
WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''''
+''')'

EXEC ( @SQL)

Then I want to use the #usersTmp.
SELECT * FROM #usersTmp

But the table not recognize .
Wat can I do?
Thanks Alot.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2010-07-22 : 10:36:22
The #table declared in the script is not in the same scope as the dynamic SQL statement.

To work-around this, you can try:
- using a normal table instead of a temporray table or,
- create a linked server to Active Directory, and not use dynamic sql, or
- ...
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-22 : 13:36:16
you are missing a single quote on
SET @ADLDAP ='OU=abc,OU=abc,DC=abc,DC=abc'


Could you try this?

DECLARE @ADLDAP varchar (50)

SET @ADLDAP ='OU=abc,OU=abc,DC=abc,DC=abc'


DECLARE @SQL VARCHAR(MAX)
DECLARE @Select VARCHAR(MAX)

SET @Select =
'SELECT name,
sn,
givenName,
mail,
sAMAccountName,
mobile,
telephoneNumber,
facsimileTelephoneNumber,
department,
physicalDeliveryOfficeName,
c,
streetAddress,
l,
manager,
wWWHomePage,
postalCode,
Company
FROM LDAP://' + @ADLDAP + ' WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''''


SET @SQL = 'SELECT sAMAccountName AS LogonName INTO #UsersTmp FROM OPENQUERY(ADSI,''' + @Select + ''' )'
SET @SQL = @SQL + CHAR(10) + ' SELECT * FROM #UsersTmp'
PRINT @SQL
EXEC ( @SQL)


If you don't have the passion to help people, you have no passion
Go to Top of Page
   

- Advertisement -