How to link different data sources together

Introduction

In today's enterprises, different types of data are sitting in various data stores. Relational data is most likely to be in a SQL Server database or Oracle database. Many enterprise applications have their own database with its own data and data model. User and account information reside in a directory like Active Directory or Active Directory Application Mode. For more information about the directory services, please refer to the following article. Smaller, more fluent and mobile data sits in Excel spreadsheets or Microsoft Access databases. Each enterprise has large amounts of files like Word documents, Excel Spreadsheets, HTML documents, etc. which are sitting on a file system. It's a common practice to use Microsoft Indexing Server to index all the files sitting on the file system or a web server. Microsoft Indexing Server creates an indexing catalog and also provides a SQL query interface which enables you to simply search the index. For more information about Microsoft Indexing Server please refer to the following article.

The challenge enterprises are facing now is how to provide a complete and easy view into all this data sitting in various stores. More over 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 varies slightly from one data source to another. For example directories like Active Directory provide a query capability but do not allow you to update, insert or delete data through SQL language. The same holds true for the SQL language of Microsoft Indexing Server. But most of the data sources provide the basic SQL query capability. Moreover Microsoft SQL Server allows you to link to other data stores which provide a SQL query language. This 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

Microsoft SQL Server provides the capability to link to other data sources which provide SQL query language. The Enterprise Manager of SQL Server shows in the left side navigation pane the "SQL Server groups" and under each all the database servers. It shows by default the local database server as "(local)". You can link other data stores to individual database servers. Expand a database server with the plus sign and you will find an entry called "Security". Expand the "Security" entry with the plus sign and you will see an entry called "Linked Servers". Expand the "Linked Servers" entry to see all the data stores which have been linked to this database server.

To link a new data store, right click on the entry "Linked Servers" and select "New Linked Server" from the popup menu. In the "Linked Server Properties" dialog you can enter a name for this linked server and then select whether you want to link another SQL Server database or any other data source. If you choose "SQL Server" then 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 then you would enter "Enterprise-Minds" as the linked server name. If you choose "Other data source" then 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 you are done, click OK to create a link to this new data store. This new data source will now show up under "Linked Servers" with whatever name you had entered.

Link Microsoft SQL Server to a directory

You can link SQL Server to a directory like Active Directory or Active Directory Application Mode. Create a linked server in the Enterprise Manager and give it a descriptive name like "Active Directory Link". Choose the option "Other data source" and select the data provider "OLE DB Provider for Microsoft Directory Services". As product name enter "Active Directory Services" and as data source enter "adsdatasource". When you expand the new linked server you will find two available items - Tables and Views. So SQL Server allows you to view any tables or views in this data source. But the data provider "OLE DB Provider for Microsoft Directory Services" does not provide either the tables or views. Therefore you will get the error "Error 7301: Could not obtain a required interface from OLEDB provider ADsDSOObject". 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 which 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].

So if the directory runs on your local machine and the local user "DirectoryUser" has access to it then 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 then you need to run the "MSSQLSERVER" service under this domain account.

Link Microsoft SQL Server to a Microsoft Indexing Server catalog

You can also link SQL Server to an Indexing Server catalog. Create a new linked server and give it a descriptive name, for example WEBCATALOG if you are linking to the Web Indexing Server catalog. Next select "Other data source" and choose as data provider "Microsoft OLE DB Provider for Indexing Service". Enter as product name "Index Server" and as data source the name of the Indexing Server catalog, for example Web. When you expand the new linked server you will find two available items - Tables and Views. So SQL Server allows you to view any tables or views in this data source. But the data provider "Microsoft OLE DB Provider for Indexing Service" neither provides the tables nor views. Therefore you will get an error "Error 7301: Could not obtain a required interface from OLEDB provider MSIDXS". 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 can also be linked to a Microsoft Access database. Create a new linked server and give it a descriptive name, for example "Booklist". Next select "Other data source" and choose as data provider "Microsoft Jet 4.0 OLE DB Provider". Enter as product name "Access" and as data source the path to Microsoft Access file, for example "c:\My files\Books.mdb". This data provider is able to show the list of tables and views. So when you expand the Table's or View's 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 can also 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 as data provider "Microsoft Jet 4.0 OLE DB Provider". Enter as product name "Jet 4.0", as data source the path to the Microsoft Excel spreadsheet - for example "c:\My files\Books.xls" - and finally as provider string enter "Excel 5.0". This data provider shows under Tables the list of excel worksheets. It does not show any Views.

There are many other providers available, which allow you to link up SQL Server with a variety of different data sources. The following link lists some of the available data providers which can be used by SQL Server.

How to query linked servers?

Now we have learnt how to link SQL Server with a variety of different data stores. To query data from a linked server you can use the command OPENQUERY in your FROM clause, which means you can query data from a linked server instead of a standard SQL Server table or view. The command OPENQUERY requires two parameters. First is the name of the linked server followed by the query you want to execute against this linked server. The following example assumes that you have a linked server with the name Books which 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. So 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

How to query a directory?

