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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
1635 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  
 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