SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select from active directory with LDAP:// Paramete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Liat001
Starting Member

17 Posts

Posted - 07/22/2010 :  03:06:38  Show Profile  Reply with Quote
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

United Kingdom
616 Posts

Posted - 07/22/2010 :  10:36:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1634 Posts

Posted - 07/22/2010 :  13:36:16  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Edited by - yosiasz on 07/22/2010 13:54:04
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000