The "OLE DB Provider for Microsoft Directory Services" accepts two different syntaxes. One is called the LDAP dialect and the other the SQL dialect . As its name suggests the SQL dialect follows the SQL language syntax. You specify for the SELECT keyword the directory attributes you want to query for. For the ORDER BY and WHERE keywords you specify the attributes to sort on and to filter by. And for the FROM keyword you specify which directory and directory container to query for. The following example queries the container "OU=Enterprise-Minds,CN=Vancouver" in the Enterprise-Minds directory. It returns the common name and returns only the directory objects of the type group.

SELECT * FROM OPENQUERY(ADAM,
  'SELECT cn FROM ''LDAP://Enterprise-Minds/OU=Enterprise-Minds,
   CN=Vancouver'' WHERE objectClass=''group'' ')

Please note that the FROM clause needs to be put under two single quotes (escape sequencing of single quotes within a string which is already under single quotes). The following example queries the common name and the ADS path for any directory object residing in the container "OU=Enterprise-Minds,CN=Vancouver" in the Enterprise-Minds directory.

SELECT * FROM OPENQUERY(ADAM,
 'SELECT cn, ADsPath FROM ''LDAP://Enterprise-Minds/OU=Enterprise-Minds,
  CN=Vancouver'' ')

The LDAP dialect consists of four parts, each separated by a semicolon. The first part specifies the directory and the directory container, the second part the filter, the third part the list of attributes to return and the last part the scope of the search. The scope part can have three values - Base, OneLevel and SubTree. Base searches only the directory path that you specify. OneLevel searches the immediate children of the directory path that you specify. And SubTree searches all the descendants of the directory path that you specify. The following example returns the same information as the first SQL dialect example - the list of all group objects:

SELECT * FROM OPENQUERY(ADAM,
  '<LDAP://Enterprise-Minds/OU=Enterprise-Minds,CN=Vancouver>;
  (objectClass=group);cn;subtree')

The next example returns the same information as the second SQL dialect example - the common name and the ADS path of all directory objects:

SELECT * FROM OPENQUERY(ADAM,
  '<LDAP://Enterprise-Minds/OU=Enterprise-Minds,CN=Vancouver>;;
  cn,ADsPath;subtree')

You can find more information about the possible filter syntaxes at the following article. The filter syntax applies for both dialects with the difference that the values need to be put under single quotes for the SQL dialect. For example you can see that the value group in the SQL dialect example is under single quotes (double single quotes for escape sequencing) while it is not for the LDAP dialect example.

How to query Indexing Server catalogs?

Please refer to the following article for a detailed explanation on how to query Indexing Server catalogs. The following example queries the file name, path and the virtual path for all files in the linked Web Indexing Server catalog.

SELECT * FROM OPENQUERY(WEBCATALOG, 'SELECT FileName, 
                            Path, VPath FROM SCOPE()')

The next example queries for all the files which contain the value "default" and are residing under the "/Info" virtual directory and all its sub directories. It returns for all matches the file name, path and virtual path.

SELECT * FROM OPENQUERY(WEBCATALOG, 'SELECT FileName, Path, VPath FROM 
  SCOPE(''DEEP TRAVERSAL OF("/Info")'') WHERE CONTAINS(FileName,''default'') ')

Please note that the value in the SCOPE function is under two single quotes for escape sequencing, while the value of the DEEP TRAVERSAL OF function is under double quotes, according to the Indexing Server SQL syntax. As shown you can create very complex queries.

How to query Excel spreadsheets?

All worksheets present in a linked Excel spreadsheet are shown as a tables. Note that all the table names show at the end a $ sign. Also if the worksheet name has spaces in it, you see the table name surrounded by single quotes which you always drop in your query. The table name in your query string needs to be surrounded by square brackets. The following example queries for all entries in the worksheet called Summary.

SELECT * FROM OPENQUERY(Books, 'SELECT * FROM [Summary$]')

It is important that the Excel spreadsheet is not open in Excel, otherwise you will get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. 
    The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' 
    IDBInitialize::Initialize returned 0x80004005: The provider 
    did not give any information about the error.].

Summary

This article describes how you can link SQL Server with many different data sources. It describes step by step how to link SQL Server to a directory like Active Directory Application Mode, a Microsoft Indexing Server catalog, a Microsoft Access database and a Microsoft Excel spreadsheet. It also explains how you can query linked servers using the OPENQUERY command of SQL. Finally it explains the query syntax for directory queries, Indexing Server catalog queries and Excel spreadsheet queries. Queries against a Microsoft Access database use the same SQL syntax as you are used to from SQL Server.

Microsoft SQL Server provides a powerful way to link up many different data stores and provide one common query interface. This makes it very easy for applications to provide a common query capability against most data stores in the enterprise. This capability is not widely known in the developer community. Taking advantage of it can simplify your application tremendously if you need to query many different data sources and provide one common query interface and search result. If you have comments on this article or this topic, please contact me @ klaus_salchner@hotmail.com. I want to hear if you have learned something new. Contact me if you have questions about this topic or article.