SQLTeam.com | Weblogs | Forums

Linked Server to Excel SQL 2014

sql2014

#1

I am having an issue with the creation of a linked server to Excel .xlsx file.

Create a Linked Server to Excel this way

EXEC sp_addlinkedserver
    @server = 'ExcelServer',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\FHR_LCL_Mthly_2015.xlsx', --Path to your excel file
    @provstr = 'Excel12.0'

It does not work so far. I get the error 7309.

Without a linked Server
This works 

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\FHR_LCL_Mthly_2015.xlsx', 'Select * from [Local Data Table$]') 

Thank for any help!


#2

looking here: Excel Import to SQL Server using Linked Servers

I see that the suggested T-SQL is:

EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\excel-sql-server.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

I note that your version is missing part of the suggested provider string.