Configure linked servers for Excel and MS SQL Server in MS SQL Server 2005

Linked servers allows

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers

Quote from MSDN

This way we have an abstract layer to other data sources (different from MS SQL Server) and we can connect them using T-SQL of MS SQL Server and only one instance of it. In addition, we can join tables from different resources which is completely transparent for our stored procedures or ad hoc queries.

In this post I’ll show you how you can configure a linked server for Excel file and a linked server for a MS SQL Server database. Assume that we have an Excel file named people.xls as those below

We also have a MS SQL Server database named test with one table named test. Well, the names are not too descriptive but for the sample are good enough.

Assume that we’d like to use the information from the excel file and also to extend it with some information like address and city of the person. I added external_id column into the table in order to be able to join the table with the Excel file. This field contains the same values as id column of the excel file.

Before to configure linked services you should ensure that DTC (Distributed Transaction Coordinator) Service is started.

You can configure both using the Microsoft SQL Server management Studio but I’ll use a query to do so. SQL Server contains a stored procedure named sp_addlinkedserver. It allows you to create linked servers.

   1: exec master.dbo.sp_addlinkedserver 

   2:     @server = N‘Excel’, 

   3:     @srvproduct=N‘Excel’, 

   4:     @provider=N‘Microsoft.Jet.OLEDB.4.0′, 

   5:     @datasrc=N‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\people.xls’, 

   6:     @provstr=N‘Excel 5.0′;

The @server parameter is the name that we will use to connect the server. The @srvproduct is not required here but left it with a value of Excel. The @provider parameter shows which OLE DB provider we gonna use. In our case it is Microsoft.Jet.OLEDB.4.0 provider. The @datasrc is the full path to the Excel file and @provstr should be Excel 5.0. After executing the procedure we can access the linked server. I’ll use sp_tables_ex stored procedure to retrieve all sheets (tables) that current linked server contains.

   1: exec sp_tables_ex @table_server=N‘Excel’

The result is below.

Note that you should not use the Excel file with any other program when you are using it from the SQL Server. If you do so you will get an exception like those below.

Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Excel" reported an error. The provider did not give any information about the error.

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Excel".

Having the linked server named Excel we can query the file using a script like those below.

   1: select * from Excel…Sheet1$

The result is below.

Now let’s connect our SQL Server database as a linked server. To do so I’ll use the same stored procedure as before but with a different OLE DB provider.

   1: exec master.dbo.sp_addlinkedserver 

   2:     @server = N‘SQL’, 

   3:     @srvproduct=N”, 

   4:     @provider=N‘SQLNCLI’, 

   5:     @datasrc=N‘GOCHEVA\SQLEXPRESS’;

We are still not ready to connect to the SQL linked server. Before trying to access it we should allow remote connections to the target SQL Server Instance. You can complete this action via SQL Server 2005 Surface Area Configuration as shown below.

Now I can connect it and get all databases that are the SQL Server instance contains and I also can get their table information. Using sp_catalogs stored procedure I’ll get all DBs.

   1: exec sp_catalogs @server_name=N‘SQL’

And the result.

I’ll use sp_tables_ex to get all tables of a specific catalog (database) of the SQL linked server.

   1: exec sp_tables_ex 

   2:     @table_server=N‘SQL’, 

   3:     @table_schema=N‘dbo’, 

   4:     @table_catalog=N‘test’;

And the result.

Now, we can use an as hoc query using T-SQL and to retrieve a joined information from both files.

   1: select s.*, t.address, t.city 

   2: from Excel…Sheet1$ as s, SQL.test.dbo.test as t

   3: where s.id = t.external_id

And the result is.

That’s all for now. have fun using SQL Linked Server option.