I have created a linked server from a Excel source and when I query or test the connection from the locally from the server everything is good but if I try the same from another machine with DBMS I get the following 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)
------------------------------
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Excel_Source". (Microsoft SQL Server, Error: 7399)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Server: Windows 2008
SQL: Server 2008 R2
The SQL service is running fine and the user attached has full administrator access.
Any help is appreciated.
Best Answer
You put no details about the involved paths in your post so I'm just making wild guesses.
You can run the query from any machine in your network but the path you save the excel file into must be accessible by the user executing the RDBMS service (usually local network or local service but may be customized at will).
The path you put into the linked server configuration is always local to the server. Even when running the query from a client machine the excel file must be on the server. (Well, you may specify a UNC network path when creating the linked server but that would make the whole thing more 'funny' to debug and handle because of permissions.)