June 8, 2012 at 9:19 am
For code reuse, I am trying to get a table valued function to return users of a given AD group name. I can easily get this with hard-coding the group name. But because OpenQuery wont accept parameters, I can't insert my group name there. And because functions can't call dynamic SQL, I can't do it via dynamic sql. I have seen people do it with CLR, but I rather not go that route. I can use a stored procedure + cursor and iterate through each group and store the results into real tables and create a cache, but I rather query Active Directory itself to save space, but I rather do the caching then the CLR. Any approach I am missing on how to do this?
The following works fine:
SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, sn
FROM ''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''
WHERE objectCategory=''Person'' AND objectClass=''USER'' AND memberOf=''CN=SomeGroupName,OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''') a
WHERE sn IS NOT NULL
The following gives me the error:
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
CREATE FUNCTION [dbo].queryADGroupMembers
(
@group nvarchar(255)
)
RETURNS @rtnTable TABLE
(
userName nvarchar(50)
)
AS
BEGIN
DECLARE @tsql VARCHAR(MAX)
SET @tsql = 'INSERT INTO @rtnTable SELECT DISTINCT sAMAccountName
FROM OPENQUERY(ADSI, ''SELECT sAMAccountName, sn
FROM ''''LDAP://OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC''''
WHERE objectCategory=''''Person'''' AND objectClass=''''USER'''' AND memberOf=''''CN=' + @group + ',OU=SomeOU,OU=SomeOtherOU,DC=SomeDC,DC=SomeOtherDC'''''') a
WHERE sn IS NOT NULL'
EXEC (@tsql)
RETURN
END
June 8, 2012 at 9:33 am
You will need to do this a stored procedure.
June 11, 2012 at 5:03 pm
I tried implementing a similar function but there seems to be issues with linked server LDAP queries. It was taking a long time to execute the openquery and it was getting incomplete/inconsistent result sets. I suggest you implement a CLR table value function and use .NET to query the server.
June 12, 2012 at 6:25 am
Thanks for your help.
June 12, 2012 at 7:57 am
Dan Price-242213 (6/11/2012)
I tried implementing a similar function but there seems to be issues with linked server LDAP queries. It was taking a long time to execute the openquery and it was getting incomplete/inconsistent result sets. I suggest you implement a CLR table value function and use .NET to query the server.
+1 on this being a great opportunity for CLR.
As for your actual failure in the UDF you created, pretty sure you cannot interact with a table variable from within dynamic SQL.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 13, 2012 at 3:53 am
Kevin,
I think you're right. What I did was use a CROSS APPLY to run the .Net CLR UDF once for each record in the result set. It worked a charm.
-- Dan
May 20, 2014 at 11:03 pm
Hi, I haven used stored procedure in sql server to get the data using LADP Query,I am getting errors.Could you guide on this.
ALTER PROCEDURE Sample_GetDetails(@len nvarchar(max),@OutNTID VARCHAR(300) OUTPUT)
AS
BEGIN
Declare @name nvarchar(100)
Declare @man nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(MAX)
set @name=@len
set @man='SELECT CN as Member, displayName, company, department, manager, c,mail
FROM OPENQUERY (ADSI, ''SELECT cn, displayName, company, department, manager, c,mail FROM ''''LDAP://......''''' +
'WHERE cn = ''''' + @name + ''''''')
and cn is not null'
SET @ParmDefinition = N'@pNTID varchar(300) OUTPUT'
EXECUTE sp_executesql @man,
@ParmDefinition,
@pNTID=@OutNTID OUTPUT
END
May 21, 2014 at 8:05 am
sudheerapps436 (5/20/2014)
Hi, I haven used stored procedure in sql server to get the data using LADP Query,I am getting errors.Could you guide on this.ALTER PROCEDURE Sample_GetDetails(@len nvarchar(max),@OutNTID VARCHAR(300) OUTPUT)
AS
BEGIN
Declare @name nvarchar(100)
Declare @man nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(MAX)
set @name=@len
set @man='SELECT CN as Member, displayName, company, department, manager, c,mail
FROM OPENQUERY (ADSI, ''SELECT cn, displayName, company, department, manager, c,mail FROM ''''LDAP://......''''' +
'WHERE cn = ''''' + @name + ''''''')
and cn is not null'
SET @ParmDefinition = N'@pNTID varchar(300) OUTPUT'
EXECUTE sp_executesql @man,
@ParmDefinition,
@pNTID=@OutNTID OUTPUT
END
One, you should probably have started a new thread for this problem. Two, can't really help you much as all you have told us is that you are getting errors. That is like taking your car to the mechanic and saying it does work, please fix it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply