ldap query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

ldap query

Author

Topic

mchohan
Starting Member

39 Posts

Posted - 2010-07-07 : 10:56:47

Hi

I want to run the below query, however I want to be able to use a variable for the ou. I've tried the below and it's giving me errors. Could someone point me into the right direction on how to do this? thanks.

declare @ou varchar(1000)
set @ou = 'IT'

set @ldap ='SELECT samAccountName, givenname ,sn[fullname],givenname
FROM OPENQUERY(ADSI,''SELECT objectGUID,samAccountName, givenName, sn, legacyExchangeDN FROM ''LDAP://ou=@ou dept,dc=xxx,dc=xxx'' WHERE objectClass=''user'''')
WHERE givenName IS NOT NULL
order by [sn]'

exec sp_executesql @ldap

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 11:05:43

Not sure but try it like this:
declare @ou varchar(1000)
set @ou = 'IT'

set @ldap ='SELECT samAccountName, givenname ,sn[fullname],givenname
FROM OPENQUERY(ADSI,''SELECT objectGUID,samAccountName, givenName, sn, legacyExchangeDN FROM ''LDAP://ou='+@ou+' dept,dc=xxx,dc=xxx'' WHERE objectClass=''user'''')
WHERE givenName IS NOT NULL
order by [sn]'

exec sp_executesql @ldap

No, you're never too old to Yak'n'Roll if you're too young to die.

mchohan
Starting Member

39 Posts

Posted - 2010-07-07 : 11:39:54

thanks for that. I now get the following error:

Incorrect syntax near 'LDAP'.

I've hit this before, not sure how to format the full string, any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 11:40:33

set your declares for @ldap and @ou from varchar to nvarchar.

No, you're never too old to Yak'n'Roll if you're too young to die.

mchohan
Starting Member

39 Posts

Posted - 2010-07-07 : 11:42:55

Ok, I now got the following:

declare @ou varchar(1000)
set @ou = 'IT dept'

declare @ldap nvarchar(1000)

set @ldap ='SELECT objectGUID
FROM OPENQUERY(ADSI,"SELECT objectGUID,samAccountName, givenName, sn, legacyExchangeDN FROM "LDAP://ou='+@ou+' dept,dc=xxx,dc=xxx" WHERE objectClass="user"")
WHERE givenName IS NOT NULL
order by [sn]'

exec sp_executesql @ldap

I get the follownig error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'SELECT objectGUID,samAccountName, givenName, sn, legacyExchangeDN FROM '.
Msg 1038, Level 15, State 4, Line 2
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

What does that mean?
Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-07 : 12:09:31

There is a mismatch in quoting and I think I have it now.
Take exactly this AND there are NO double quotes!!
There are all single quotes!!


declare @ldap nvarchar(1000)
declare @ou nvarchar(1000)
set @ou = 'IT dept'


set @ldap ='SELECT objectGUID
FROM OPENQUERY(ADSI,''SELECT objectGUID,samAccountName, givenName, sn, legacyExchangeDN FROM ''''LDAP://ou='+@ou+',dc=xxx,dc=xxx'''' WHERE objectClass=''''user'''''') 
WHERE givenName IS NOT NULL 
order by [sn]'

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources