SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Retrieve Active Directory using sql SP more than 1
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 05/20/2013 :  10:12:18  Show Profile  Reply with Quote
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

Edited by - micnie_2020 on 05/20/2013 10:31:54

James K
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 05/20/2013 :  11:11:17  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000