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.
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 ASRETURN ( --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 allSELECT 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,SAMAccountNameFROM ''LDAP://N''xyz.com/OU=Users,OU=Singapore,OU=.AP,DC=xyz,DC=com''WHERE objectCategory = ''Person'' AND objectClass = ''user''')UNION ALLSELECT *FROM OPENQUERY(ADSI, 'SELECT NAME,SAMAccountNameFROM ''LDAP://N''xyz.com/OU=Users,OU=Japan,OU=.AP,DC=xyz,DC=com''WHERE objectCategory = ''Person'' AND objectClass = ''user''') |
|
|
|
|
|
|
|