LDAP query in Stored Procedure.

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 in Stored Procedure.

Author

Topic

Arma
Starting Member

10 Posts

Posted - 2010-05-13 : 13:56:23

Hi All,
I'have made a linked server with AD and query to get all members of specific AD group.
Now when I try to make stored procedure to pass parameter as AD group name these apostrophs ' making me trouble.
Did anyone played with these stuff.
Thanx a lot.

SELECT * FROM OPENQUERY( TORAD, 'SELECT DisplayName,mail
FROM ''LDAP://OU=xxxx,DC=xxx,DC=xxx,DC=NET''WHERE objectCategory = ''CN=Person,CN=Schema,CN=Configuration,DC=xxx,DC=NET''
AND memberOf = ''CN=TORMosshscont,OU=xxxxx,DC=xxxx,DC=xxx,DC=NET'' ORDER BY DisplayName ')
GO

SP ISSUE

ALTER PROCEDURE [dbo].[AD_SP_GroupUsers]
-- Add the parameters for the stored procedure here
@ADGroup nvarchar(250)

AS
--SET @ADGroup = 'TORMossAcctDocFull'
BEGIN
SET NOCOUNT ON;
SELECT * FROM OPENQUERY( TORAD, 'SELECT DisplayName,mail
FROM ''LDAP://OU=xxx,DC=xxx,DC=xxx,DC=NET''WHERE objectCategory = ''CN=Person,CN=Schema,CN=Configuration,DC=CCLIND,DC=NET''
AND memberOf = ''CN=' +@ADGroup+',OU=xxxx,DC=xxx,DC=xxx,DC=NET'' ORDER BY DisplayName' )
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 14:05:59

shouldnt it be like


EXEC('SELECT * FROM OPENQUERY( TORAD, ''SELECT DisplayName,mail
FROM ''''LDAP://OU=xxx,DC=xxx,DC=xxx,DC=NET''''WHERE objectCategory = ''''CN=Person,CN=Schema,CN=Configuration,DC=CCLIND,DC=NET''''
AND memberOf = ''''CN=' +@ADGroup+',OU=xxxx,DC=xxx,DC=xxx,DC=NET'''' ORDER BY DisplayName' )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Arma
Starting Member

10 Posts

Posted - 2010-05-13 : 14:17:56

Almost there when I pass a parameter Igot error.

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'CN'.

(1 row(s) affected)

Thanx man

Arma
Starting Member

10 Posts

Posted - 2010-05-13 : 15:34:48

How can I make stored procedure of this query to filter groups.
Query works fine.

SELECT * FROM OPENQUERY( TORAD, 'SELECT DisplayName,mail
FROM ''LDAP://OU=xxx,DC=xxx,DC=xxx,DC=NET''WHERE memberOf = ''CN=MYGROUPNAME,OU=xxx,DC=xxx,DC=xxx,DC=NET'' ORDER BY DisplayName ')
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources