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

Question

DECLARE @PATH NVARCHAR(1000) = N'\\MY-SERVER\C$\Folder\\'
DECLARE @TABLE NVARCHAR(50) = SUBSTRING(@FILENAME,0,CHARINDEX('.',@FILENAME))
DECLARE @SQL NVARCHAR(4000) = 
    N'IF OBJECT_ID(''dbo.' + @TABLE + ''' , ''U'') IS NOT NULL 
    DROP TABLE dbo.[' + @TABLE + ']
    SELECT * INTO [' + @TABLE + ']
    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''
                    ,''Text; Database='+@PATH+';''
                    ,''SELECT * FROM [' + @FILENAME + ']'')'

EXEC(@SQL)

Today I have come across an issue with Microsoft.ACE.OLEDB.12.0 driver in SSIS 2012. The script above sits in a stored procedure which dynamically loads the .csv's into the database based on the current file which is supplied by the SSIS loop in which stored procedure sits. There are files in the directory.

The stored procedure runs correctly when run directly in SQL Server Management Studio.

This has been working fine up until today. Today I am getting the following error:

Executing the query "EXEC [dbo].[CreateAndImportCSVs] ?" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any help on this issue would be great!

Edit

So looking into what's changed I hear alarm bells when I look at the windows updates installed on the server yesterday! The following two were installed:

Microsoft Office Access Runtime and Data Connectivity 2007 (SP3) http://support.microsoft.com/kb/2526310

Update for the 2007 Microsoft Office System (KB967642) http://www.microsoft.com/downloads/details.aspx?FamilyId=E93AB1BE-ADE6-4FF8-8637-DBD3EBE3C5C5&displaylang=en

So what changed today? Server fail over/move? Password change? Accounts change? Something changed, let's identify what and then we can work towards a resolution — billinkc
– billinkc, Commented Nov 30, 2017 at 16:56

Hadi · Accepted Answer · 2017-11-30 21:00:05Z

2

Many things to Try:

Check the In Process and Dynamic Provider options for the ACE provider
Check the permissions on the Temp folder
Check the MemToLeave memory area allocated
Make sure the EXCEL process is not running in background

Made sure 'ad hoc distributed queries' was enabled (1)

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

Read more at How to solve Microsoft.ACE.OLEDB.12.0 error "Unspecified error"

Also i found an interesting suggested solution in the following link, take a look:

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

edited Nov 30, 2017 at 21:00

answered Nov 30, 2017 at 20:45

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AMouat Over a year ago

Thanks, 1 and 2 already done, and 3 not an issue as it runs successfully when run in SSMS.

Hadi Over a year ago

CHeck the the EXCEL process is not running, even if the excel app is not opened

Hadi Over a year ago

Read the last link

AMouat Over a year ago

None of these solve the problem unfortunately, but like I said in the post when I run this directly from SSMS it runs correctly, the issue appears to be in SSIS.

AMouat · Accepted Answer · 2017-12-06 09:06:48Z

2

This problem resolved itself. There were further updates installed, one of these must have fixed the issue introduced by the previous updates:

Update for Microsoft Office 2010 (KB4011188) 64-Bit Edition https://support.microsoft.com/kb/4011188

Update for Microsoft Office 2010 (KB2553347) 64-Bit Edition http://support.microsoft.com/kb/2553347

Security Update for Microsoft Office 2010 (KB2553338) 64-Bit Edition https://support.microsoft.com/kb/2553338

Thanks for all the help with this!

answered Dec 6, 2017 at 9:06

AMouat

7752 gold badges16 silver badges29 bronze badges

Comments

John Livengood · Accepted Answer · 2021-04-15 15:36:20Z

1

The final piece that solved this for me was moving the spreadsheet to a windows directory that SQL Server was able to access. I moved my spreadsheet to a new subdirectory, within the directly where my sql database was located, and the problem was resolved.

answered Apr 15, 2021 at 15:36

John Livengood

111 bronze badge

Comments

Venkataraman R · Accepted Answer · 2022-04-11 07:26:43Z

1

SQL Server is not able to access the Access provider, if the installation of office was Click to Run installation.

The issue can be resolved by any of the following approaches.

OLEDB driver issues resolution

Resolution

Beginning with Microsoft 365 Apps for Enterprise Version 2009, work has been completed to break ACE out of the C2R virtualization bubble so that applications outside of Office are able to locate the ODBC, OLEDB and DAO interfaces provided by the Access Database Engine within the C2R installation.

Use the following table to understand if additional components are necessary to access these intefaces within your environment:

answered Apr 11, 2022 at 7:26

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Comments

Tomek · Accepted Answer · 2023-12-10 23:30:19Z

1

Currently we installed 16.0. On one server both options N'AllowInProcess' and N'DynamicParameters' are set to 1 for the old 12.0 and the new 16.0 driver.

On the other server 12.0 worked well with both options set to 1. BUT 16.0 works only when they are both set to ZERO.

The IT swear both systems are the same.......

answered Dec 10, 2023 at 23:30

Tomek

693 bronze badges

1 Comment

Adel Mourad Over a year ago

He wanted to say set AllowInProcess to 0 and set DynamicParameters to 1 to get it work.

cjjeeper · Accepted Answer · 2025-01-26 23:01:11Z

Update that may be helpful I am using SQLExpress v 15 and Management Studio 19. I installed accessdatabaseengine_X64 and ran

`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.16.0', N'AllowInProcess', 1 GO

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

Then I ran the openrowset query....

--build new table SELECT * into MyTable from OPENROWSET('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=C:\ExcelFiles\Book2.xlsx;HDR=YES', 'select * from [Sheet1$]')

Everything was roses... Shut down the machine and started in back up the next day and ran .....

--build new table SELECT * into MyTable from OPENROWSET('Microsoft.ACE.OLEDB.16.0', 'Excel 12.0;Database=C:\ExcelFiles\Book2.xlsx;HDR=YES', 'select * from [Sheet1$]')

Got this error... "Cannot initialize the data source object of OLE DB provider ACE.OLEDB.16.0"

Spent the next X hours trying to figure out what happened. Logged out and logged in as 'sa' and it all worked....--> windows security issue then.

Rebooted the machine and got the error again...found this solution. --I will summarize here..-> my windows authenticated login for SQLExpress did not have permissions on the SQLExpress service --

Solution 2 Check whether the user login that you are accessing has rights on SQL Server Service or not. If not you will have to give your credentials so that the SQL Service runs using your login credentials.

Steps

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 This Account. Click on Browse and type your login name there and the click on Check Names
Once it finds your login name press OK.
Now type your login’s passwords in both the fields as shown below press apply and then ok
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

--I did the services restart and it worked and a reboot and it worked. here is the link -- it was for oledb.12 but it also works for oledb.16 --solution 2

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

n8. · Accepted Answer · 2017-11-30 20:56:03Z

0

Probably not a solution, but you should execute SQL with:

sp_executesql @SQL

It protects from injection and wotnot.

answered Nov 30, 2017 at 20:56

n8.

1,7423 gold badges16 silver badges42 bronze badges

2 Comments

Bacon Bits Over a year ago

sp_executesql only protects against injection when you use a parameterized query and supply parameters for it. Something like this:

EXECUTE sp_executesql N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee WHERE EmployeeKey = @level',  N'@level tinyint',  @level = 109;

There isn't any particular advantage here over EXEC since this dynamic SQL uses variables to represent names of objects, and you can't do that with a parameterized query.

n8. Over a year ago

Maybe you didn't see where @filename isn't declared. I took that to mean it's an input parameter

Collectives™ on Stack Overflow

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

7 Answers 7

4 Comments

Comments

Comments

Resolution

Comments

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

Comments

Comments

Resolution

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related