大网数据广告活动
域与目录服务

Joining Heterogeneous Data

· 52CE 我爱测

Typical organizations store data in multiple heterogeneous databases. Human Resources data may be stored in SQL Server, while account management data is stored in the directory. Other data may be stored in proprietary formats.

Typical organizations store data in multiple heterogeneous databases. Human Resources data may be stored in SQL Server, while account management data is stored in the directory. Other data may be stored in proprietary formats.

With, SQL Server 7.0, ADSI, and the OLE DB Provider, it is possible to join data from Active Directory to data in SQL Server and create a view of the joined data.

To join Active Directory Data with SQL Server Data

  1. Run the SQL Query Analyzer (Start | Programs | Microsoft SQL Server 7.0)

  2. Log on to the SQL Server computer.

  3. Execute the following line (by highlighting it and pressing CTRL+E):

    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 
    'ADSDSOObject', 'adsdatasource'
    GO
    

    In this line, the arguments for the sp_addlinkedserver System Stored Procedure are as follows:

    • " ADSI" is the server argument, which will be the name of this linked server.
    • "Active Directory Services" is the srvproduct argument, which is the name of the OLE DB data source that you are adding as a linked server.
    • "ADSDSOObject" is the provider_name argument and indicates you are using the OLE DB Provider.
    • "adsdatasource" is the data_source argument, which is the name of the data source as interpreted by the OLE DB Provider.

    You can now use the linked server to access Active Directory from SQL Server.

  4. The next example performs a query using the OPENQUERY statement. This statement has two arguments: ADSI, which is the name of the linked server that you just created, and a query statement. The query statement contains the following items:

    • The SELECT statement contains the list of data that will be obtained from the directory service. You will need to use the LDAP display name to indicate which data you are searching for.
    • The FROM statement contains the name of the linked directory server where this information will be obtained from.
    • The WHERE statement provides the search conditions. In this example, it is searching for users.

    Type and execute:

    SELECT * FROM OPENQUERY( ADSI, 
        'SELECT name, adsPath 
         FROM 'LDAP://DC=Fabrikam,DC=com' 
         WHERE objectCategory = 'Person' AND objectClass= 'user'')
    

    You can also use the ADSI LDAP dialect. For example:

    SELECT * FROM OPENQUERY(ADSI,
        '<LDAP://DC=Fabrikam,DC=COM>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')
    

    In the previous example, the LDAP query has four parts:

    • "<LDAP://DC=Fabrikam,DC=COM>" is the distinguished name of the directory server to search.
    • "(&(objectCategory=Person)(objectClass=user))" is the LDAP search filter (see Search Filter Syntax).
    • "name, adspath" are the names (using the LDAP display name format) of the attributes to retrieve.
    • "subtree" indicates the scope of the search.

Related topics

Creating and Executing a View

Source: Microsoft Learn / Windows Win32 documentation by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / Windows Win32 documentation
适用范围
Windows Win32 API · Source date 05/31/2018
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。