February 27, 2013 at 11:29 am
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
February 27, 2013 at 5:02 pm
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy