'The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

I'm trying to run the following statement but am receiving the error messages just below. I have researched answers to no end and none have worked for me. I'm running Office 365 (64bit). I have loaded the Microsoft Access Database Engine (64bit). This is in Visual Studio 2013 with SSDT as well as SQL Server 2012. I do not have access to changing environment or startup parameters to SQL Server. Any help is appreciated.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.15.0', 
    'Excel 12.0;Database=C:\Users\UserName\Folder\SomeFile.xlsx;;HDR=NO;IMEX=1', [Table 1$])

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.15.0" for linked server "(null)".

Here's what I have tried:

First, I ran...

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Followed by...with no love.

EXEC sys.sp_addsrvrolemember @loginame = N'<<Domain\User>>', @rolename = N'sysadmin';
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.15.0', N'DynamicParameters', 1 
GO

I have changed the code to read Microsoft.ACE.OLEDB.12.0 as I have seen that as well, still no love.

I have also checked permissions of C:\Users\MSSQLSERVER\AppData\Local\Temp and C:Windows\ServiceProfiles\NetworkService\AppData\Local which have granted Full Control for the following: System, MSSQLSERVER, and Administrators, Network Service (on the latter).

Still no love.

Lastly, I have tried changing to the 32bit version of the Microsoft Access Database Engine which has persisted in not working.

Help, anyone?

Solution 1:^[1]

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

This solves the issue. For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.

Solution 2:^[2]

This is for my reference, as I encountered a variety of SQL error messages while trying to connect with provider. Other answers prescribe "try this, then this, then this". I appreciate the other answers, but I like to pair specific solutions with specific problems

Error

...provider did not give information...Cannot initialize data source object...

Error Numbers

7399, 7303

Error Detail

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  The provider did not give any information about the error. 
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object
  of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

File was open. Close it.

Credit

https://stackoverflow.com/a/29369868/1175496

Error

Access denied...Cannot get the column information...

Error Numbers

7399, 7350

Error Detail

Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. 
  Access denied.
Msg 7350, Level 16, State 2, Line 2 Cannot get the column information 
  from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Give access

Credit

https://stackoverflow.com/a/27509955/1175496

Error

No value given for one or more required parameters....Cannot execute the query ...

Error Numbers

???, 7320

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "select [Col A], [Col A] FROM?$Sheet" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Column names might be wrong. Do [Col A] and [Col B] actually exist in your spreadsheet?

Error

"Unspecified error"...Cannot initialize data source object...

Error Numbers

???, 7303

Error Detail

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Solution

Run SSMS as admin. See this question.

Other References

Other answers which suggest modifying properties. Not sure how modifying these two properties (checking them or unchecking them) would help.

Solution 3:^[3]

Instead of changing the user, I've found this advise:

https://social.technet.microsoft.com/Forums/lync/en-US/bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb/the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access?forum=sqldataaccess

This might help someone else out - after trying every solution to trying and fix this error on SQL 64..

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

..I found an article here...

http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/

..which suggested I give Everyone full permission on this folder..

C:\Users\SQL Service account name\AppData\Local\Temp

And hey presto! My query suddenly burst into life. I punched the air in delight.

Edwaldo

Solution 4:^[4]

Make sure the Excel file isn't open.

Solution 5:^[5]

In the SQL Server, try these steps:

Open one database.
Click in the option Server Object.
Click in Linked Servers.
Click in Providers.
Right click on Microsoft.ACE.OLEDB.12.0 and click Properties.
Uncheck all the options and close.

Solution 6:^[6]

Here is specifically what worked for me only when the Excel file being queried was not open and when running the SQL Server Service as me [as a user that has access to the file system]. I see pieces of my answer already given elsewhere, so I apologize for any redundancy, but for the sake of a more succinct answer:

USE [master]
GO

EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO


SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                'Excel 12.0;Database=C:\MyExcelFile.xlsx',
                'SELECT * FROM [MyExcelSheetName$]')

Solution 7:^[7]

For me it was the permission on the file's folder. I had to add all permissions for user "Everyone". Folder/properties/security -> add user everyone and set all permissions.

