"Microsoft.ACE.OLEDB.12.0" 64x Sql Server and 86x Office?

Question

Error:

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be used for distributed queries because the provider is configured to operate in single-threaded apartment mode.

And the answers that I see are the conflict between the 64-bit Sql server and the 32-bit Office. Is there a way to run openrowset in Excel on Sql Server?

insert into dbo.FiscalCalendar 
select * from 
openrowset('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\Users\uname\Desktop\fy11.xlsx;',
'Select * from [Sheet1]')

Answer 1

It looks like Microsoft hasn't come up with a solution yet. check contact form with Microsoft

They have workarounds like using SSIS

Answer 2

... 64- /passive:

c: > AccessDatabaseEngine_64/passive

Answer 3

64- Microsoft Access Database Engine 2010, .

, Excel: E:\Sample.xls Excel: Sheet1

Excel :

SELECT * FROM OPENDATASOURCE( 'Microsoft.ACE.OLEDB.12.0', 'Data Source="E:\Sample.xls";
Extended properties=Excel 8.0')...Sheet1$

64- Windows Server 2008 64- 64- SQL Server 2005 SQL Server 2008 R2.

64- "Microsoft.ACE.OLEDB.12.0" 32- Jet.

Answer 4

, , - , SSIS,

Answer 5

64- 64- Microsoft Access Database Engine 2010 . W7 64bit, Office 2010 SQL 2008 R2.

/ , , . MS VS 2008, . Access Database Engine 2010 . 2007 , .

.

Answer 6

: MS Office 2010 (x86/32bit) Windows 7 (x64/64bit) SQL Server 2014 (x64/64bit)

:

• SQL Server MS Office

• MS Access Database Engine 2010 x64/64- : https://www.microsoft.com/de-de/download/details.aspx?id=13255

• Windows (Start-Execute- "cmd" ) : AccessDatabaseEngine_X64.exe /passive

• regedit : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPath. mso.dll mso.dll.rename

• , XLSX, ( ).

• Windows. , MS SQL Server. " " ​​ " ". MS SQL Server.

• SQL Server Management Studio, MS SQL Server T-SQL:

  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
  

• Refer to the following T-SQL statement:

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