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

December 20, 2018

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

I was trying to retrieve data from excel in T-SQL using the following statement:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\MyPath\MyFile.xlsx;;HDR=YES;IMEX=1', [Sheet1$])

But was receiving the error message.

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

While searching solution for this error, I have found multiple reasons that leads to differents solutions to fix this issue. Here are the options I have found during my search.

Solution 1:
- Check if the file name is correct/exists?
- Check if the path/directory name is correct/exists?
Solution 2:

Check if the target file is open, then close it and run your query again.
Solution 3:

Check if you have Microsoft Access Database Engine 2010 installed? If not, you can download it from Microsoft . A system restart may also be helpful.
Solution 4:

Run SSMS as admin.
Solution 5:

Check if the user has rights on SQL Server Service. If not you have to set the proper account/user for this service. Follow these steps:
- Open the services console by typing Services.msc in the run command.
- Find the service with name SQL Server(MyInstanceName) (MyInstanceName is the name of your instance), right click on it and click Properties.
- In the Properties dialog box, move to Log On tab, against This account: radio option, click the Browse button, a Select User dialog box will appear.
- In the Select User dialog box, enter the proper user name or you can find from the list by click on Advance button and search for the desired user. Once user is selected, click OK.
- Then back on the Log On tab, enter the passwords, press Apply, then press OK.
- Restart the service to make the changes take effect.

Solution 6:

You have to enable Ad hoc distributed queries:

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

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

Solution 7:

Check the In Process and Dynamic Parameters options for the ACE provider, use following T-SQL command.

    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 8:
- If you are using select statement with specific columns, make sure the column names are correct. For example in this query:
```
    SELECT [Col A], [Col B] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
        'Excel 12.0;Database=C:\MyPath\MyFile.xlsx;;HDR=YES;IMEX=1', [Sheet1$])
  
```
  Check if [Col A] and [Col B] actually exist in your spreadsheet?
- Sheet name might be wrong. Do Sheet1(in excel) actually exist in your spreadsheet? Have you appended $ sign with sheet name in your query like [Sheet1$].
Solution 9:
Check if your user have the write access on the Temp folder, e.g.
```
   C:\Users\MyUser\AppData\Local\Temp (MyUser is your user name)
  
```
Solution 10:

Check the permissions on the Temp folder located in ServiceProfiles. You have to pick the one based on whether you use a local system account or network domain account.

For local system account, you have to select:
```
    C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
   
```
For network domain accounts, folder is:
```
    C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
   
```
Right click on this folder and give it read write access to the account executing the code.

I tried some of the above solutions to get rid of this error.

If any one has some other solution that helped him solve the issue please share in the comments section below.

Idrees' Blog

Pages

Search This Blog

December 20, 2018