1

I have to create a view querying AD group using OpenQuery. I have gone through this question but this doesn't work in case of an SQL view since we can't have local variables in a view.

Please see my below view query:

SELECT 
    emp.PersonnelID
FROM 
    OPENQUERY(
        MyLinkerServer, 
        'SELECT 
            sAMAccountName 
        FROM 
            ''LDAP://DC=**domain**,DC=organisation,DC=com''
        WHERE 
            objectCategory = ''Person'' AND
            objectClass = ''user'' AND
            memberOf = ''CN=ADGroupName,OU=SecurityGroups,DC=**domain**,DC=organisation,DC=com'' '
    ) a
    INNER JOIN Employee emp ON 
        emp.Alias = a.sAMAccountName

I want to replace domain by fetching it from another table called config which has columns - key and value. I should be able to select value passing a key and that value should replace domain

Community
  • 1
  • 1
NayeemKhan
  • 1,210
  • 7
  • 19
  • 38
  • Is it a strict requirement to have this in a view? A stored procedure/function won't do? The reason I'm asking is because the only option to do this (AFAICT) is to write a dynamic query. You can't use a dynamic query in a view. – TT. Oct 19 '16 at 10:02
  • I discussed same with my solution architect. Use of view is no longer required, we are going to create Stored Procedure. Thanks!! – NayeemKhan Oct 20 '16 at 06:47
  • You're welcome, good luck! – TT. Oct 20 '16 at 09:21

0 Answers0