Solution 8:^[8]

I'm running SQL Server 2014 64 bit on Windows 10. I tried everything, the thing that made it work was:

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0

I don't know why the AllowInProcess turned off makes it work but that was the key in my case. Thank you for the suggestion of turning all the options off on the linkserver.

Solution 9:^[9]

I had the same issues even after running all those EXEC stored procedure commands that you see on every answer/blog, but when I ran SSMS as administrator, the problems went away. Since, I didn't want to do that, I opened Services, went to SQL Server (MyServer), right clicked, selected properties. On the Log On tab, "This account:" was selected with and listed my SQL Server account/password. I instead checked "Local System account" and ticked the "Allow service to interact with desktop". Then I stopped and started the service. Now, I can run the same query as the original poster mentions without running as administrator in SSMS. This probably worked because I am running SQL Server on my home desktop.

I'd also like to point out that you can use GUI in SSMS to enable adhoc access (and many other options). In SSMS, go to Linked Servers>Providers, right click on the provider, and select properties. Then you can just check/uncheck the ones you want.

Solution 10:^[10]

Removing Read-only CheckBox from C:\Users\SQL Service account name\AppData\Local\Temp worked for me.

Solution 11:^[11]

There are two crucial nonobvious settings that I've discovered when tuning linked servers on Excel under SQL Server 2014. With those settings, ' FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.16.0'', ...)' as well as '... FROM [' + @srv_name + ']...data AS xl ...' function properly.

Linked server creation

This is just for context.

DECLARE @DB_NAME NVARCHAR(30) = DB_NAME();
DECLARE @srv_name nvarchar(64) = N'<srv_base_name>@' + @DB_NAME; --to distinguish linked server usage by different databases

EXEC sp_addlinkedserver
    @server=@srv_name,
    @srvproduct=N'OLE DB Provider for ACE 16.0',
    @provider= N'Microsoft.ACE.OLEDB.16.0',
    @datasrc= '<local_file_path>\<excel_workbook_name>.xlsx',
    @provstr= N'Excel 12.0;HDR=YES;IMEX=0'
;

@datasrc: Encoding is crucial here: varchar instead of nvarchar.
@provstr: Version, settings and sytax are important!
@provider: Specify the provider installed in your SQL Server environment. Available providers are enumerated under Server Objects::Linked Servers::Providers in SSMS's Object Explorer.

Providing access to the linked server under specific SQL Server logins

This is the first crucial setting. Even for SA like for any other SQL Server login:

EXEC sp_addlinkedsrvlogin @rmtsrvname = @srv_name, @locallogin = N'sa', @useself = N'False', @rmtuser = N'admin', @rmtpassword = N''
;

@rmtuser: It should be admin. Actually, there is no any admin in Windows logins on the system at the same time.
@rmtpassword: It should be an empty string.

Providing access to the linked server via ad hoc queries

This is the second crucial setting. Setting Ad Hoc Distributed Queries to 1 is not enough. One should set to 0 the DisallowAdhocAccess registry key explicitly for the driver specified to @provider:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.DEF_INST\Providers\Microsoft.ACE.OLEDB.16.0]
"DisallowAdhocAccess"=dword:00000000

Solution 12:^[12]

Close SQL Server Management Studio. Type Services.msc in the run command to open the services window.
Search for SQL Server Service and right click it and select properties.
In the Log On Tab, select system account/or select your domain ID and Account and password.
Once it finds your login name press OK.
Now type your login’s passwords in both the fields.
Restart the services so that the new changes are applied as shown in figure below.
Now start SQL Server Management Studio and try to run the query if still not working try a system restart.

... or execute the following query:

USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO

Solution 13:^[13]

In our case, it helped to add a parameter for SQL Server service:

Go to Services.msc, select SQL Server Service and open Properties.
Choose Startup Parameters and add new parameter –g512
Restart SQL server service.

Solution 14:^[14]

