How to use Excel with SQL Server linked servers and distributed queries

Microsoft SQL Server supports connections to other OLE DB data sources on a persistent or an ad hoc basis. The persistent connection is known as a linked server; an ad hoc connection that is made for the sake of a single query is known as a distributed query.

Microsoft Excel workbooks are one type of OLE DB data source that you can query through SQL Server in this manner. This article describes the syntax that is necessary to configure an Excel data source as a linked server, as well as the syntax that is necessary to use a distributed query that queries an Excel data source. 

Querying an Excel data source on a linked server

You can use SQL Server Management Studio or Enterprise Manager, a system stored procedure, SQL-DMO (Distributed Management Objects), or SMO (SQL Server Management Objects) to configure an Excel data source as a SQL Server linked server. (SMO are only available for Microsoft SQL Server 2005.) In all of these cases, you must always set the following four properties:

• The name that you want to use for the linked server.
• The OLE DB Provider that is to be used for the connection.
• The data source or complete path and file name for the Excel workbook.
• The provider string, which identifies the target as an Excel workbook. By default, the Jet Provider expects an Access database.

The system stored procedure sp_addlinkedserver also expects the @srvproduct property, which can be any string value.

Note If you are using SQL Server 2005, you must specify a value that is not empty for the Product name property in SQL Server Management Studio or for the @srvproduct property in the stored procedure for an Excel data source.

Using SQL Server Management Studio or Enterprise Manager to configure an Excel data source as a linked server

SQL Server Management Studio (SQL Server 2005)

1. In SQL Server Management Studio, expand Server Objects in Object Explorer.
2. Right-click Linked Servers, and then click New linked server.
3. In the left pane, select the General page, and then follow these steps:
   1. In the first text box, type any name for the linked server.
   2. Select the Other data source option.
   3. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
   4. In the Product name box, type Excel for the name of the OLE DB data source.
   5. In the Data source box, type the full path and file name of the Excel file.
   6. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
   7. Click OK to create the new linked server.

Note In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains.

Enterprise Manager (SQL Server 2000)

1. In Enterprise Manager, click to expand the Security folder.
2. Right-click Linked Servers, and then click New linked server.
3. On the General tab, follow these steps:
   1. In the first text box, type any name for the linked server.
   2. In the Server type box, click Other data source.
   3. In the Provider name list, click Microsoft Jet 4.0 OLE DB Provider.
   4. In the Data source box, type the full path and file name of the Excel file.
   5. In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.
   6. Click OK to create the new linked server.
4. Click to expand the new linked server name to expand the list of objects that it contains.
5. Under the new linked server name, click Tables. Notice that your worksheets and named ranges appear in the right pane.

Using a stored procedure to configure an Excel data source as a linked server

You can also use the system stored procedure sp_addlinkedserver to configure an Excel data source as a linked server:

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.Jet.OLEDB.4.0'
SET @datasrc = 'c:\book1.xls'
SET @provstr = 'Excel 8.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider, 
@datasrc, @location, @provstr, @catalog
    

As noted above, this stored procedure requires an additional, arbitrary string value for the @srvproduct argument, which appears as "Product name" in the Enterprise Manager and SQL Server Management Studio configuration. The @location and @catalog arguments are not used.

Using SQL-DMO to configure an Excel data source as a linked server

You can use SQL Distributed Management Objects to configure an Excel data source as a linked server programmatically from Microsoft Visual Basic or another programming language. You must supply the same four arguments that are required in the Enterprise Manager and SQL Server Management Studio configuration.

Private Sub Command1_Click()
    Dim s As SQLDMO.SQLServer
    Dim ls As SQLDMO.LinkedServer
    Set s = New SQLDMO.SQLServer
    s.Connect "(local)", "sa", "password"
    Set ls = New SQLDMO.LinkedServer
    With ls
        .Name = "XLTEST_DMO"
        .ProviderName = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = "c:\book1.xls"
        .ProviderString = "Excel 8.0"
    End With
    s.LinkedServers.Add ls
    s.Close
End Sub
    

Using SMO to configure an Excel data source as a linked server

In SQL Server 2005, you can use SQL Server Management Objects (SMO) to configure an Excel data source as a linked server programmatically. To do this, you can use Microsoft Visual Basic .NET or another programming language. You must supply the arguments that are required in the SQL Server Management Studio configuration. The SMO object model extends and supersedes the Distributed Management Objects (SQL-DMO) object model. Because SMO is compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, you can also use SMO for configuration of SQL Server 2000.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xls"
                .ProviderString = "Excel 8.0"
            End With
            ls.Create()
            MessageBox.Show("New linked Server has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

Querying an Excel data source on a linked server

After you configure an Excel data source as a linked server, you can easily query its data from Query Analyzer or another client application. For example, to retrieve the rows of data that are stored in Sheet1 of your Excel file, the following code uses the linked server that you configured by using SQL-DMO:

SELECT * FROM XLTEST_DMO...Sheet1$
    

You can also use OPENQUERY to query the Excel linked server in a "passthrough" manner, as follows:

SELECT * FROM OPENQUERY(XLTEST_DMO, 'SELECT * FROM [Sheet1$]')
    

The first argument that OPENQUERY expects is the linked server name. Delimiters are required for worksheet names, as shown above.

You can also obtain a list of all the tables that are available on the Excel linked server by using the following query:

EXECUTE SP_TABLES_EX 'XLTEST_DMO'
    

Querying an Excel data source by using distributed queries

You can use SQL Server distributed queries and the OPENDATASOURCE or OPENROWSET function to query infrequently accessed Excel data sources on an ad hoc basis.

Note If you are using SQL Server 2005, make sure that you have enabled the Ad Hoc Distributed Queries option by using SQL Server Surface Area Configuration, as in the following example:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0')...Sheet1$
    

Note that OPENROWSET uses an uncommon syntax for the second ("Provider String") argument:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Excel 8.0;Database=c:\book1.xls', Sheet1$)
    

The syntax that an ActiveX Data Objects (ADO) developer may expect to use for the second ("Provider String") argument with OPENROWSET:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
   'Data Source=c:\book1.xls;Extended Properties=Excel 8.0', Sheet1$)
    

This syntax raises the following error from the Jet Provider:

Could not find installable ISAM.

Note This error also occurs if you enter DataSource instead of Data Source. For example, the following argument is incorrect:

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'DataSource=c:\book1.xls;Extended Properties=Excel 8