SQL Server 2008 R2 Express 64bit Linked Server

Question

Windows Server 2008 R2 Standard 64bit.
Sql Server 2008 R2 Express with Advanced Services 64bit.

I am converting an Access 2000 database to SQL Server. I developed a script that creates a linked server to import the data into SQL Server and manipulate it into the new structure. All works well on my 32 bit developement machine.

We just had the above new server built. The linked server is created without error, but trying to access the data in the Access database results in the error

'OLE DB provider Microsoft.Jet.OLEDB.4.0 cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode."

 In searching, it seems that there is no 64 bit provider for jet.

Once the new system is up and running, the application also requires some regular imports of Access and Excel files, so this is not a one-time problem.

I can convert all Access and Excel files to 2007 version (ACE) if needed. However, I did try adding a linked server using the ACE provider and got the following error when trying to access the linked server

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered. (Microsoft SQL Server, Error: 7403)

What are my options?

Do I need to uninstall 64bit SQL and install 32bit?

 

Answer 1

Alphonse,

Since your SQL Server Express is 64-bit, please install the 64-bit ACE driver (AccessDatabaseEngine_X64.exe) first, http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

Then you can create a linked server with the 64-bit provider "Microsoft.ACE.OLEDB.12.0" to access your Access file.

As far as I know, you cannot install both 32-bit and 64-bit ACE driver on the same computer. If previously you installed a 32-bit one, please first remove it from Program Features in Control Panel.

The other workaround is that you install a new 32-bit SQL Server Express instance on which you can use 32-bit JET provider to create a linked server to your Access database and then in your 64-bit SQL Server, create a linked server to the 32-bit SQL Server Express linked server. More information can be found here, http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/411e1b71-dab9-46b5-8717-bd90c507b41f

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 2

Thanks Charles.

That raises a couple more questions.

Office 2007 (including Access) will be installed on the same machine under Terminal Server. I believe that is only available in 32 bit. Correct?

Will that create a conflict?

Are there any advantages to using SQL Server Express 64-bit over 32-bit, since Express is limited to 1 cpu and 1GB of RAM?

Answer 3

Check 

Dealing with Microsoft.Ace and OPENROWSET Errors as it seems to be dealing with the similar problem. 

---

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

Answer 4

Thanks Charles.

That raises a couple more questions.

Office 2007 (including Access) will be installed on the same machine under Terminal Server. I believe that is only available in 32 bit. Correct?

Will that create a conflict?

Are there any advantages to using SQL Server Express 64-bit over 32-bit, since Express is limited to 1 cpu and 1GB of RAM?

1. Yes, Office 2007 is only available in 32-bit.

2. It will not create a conflict with existing 2007 component.

3. I think that the advantages are not much obvious for 64-bit SQL Server Express. But on a 64-bit server, 64-bit SQL Server has better performance than a 32-bit one on parralism processing.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help