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

x6yk4ghg 于 2024-01-05 发布在其他

关注(0)|答案(6)|浏览(110)

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

sql-server

来源：https://stackoverflow.com/questions/47578546/cannot-initialize-the-data-source-object-of-ole-db-provider-microsoft-ace-oledb

6条答案

按热度按时间

gkn4icbw1#

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

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)

赞(0）回复(0）举报 2024-01-05

mu0hgdu02#

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 Editionhttps://support.microsoft.com/kb/4011188

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

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

Thanks for all the help with this!

赞(0）回复(0）举报 2024-01-05

bogh5gae3#

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.

赞(0）回复(0）举报 2024-01-05

watbbzwu4#

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:

赞(0）回复(0）举报 2024-01-05

dgenwo3n5#

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

sp_executesql @SQL

It protects from injection and wotnot.

赞(0）回复(0）举报 2024-01-05

laximzn56#

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

赞(0）回复(0）举报 2024-01-05

我来回答

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

6条答案

Resolution

相关问题

热门标签

最新问答