Openrowset command does not work with 64 bit SQL Server
Hi, When we try to use OpenRowset command to upload .xls file we get the following error: Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. ---Command being executed Select * From OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=C:\\test.xls;HDR=No;IMEX=1','Select * From [Sheet1$]') -----Version of SQL Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) Microsoft Office 2007 standard is also installed on the server On surfing the internet, we found that there is a exe called AccessDatabaseEngine.exe which should resolve the issue. However, installing this is not helping. Please look into the following article:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d However, when we tried running the same query on another server, it worked. Following is the server version Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: ) ----- Works
What version of Microsoft Office 2007 you have installed? If you have a 32 bit version, then you have also 32 bit ACE and you cannot use 32bit drivers on x64 machine. Also you cannot have 32bit office and 64bit ACE drivers on the same machine.