-
ADSI and Other Databases
End Goal: SSRS report joining Citrix Users/Login Time with AD Info
I recently got my linked server to active directory working. Now to actually use it I need to be able to join on the SAMAccountName. How can this be done considering it require OPENQUERY command? There is also a ~901 object fetch limit on AD, so grabbing the whole thing and putting it into a temp table is out of the question i believe.
This is what I have so far.
Code:
declare @SQL as varchar(8000), @UserName as varchar(10)
set @UserName = 'MYID'
select @SQL =
replace(
'SELECT ADquery.*
FROM openquery( ADSI,
''SELECT NAME,SAMAccountName
FROM ''''LDAP://DC=my,DC=domain''''
WHERE objectCategory = ''''Person''''
AND objectClass =''''user''''
AND SAMAccountName = ''''@UserName'''''') ADquery',
'@UserName',@UserName)
exec (@SQL)
this works and was taken from
http://dbaspot.com/sqlserver-server/...y-problem.html
Can someone assist if I had a table called dbo.CitrixUsers and column UserID (for example)
and lastly to make all of this look nice. I would want to be able to create a view or function out of this so that I could use it in a SSRS report.
thanks for any feedback :)