sql2008 reading Excel

Environment: Win7 (64-bit) +sql2008

SQL statements:

   --Enable ad Hoc distributed Queries:execsp_configure'Show advanced Options',1Reconfigureexecsp_configure'Ad Hoc Distributed Queries',1Reconfigure    --when you are finished using, close ad Hoc distributed Queries:execsp_configure'Ad Hoc Distributed Queries',0Reconfigureexecsp_configure'Show advanced Options',0Reconfigure --allow ace.oledb.12 to be used in the processEXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'allowinprocess',1--Allow dynamic ParametersEXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'dynamicparameters',1     --read Excel mode 1Select *  from OpenRowSet         ('microsoft.ace.oledb.12.0',          'Excel 8.0; Hdr=yes;imex=1;database=c:\data.xls',           [sheet1$]          )--read Excel mode 2 SELECT *  from OpenDataSource('microsoft.ace.oledb.12.0','Data Source=c:/data.xls; Extended Properties=excel 8.0')... sheet1$

View Code

Tips:

The OLE DB provider ' Microsoft.Jet.OLEDB.4.0 ' is configured to run in single-threaded apartment mode, so the provider cannot be used for distributed queries for error reasons: No jet.oledb.4.0 drivers are available in 64SQL engine

Workaround: Download a ace.oledb.12.0 for X64 bit driver and change the connection string microsoft.jet.oledb.4.0 to microsoft.ace.oledb.12.0

From the Microsoft homepage, you will find two versions, one for the 32-bit system and the other for the 64-bit system. Because we are a Win7 64-bit system, and the DLL used for the project is 64-bit, you install Microsoft Access engine-x64, but the installation will detect that the 32-bit Office installed on your machine requires you to upgrade office to 64-bit, Installing it directly requires prompt removal,

1. Remove 32-bit Microsoft Access Engine.exe (Control Panel, add Remove Programs)
2, use "/passive" command to install, for example "C:\directory Path\accessdatabaseengine_x64.exe"/passive
3, after the installation is complete, review the registry hkey_local_machine\software\microsoft\office\14.0\common\filespaths, delete the Mso.dll

sql2008 reading Excel