Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using LinkedServer Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 11:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 10, 2014 5:35 PM
Points: 266, Visits: 562
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



Post #1424668
Posted Wednesday, February 27, 2013 5:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 10, 2014 5:35 PM
Points: 266, Visits: 562
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



Post #1424813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse