SQL Server reads data from Excel

The following is the execution of the SQL code, only take a simple read data examples, other detailed, please see for yourself

execsp_configure'Show advanced Options',1Reconfigureexecsp_configure'Ad Hoc Distributed Queries',1RECONFIGUREGOEXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'allowinprocess',1EXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'dynamicparameters',1GOSELECT * from OPENROWSET('microsoft.ace.oledb.12.0','Excel 12.0;database=c:\users\beige\desktop\sqltoexcel\data.xlsx;hdr=yes;imex=1', icd$)GoSELECT * from OPENROWSET('microsoft.ace.oledb.12.0','Excel 12.0;database=c:\users\beige\desktop\sqltoexcel\data.xlsx;hdr=yes;imex=1', cms$)GOexecsp_configure'Ad Hoc Distributed Queries',0Reconfigureexecsp_configure'Show advanced Options',0Reconfigure 

The OLE DB provider "microsoft.ace.oledb.12.0" was not registered when an error was performed.

This error requires installing the Accessdatabaseengine_x64.exe driver, select the appropriate version to download

: https://www.microsoft.com/zh-cn/download/details.aspx?id=13255

Also, it is important to note that the database executing the above SQL statement must be a local database,

Otherwise the error is

 Linked Server "(null )" OLE DB provider "Microsoft.ace.oledb.12.0  " error. The provider did not give any information about the error. Message  7303 , Level 16 , State 1 , section 1   row failed to initialize the linked server "(  null ) "OLE DB provider" Microsoft.ace.oledb.12.0 ". 

Here's how to summarize the solution:
1. Enable the OpenRowSet and OPENDATASOURCE functions in the SQL Server perimeter application Configurator
2, the database executing the above SQL statement must be a local database, if the remote database will report the above error
3. Link string Extended the contents of the Properties property are separated by semicolons and enclosed in double quotation marks, sheet1$ outside the parentheses.
4. Note that version 4.0 of office is office2003,12.0 is the office2007 version to see if the driver is installed.
5, the most critical is to see the SQL Server version number, is 32-bit or 64-bit. x64-bit SQL Server is not supported for many of the drivers of office.

SQL Server reads data from Excel