Using LinkedServer

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  February 27, 2013 at 11:29 am

  #274681

  Hello comunity

  On SQL server 2008 most of the time i have used a linkedserver to access excel file with this script:

  EXEC sp_addlinkedserver @server = N'ExcelDataSource',

  @srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0',

  @datasrc=N'C:\Invoice\teste.xls',

  @provstr=N'EXCEL 12.0' ;

  GO

  EXEC sp_addlinkedsrvlogin 'ExcelDataSource', 'false';

  GO

  and finally the query:

  SELECT *

  FROM ExcelDataSource...[folha1$];

  Now on sql server 2012, after install the provider "Microsoft.ACE.OLEDB.12.0"

  IF i run test connection , i have this error:

  TITLE: Microsoft SQL Server Management Studio

  ------------------------------

  The test connection to the linked server failed.

  ------------------------------

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------

  Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelDataSource". (Microsoft SQL Server, Error: 7302)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

  ------------------------------

  BUTTONS:

  OK

  ------------------------------

  If i run the Select statment, i have this one:

  Msg 7302, Level 16, State 1, Line 2

  Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelDataSource".

  Someone could me some help to solve this issue.

  Note : my OS is Win8 64 bit and Microsoft SQL Server Business Intelligence (64-bit)

  Many thanks,

  Luis Santos

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  February 27, 2013 at 5:02 pm

  #1591792

  Hello comunity

  I found the solution, please visit this link:

  http://akawn.com/blog/2012/01/query-and-update-an-excel-2010-spread-sheet-via-a-linked-server/

  Very good post and explanation for all of you that use this tecnique like me.

  Best regards

  Luis Santos