I found unswer)
After many struggles with this issue, I found the following solution:
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.
Download and install the new component from Microsoft:
- This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.
- Open up SQL Server and run the following:
sp_configure ‘show advanced options’, 1;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
- This sets the parameters needed to access and run queries related to the components. Address ‘null
- 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
- 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