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

Source: Internet
Author: User
Tags ole microsoft access database

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.