SQL SERVER – Error 7308: MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

FIX suggestion (which works for me), by Mitch Stokely:

1. On 64-bit servers and boxes, you need to first UNINSTALL all 32-bit Microsoft Office applications and instances (Access 2007 install, Office 10 32-bit, etc.). If you dont, you cannot install the new 64-bit Microsoft Access Database Engine 2010 Redistributable components. Yes, its a headache but the only way I found to install the new replacements for the JET engine components that need to run on 64-bit machines.

2. Download and install the new component from Microsoft:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
* This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.

3. Open up SQL Server and run the following:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
GO

* This sets the parameters needed to access and run queries related to the components.

4. Now, if you are running OPENROWSET calls you need to abandon calls made using the old JET parameters and use the new calls as follows:

(*Example, importing an EXCEL file directly into SQL):

DONT DO THIS….

SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;HDR=YES;Database=c:\PATH_TO_YOUR_EXEXCEL_FILE.xls’,’select * from [sheet1$]‘)

USE THIS INSTEAD…

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′, ‘Excel 12.0;Database=c:\PATH_TO_YOUR_EXEXCEL_FILE.xls’,’select * from [sheet1$]‘)

*At this point resolved two SQL issues and ran perfectly

5. Now for the fun part…..find all your Office Disks and reinstall Office and/or applications needed back onto the machine. You can install the 64- bit version of Office 10 by going onto the disk and going into the 64-bit folder and running it but beware as in some cases some third party apps dont interface yet with that version of Office.

Hope that help!

Mitch Stokely – Texas
Chief Internet Architect

See original post on Pinal Dave’s blog here >>

Advertisements

One thought on “SQL SERVER – Error 7308: MS Jet OLEDB 4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

  1. Hi there, I found your web site via Google at the same
    time as looking for a related topic, your website came up,
    it appears great. I’ve bookmarked it in my google bookmarks.

    Hello there, simply turned into aware of your blog through
    Google, and located that it is really informative. I’m going to be careful for brussels.
    I’ll be grateful in case you proceed this in future.
    A lot of other people shall be benefited from your writing.
    Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s