July 6, 2016 at 5:59 pm
I am trying to set up linked servers to four Excel spreadsheets. I am using SQL Server 2008 R2 and MS Office 2010. I also have both Microsoft Access database engine 2010 and Microsoft Access database engine 2007 installed on the SQL server. The spreadsheets are all on the D drive of the SQL server as well.
Among many others, I have tried
EXEC master.dbo.sp_addlinkedserver
@server = N'ExcelColumbia',
@srvproduct=N'Excel',
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'D:\P21DocumentLinks\International Inventory\International_Inventoy_Columbia.xlsx',
@provstr=N'Excel 12.0;HDR=Yes'
EXEC sp_addlinkedserver
@server = 'ExcelColumbia',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'D:\P21DocumentLinks\International Inventory\International_Inventoy_Columbia.xls',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
I always get an error message about Cannot initialize the data source object of OLE provider "Microsoft.ACE.OLEDB.12.0" for linked server "ExcelColumbia"
Or something close to that.
Any help will be greatly appreciated.
Thanks.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
July 9, 2016 at 12:39 pm
Both are set up with full access, so that is not the problem.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
July 9, 2016 at 3:04 pm
Current status is I have created three different linked servers, none of which work. Below is the server configs and the logins. The Temp folders have full access for everyone. Included is the errors I get when I try the test connection.
EXEC master.dbo.sp_addlinkedserver
@server = N'lsxColumbia'
, @srvproduct=N'Excel'
, @provider=N'Microsoft.ACE.OLEDB.12.0'
, @datasrc=N'D:\P21DocumentLinks\International Inventory\International_Inventoy_Columbia.xlsx'
, @provstr=N'Excel 12.0;HDR=Yes'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lsxColumbia',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
ERROR: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "lsxCoumbia".
OLE DB provider "Microsoft.ACE.OLDBE.12.0" for linked server "lsxColumbia" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
EXEC master.dbo.sp_addlinkedserver
@server = N'lsComumbia'
, @srvproduct=N'Excel'
, @provider=N'Microsoft.Jet.OLEDB.4.0'
, @datasrc=N'D:\P21DocumentLinks\International Inventory\International_Inventoy_Columbia.xls'
, @provstr=N'Excel 5.0'
, @catalog=N'Columbia'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lsComumbia',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
ERROR: OLE DB provider "Mirosoft.Jet.OLEDB.4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)
EXEC master.dbo.sp_addlinkedserver
@server = N'lstColumbia'
, @srvproduct=N'CSVFLATFILE'
, @provider=N'Microsoft.Jet.OLEDB.4.0'
, @datasrc=N'\\192.168.15.20\Local\Prophet 21\International Inventory\International_Inventoy_Mexico.txt'
, @provstr=N'text'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lstColumbia',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
ERROR: OLE DB provider "Mirosoft.Jet.OLEDB.4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308)
I ran…
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
With result:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
I still get the same error message.
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
July 14, 2016 at 5:29 am
Hi John,
It seems like provider issue, can you double check with provider part? Whether appropriate provider is available or not - if not download and install it, also verify provider properties which "Allow Inprocess" Checked properly.
It may fix your problem.
Cheers,
LK.
July 18, 2016 at 12:48 am
Hi
I believe that to connect to Office documents you need to use a password (even if the doc doesn't have a password)
Linked server properties --> Security tab
"Be made with this security context" put "Admin" as username and leave pwd blank
OR
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVERNAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'ADMIN',@rmtpassword=null
July 18, 2016 at 6:08 pm
I've dropped all but the one link as follows. Both the SQL Server and Excel are 64 bit so the ACE drivers should work.
EXEC sp_addlinkedserver
@server = 'lnkColumbia'
, @srvproduct = 'Excel'
, @provider = 'Microsoft.ACE.OLEDB.12.0'
, @datasrc=N'D:\P21DocumentLinks\International Inventory\International_Inventoy_Columbia.xlsx'
, @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'lnkColumbia',@useself=N'False',@locallogin=NULL,@rmtuser=N'ADMIN',@rmtpassword=null
Clicking on test connects results...
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 initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "lnkColumbia".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "lnkColumbia" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
John
SQL Rebel without a Where Clause
SQL/Epicor/Prophet21/VISUAL/e.i.e.i.o.
July 19, 2016 at 1:54 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply