I have the following code to connect to my active directory and then pull users in a certain group. As the code is below it works fine, however I have to hard code the department I want to look in.
I am trying to pass a parameter to the openqueries second parameter (second code) but I keep getting an error, I know its a problem with my quotation marks, any help would be greatly appreciated thanks,
select *
from openquery(ADSI, '
select
givenName,
sn,
sAMAccountName,
displayName,
mail,
telephoneNumber,
mobile,
physicalDeliveryOfficeName,
department,
division
from ''LDAP://DC=directorysync,DC=cfc, DC=com''
where objectCategory = ''Person''
and objectClass = ''user''
and department = ''Production''
AND displayname <> ''0_UW_Template_Remote''
ORDER BY displayName
')
select *
from openquery(ADSI, '
select
givenName,
sn,
sAMAccountName,
displayName,
mail,
telephoneNumber,
mobile,
physicalDeliveryOfficeName,
department,
division
from ''LDAP://DC=directorysync,DC=cfc, DC=com''
where objectCategory = ''Person''
and objectClass = ''user''
and department = '''+@Department+'''
AND displayname <> ''0_UW_Template_Remote''
ORDER BY displayName
')
Best Answer
You cannot construct the query within the
openquery
call, you'll need to build the query in a variable then execute it.