WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
In today's enterprises, different types of data are sitting in various data stores. Relational data is most likely in a SQL Server or Oracle database. Many enterprise applications have their own database with its own data and data model. User and account information resides in a Directory like Active Directory or Active Directory Application Mode. Smaller and more fluent and mobile data sits in Excel spreadsheets or Microsoft Access databases. Each enterprise has also large amounts of files like Word Documents, Excel Spreadsheets, HTML documents, and so forth that are sitting on a file system. It's common practice to use Microsoft Indexing Server to index all files sitting on the file system or a Web server. Microsoft Indexing Server creates an indexing catalog and provides also a SQL query interface that enables you to simply search the index.
The challenge enterprises are facing now is how to provide a complete and easy view into all this data sitting in various stores. Moreover, how can you search all this data in various data sources without having to create many different interfaces or create complex search mechanisms? The good news is that most of these data stores provide a SQL query language. The SQL language various slightly from data source to data source. For example, directories such as Active Directory provide a query capability but do not allow you to update, insert, or delete data through the SQL language. The same holds true for the SQL language of Microsoft Indexing Server. But, most data sources provide the basic SQL query capability. Moreover, Microsoft SQL Server allows you to link to other data stores that provide a SQL query language. This then allows you to create a single SQL query spawning multiple data sources. This article explains how you can achieve this.
Linking Other Data Stores to Microsoft SQL Server
To link a new data store, right-click on the "Linked Servers" entry and select "New Linked Server" from the popup menu. In the "Linked Server Properties" dialog, you enter the name for this linked server and then select whether you link another SQL Server database or any other data source. If you choose "SQL Server," the linked server name must be the name of the SQL Server database you want to link to. For example, if you have another SQL Server database running on the machine Enterprise-Minds,you would enter "Enterprise-Minds" as the linked server name. If you choose "Other data source," you need to select from a list of existing data source providers, for example "Microsoft Jet 4.0 OLE Provider." Based on which provider you choose, you need to enter the product name, data source, provider string, location, and catalog. When done, click Ok to create the link to this new data store. This new data source will now show up under "Linked Servers" with whatever name you entered.
Link Microsoft SQL Server to a Directory
You can link SQL Server to a directory such as Active Directory or Active Directory Application Mode. Create a linked server in Enterprise Manager and give it a descriptive name like "Active Directory Link." Choose the "Other data source" option and select the data provider "OLE DB Provider for Microsoft Directory Services." As a product name, enter "Active Directory Services" and as the data source, enter "adsdatasource." When you expand the newly linked server, you find two available items: Tables and Views. SQL Server allows you to view any tables and views in this data source. But, the data provider "OLE DB Provider for Microsoft Directory Services" provides neither tables nor views. Therefore, you will get the "Error 7301: Could not obtain a required interface from OLEDB provider ADsDSOObject" error. You can safely ignore the error message. You will be able to query the data store through the SQL query language.
It is important that you run the services "MSSQLSERVER" under an account that has access to the directory. If you run the services under the local system account, you will get the following error when you try to query the data store:
Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' CommandPrepare::Prepare returned 0x80040e14].
If the directory runs on your local machine and the local user "DirectoryUser" has access to it, you need to run the "MSSQLSERVER" service under this account. If the directory is running somewhere in your domain and the domain user "DomainUser" has access to it, you need to run the "MSSQLSERVER" service under this domain account.
Link Microsoft SQL Server to a Microsoft Indexing Server catalog
You also can link SQL Server to an Indexing Server catalog. Create a new linked server and give it again a descriptive name—for example, WEBCATALOG if you link to the Web Indexing Server catalog. Next, select "Other data source" and choose "Microsoft OLE DB Provider for Indexing Service" as a data provider. Enter "Index Server" as the product name and, as the data source, the name of the Indexing Server catalog; for example, Web. When you expand the newly linked server, you find two available items: Tables and Views. SQL Server allows you to view any tables and views in this data source. But, the data provider "Microsoft OLE DB Provider for Indexing Service" provides neither tables nor views. Therefore, you will get the "Error 7301: Could not obtain a required interface from OLEDB provider MSIDXS" error. You can safely ignore the error message. You will be able to query the data store through the SQL query language.
Link Microsoft SQL Server to a Microsoft Access Database
SQL server also can be linked to a Microsoft Access database. Create a new linked server and, again, give it a descriptive name; for example, "Booklist." Next, select "Other data source" and choose "Microsoft Jet 4.0 OLE DB Provider" as the data provider. Enter "Access" as the product name and, as the data source, the path to the Microsoft Access file; for example, "c:\My files\Books.mdb." This data provider is able to show the list of tables and views. When you expand the Tables or Views item under this linked server, you will see the list of tables or views in the Microsoft Access database.
Link Microsoft SQL Server to a Microsoft Excel Spreadsheet
SQL server also can be linked to a Microsoft Excel spreadsheet. Create a new linked server and give it a descriptive name; for example, "Booklist." Next, select "Other data source" and choose "Microsoft Jet 4.0 OLE DB Provider" as the data provider. Enter "Jet 4.0" as the product name, as the data source, the path to the Microsoft Excel spreadsheet—for example "c:\My files\Books.xls"—and finally as the provider string enter "Excel 5.0." This data provider shows the list of excel worksheets under Tables. It does not show any Views.
There are many other providers available that allow you to link up SQL Server with a variety of different data sources. This link lists some of the available data providers that can be used by SQL server.
How to Query Linked Servers
Now, you have learned how to link SQL Server with a variety of different data stores. To query data from a linked server, you use the OPENQUERY command in your FROM clause, meaning you query data from a linked server instead of a standard SQL Server table or view. The OPENQUERY command requires two parameters: the name of the linked server followed by the query you want to execute against this linked server. The following example assumes you have a linked server with the name Books which itself has a table called Books:
SELECT * FROM OPENQUERY(Books, 'SELECT * FROM Books') AS Books
The query you pass along in the OPENQUERY command needs to be supported by the linked data provider. Depending on the data provider, it might vary slightly. You can join the data from any linked data source or SQL Server table together. The next example assumes that you query a linked data source and join it together with a SQL Server table called BookValue.
SELECT * FROM OPENQUERY(Books, 'SELECT * FROM Books') AS Books INNER JOIN BookValue ON Books.ID = BookValue.ID