In my case this problem was occuring because i was accessing the file from shared folder by using computerName. OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='\ntpc0100\MysharedFolder\KPI_tempData\VariablePayoutDetails.xls';IMEX=1;'','.....) Insted of using computerName ntpc0100(whatever your machine name) you should specify IPaddress of your machine. for example:- OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0; Database='\193.34.23.200\MysharedFolder\KPI_tempData\KPIVariablePayoutDetails.xls'....);

Solution 15:^[15]

For me, these two things helped on different occasions:

1) If you've just installed the MS Access runtime, reboot the server. Bouncing the database instance isn't enough.

2) As well as making sure the Excel file isn't open, check you haven't got Windows Explorer open with the preview pane switched on - that locks it too.

Solution 16:^[16]

Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1; 
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1;
GO

Insert into OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\upload_test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
SELECT ColumnNames FROM Your_table -- Sheet Should be already Present along with headers

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO

Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO

Solution 17:^[17]

With SQL 2014, I changed the SQL Server Service (MSSQL) to run as LocalSystem. This solved the problem for me.

It used to work as NT_SERVICE\MSSQL$MSSQL fine under 2008, from what I remember.

Solution 18:^[18]

I had the exact same error message, and tried the suggested solutions in this thread but without success.

what solved the problem for me is opening the .xlsx file and saving it as .xls (excel 2003) file.

maybe the file was corrupted or in a different format, and saving it again fixed it.

Solution 19:^[19]

This is my error code:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'ByStore$'. Make sure the object exists and that you spell its name and the path name correctly. If 'ByStore$' is not a local object, check your network connection or contact the server administrator.".

Msg 7350, Level 16, State 2, Procedure PeopleCounter_daily, Line 26

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

My problem was the excel file was missing at the path. Just put the file with the correct sheet will do.

Solution 20:^[20]

I had same problem and the reason was "This copy of Microsoft Office is not activated" on remote side. (XLSX linked from the other machine)

Solution 21:^[21]

For future reference, granting SQL Server service account Full Control for the "C:Windows\ServiceProfiles\NetworkService\AppData\Local\Temp" solved the following issue for me.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Solution 22:^[22]

On top of the previously mentioned error numbers combinations, error numbers 7399 and 7303 can mean both that the file is open and the file isn't even an Excel file. So keep that in mind aswell.

Solution 23:^[23]

3 things that gives error -File is open -Need full read/open access to the share folter -File is PassWord protected.

File name is case sensitive E.G. IMEX=1;Database=\FPTsite.mywebsite.com\E$\FTP\Folder1\FoldDer2\202202_MyFile.xlsx' is not the same as IMEX=1;Database=\FPTsite.mywebsite.com\E$\FTP\Folder1\FoldDer2\202202_myfile.xlsx'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Rahul Goel
Solution 2
Solution 3	Michael Møldrup
Solution 4	Drew
Solution 5	Code Maverick
Solution 6	MapLion
Solution 7	Renan
Solution 8	cramopy
Solution 9	wrightstork
Solution 10	double-beep
Solution 11
Solution 12	Code Maverick
Solution 13	sridesmet
Solution 14	Vinit Bhardwaj
Solution 15	pierreluigi88
Solution 16	M_ Fa
Solution 17	Kavu
Solution 18	Abu Assar
Solution 19	YHTAN
Solution 20	Piotr
Solution 21	Ekrem Önsoy
Solution 22	Marko Boroš
Solution 23

'The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Linked server creation

Providing access to the linked server under specific SQL Server logins

Providing access to the linked server via ad hoc queries

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]

Solution 17:[17]

Solution 18:[18]

Solution 19:[19]

Solution 20:[20]

Solution 21:[21]

Solution 22:[22]

Solution 23:[23]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]

Solution 4:^[4]

Solution 5:^[5]

Solution 6:^[6]

Solution 7:^[7]

Solution 8:^[8]

Solution 9:^[9]

Solution 10:^[10]

Solution 11:^[11]

Solution 12:^[12]

Solution 13:^[13]

Solution 14:^[14]

Solution 15:^[15]

Solution 16:^[16]

Solution 17:^[17]

Solution 18:^[18]

Solution 19:^[19]

Solution 20:^[20]

Solution 21:^[21]

Solution 22:^[22]

Solution 23:^[23]