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 2008 Forums
 Transact-SQL (2008)
 Retrieve Active Directory using sql SP more than 1

Author  Topic 

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-05-20 : 10:12:18
Hi All,

I have statement below:-

Alter FUNCTION [dbo].[GetAuthenticatedUser]
(
-- Add the parameters for the function here
@userId nvarchar(50),
@password nvarchar(50)

--EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
--sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'false', @locallogin = 'xyz\sg.services', @rmtuser = 'xyz\sg.services', @rmtpassword = 'spEdrEG6'
--EXEC sp_dropserver 'ADSI'
--sp_droplinkedsrvlogin 'ADSI','xyz\sg.services'




--sp_configure
-- 'show advanced options', 1;
--GO

--RECONFIGURE
--;
--GO

--sp_configure
-- 'Ad Hoc Distributed Queries', 1;
--GO

--RECONFIGURE
--;
--GO


/*--
SQL server group => Security => Linked server => ADSI (the name of your linked server)

right click on it then go in the "security menu" then choose the option "be made using this security context"
--*/



)
RETURNS TABLE
AS
RETURN
(
--Then execute the openquery like this:
select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName
FROM ''LDAP://N''xyz.com/OU=Users,OU=Singapore,OU=.AP,DC=xyz,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''

union all
SELECT NAME,SAMAccountName
FROM ''LDAP://N''xyz.com/OU=Users,OU=Japan,OU=.AP,DC=xyz,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''

')


)


I tried to use union all as above bold & strikethrough method but failed.

Please advise.

Thank you.

Regards,
Micheale

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-20 : 11:11:17
YOu could try making them into two separate queries against LDAP and unioning the results like shown below:
SELECT  *
FROM OPENQUERY(ADSI, 'SELECT NAME,SAMAccountName
FROM ''LDAP://N''xyz.com/OU=Users,OU=Singapore,OU=.AP,DC=xyz,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')
UNION ALL
SELECT *
FROM OPENQUERY(ADSI, 'SELECT NAME,SAMAccountName
FROM ''LDAP://N''xyz.com/OU=Users,OU=Japan,OU=.AP,DC=xyz,DC=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')
Go to Top of Page
   

- Advertisement -