Tuesday, October 9, 2012

Query Excel spreadsheet on SQL Server 2008 64 bit with Linked Server

For SQL Server 64 bit, if you use Microsoft.Jet.OLEDB.4.0, you may experience this issue:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

To fix this, please follow the following:

1. Download and install Microsoft Access Database Engine 2010 Redistributable at this address http://www.microsoft.com/en-us/download/details.aspx?id=13255 

image

2. After installing, create a linked server to your Excel file

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL_LINKED_SERVER_NAME'
                    , @srvproduct=N'Excel'
                    , @provider=N'Microsoft.ACE.OLEDB.12.0'
                    , @datasrc=N'C:\Your_Excel_File.xls'
                    , @provstr=N'Excel 12.0';

GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL_LINKED_SERVER_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;

GO

3. Run OPENQUERY to get data from your Excel file

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

No comments:

Post a Comment