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

Answer 1

Many things to Try:

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

5. 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)