Friday, February 20, 2009

Excel 2007 Linked Server

Lets see How to create a linked server with Excel 2007. Well most of the stuff you'll find on the net on how to create a linked Server is using Microsoft Jet 4.0 OLE DB Provider which is no longer able to connect to Excel 2007.

So here you go...
We will use "Microsoft.ACE.OLEDB.12.0" which come with Office 2007 pack. If you don't have it on Provider's list you can download it from
2007 Office System Driver: Data Connectivity Components

After downloading and Installing the ACE driver you can create the Linked server as

exec sp_addLinkedServer
@server= 'XLFile',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'E:\Mangal.xls',
@provstr = 'Excel 12.0; HDR=No'

If you have the Column names in Excel do HDR=yes.

Now you can query your Excel just like any table - SELECT * FROM XLFile...[sheet1$]

Or if you don't want to create a Linked Server, and just want to execute the Excel then do -
SELECT
* FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=E:\Mangal.xls;HDR=No', 'Select * from [Sheet1$]' )

And if you execute the above query and you get the error
Msg 15281, Level 16, State 1, Line 1 SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off. That means first you need to enable the 'Ad Hoc Distributed Queries'.

For that first execute the following scripts. 
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO

For Creating Linked Server from Management Studio:

Open the Management studio, connect to your server. And follow the steps
1. Go to "Server Objects" then Right CLick on "Linked Servers" and write "New Linked Server".
2. Give any name to your linked server. In our example I've given "XL".
3. Now select Server Type as "Other Data Source". Now select the provider"Microsoft Office 12.0 Access Database Engine OLE DB Provider."
4. Product Name : ACE 12.0
5. Date Source : E:\Mangal.xls --- Here you need to put the path of your Excel Sheet.
e.g. C:\Folder\New.xls Provider String : Excel 12.0; HDR=No

Again remember if you have column names in Excel then HDR=Yes.


- Mangal Pardeshi.

3 comments:

  1. çalışmadı!
    do not work

    ReplyDelete
  2. I followed the steps For Creating Linked Server from Management Studio and all is ok. But after I´m trying to make a SP:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    CREATE Proc [dbo].[spActualizarPreciosDesdeExcel]
    As
    Begin
    UPDATE dbo.ARTICULOS_RUTAS
    SET aruPrecio = Precio
    FROM EXCEL...[Precios$]
    JOIN dbo.ARTICULOS_RUTAS ON aruCodArt = CodigoArticulo
    AND aruCodRut = CodigoRuta
    End

    And I get: Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

    Help me please.

    ReplyDelete