Resolves an issue in which SQL Server 2008 64-bit systems could not import Access/excel 2012/08/01

Operating system Windows Server X64, database SQL Server X64,office 2007 (as if there were only 32 bits), there was a problem when the stored procedure executed OpenDataSource importing Access data, OLE DB 4.0 is not supported, here are some of the error hints encountered:

Because OLE DB provider ' Microsoft.Jet.OLEDB.4.0 ' is configured to run in single-threaded apartment mode, this provider cannot be used for distributed queries. Unable to create an instance of the OLE DB provider "microsoft.ace.oledb.12.0" for the linked server "(null)". Because OLE DB provider ' microsoft.ace.oledb.12.0 ' is configured to run in single-threaded apartment mode, this provider cannot be used for distributed queries.

The solution is to install the Office 2010 64-Bit Edition, have a lot of downloads online, and then download the X64 version of Microsoft Access Database Engine 2010, which is also available for download on the Web.

Then change the OpenDataSource parameter, change microsoft.jet.oledb.4.0 to microsoft.ace.oledb.12.0, you are done.

Paste the Stored Procedure section code:

--Open Import function
exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
--Allow ace.oledb.12 to be used in the process
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 1
--Allow dynamic parameters
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 1

--Import temporary tables
EXEC (' INSERT into Jihua (id,[lot number],right (' ' + @filepath + ', charindex (' ' \ ', REVERSE (' + @filepath + ') ')-1), GETDATE ( ) from OpenDataSource (' microsoft.ace.oledb.12.0 ', ' Data source= ' [email protected]+ '; User id=admin; Password= ") ... Plan summary table ')

--note here to turn off the peripheral settings before turning off the advanced options
exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure
--option to turn off ace.oledb.12
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' allowinprocess ', 0
EXEC master.dbo.sp_MSset_oledb_prop n ' microsoft.ace.oledb.12.0 ', n ' dynamicparameters ', 0

Resolves an issue in which SQL Server 2008 64-bit systems could not import Access/excel 2012/08/01