Querying data in an Excel table in SQL Server for various problems encountered

Original: Querying data in an Excel table in SQL Server for various problems encountered

SELECT * from OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' D:\KK.xls "; User id=admin; password=; Extended properties=excel 5.0 ') ... [sheet1$]  

Questions:
msg 15281, Level 16, State 1, line 1th
SQL Server blocked access to the STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component was shut down as part of this server's security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "surface area Configurator" in SQL Server Books Online.

Solve:

exec sp_configure ' show advanced options ', 1 reconfiguregoexec sp_configure ' Ad Hoc distributed Queries ', 1reconfigureGO SE Lect * from OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' D:\KK.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... [sheet1$]  --exec sp_configure ' Ad Hoc distributed Queries ', 0--reconfigure--go--exec sp_configure ' show advanced options ', 0-- Reconfigure--go

Problem:

Msg 7399, Level 16, State 1, line 1th
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" error for the linked server "(null)". The provider did not give any information about the error.
Msg 7303, Level 16, State 1, line 1th
Unable to initialize the data source object for the OLE DB provider "Microsoft.Jet.OLEDB.4.0" of the linked server "(null)".

Solve:

Object Explorer--server object--connect to server--access interface-->microsoft.jet.oledb.4.0--> (right-click Property, interface option is not enabled at all)

Problem:

Msg 7302, Level 16, State 1, line 1th
Unable to create an instance of the OLE DB provider "MSDASC" for the linked server "(null)".

Solve:

Change the logon status of SQL Server (MSSQLSERVER) and SQL Full-filter Deamon Launcher (MSSQLSERVER) Two services to LocalSystem

Problem:

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" of the linked server "(NULL)" returned the message "The external table is not in the expected format." "。
Msg 7303, Level 16, State 1, line 1th
Unable to initialize the data source object for the OLE DB provider "Microsoft.Jet.OLEDB.4.0" of the linked server "(null)".

Solve:

Microsoft.Jet.OLEDB.4.0 is the Microsoft Jet engine, which applies to version 2003. In 2007, Microsoft modified its main file format for access and Excel, renamed. ACCDB (Access 2007 database files) and. xlsx (Excel 2007 files), so it is not supported by the Microsoft Jet engine and is not Microsoft Office Drivers:data Connectivity components to support the current solution is to change the data provider in the connection string to microsoft.ace.oledb.12.0.

Download installation: Microsoft Office-Desktop drivers:data Connectivity Components Http://www.microsoft.com/zh-cn/download/detai ls.aspx?id=23734

SELECT * from OpenDataSource ('microsoft.ace.oledb.12.0 ', 'Data source= ' D:\KK. Kk.xlsx "; User id=admin; password=; Extended properties=excel 5.0 ')... [sheet1$]

Problem:

Msg 7314, Level 16, State 1, line 1th
The OLE DB provider "microsoft.ace.oledb.12.0" of the linked server "(NULL)" does not contain the table "sheet1$". The table does not exist, or the current user does not have permission to access the table.

Final Solution:

Search on the Internet, finally find out why!

Originally my Excel list name is not Sheet1, but KK! So all the above sheet1$ are changed to kk$ !

The complete code is as follows (two):

--exec sp_configure ' show advanced options ', 1 reconfiguregoexec sp_configure ' Ad Hoc distributed Queries ', 1reconfigureGO SELECT * from OpenDataSource (' microsoft.jet.oledb.4.0 ', ' Data source= ' D:\KK.xls "; User id=admin; password=; Extended properties=excel 5.0 ') ... [kk$]  SELECT * from OpenDataSource (' microsoft.ace.oledb.12.0 ', ' Data source= ' D:\KK.xlsx "; User id=admin; password=; Extended properties=excel 12.0 ') ... [kk$]  SELECT * from OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 8.0;database=d:\kk.xls ', ' select * from [kk$] ') SELECT * from OP Enrowset (' microsoft.ace.oledb.12.0 ', ' Excel 12.0;database=d:\kk.xlsx; Hdr=yes ', ' SELECT * from [kk$] ')  exec sp_configure ' Ad Hoc distributed Queries ', 0reconfigureGOexec sp_configure ' show Advanced options ', 0reconfigure go--

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------

After success, try to return to the original operation:

1. This has no effect on microsoft.jet.oledb.4.0 and has an impact on microsoft.ace.oledb.12.0 (interface options are not enabled at all):

Object Explorer--server object--connect to server--access interface-->microsoft.jet.oledb.4.0--> (right-click Property, interface option is not enabled at all)

2. The following two service identities have been changed back from LocalSystem to netword service and have no effect on microsoft.jet.oledb.4.0 , microsoft.ace.oledb.12.0 has an impact

Change the logon status of SQL Server (MSSQLSERVER) and SQL Full-filter Deamon Launcher (MSSQLSERVER) Two services to LocalSystem

SELECT * from OpenDataSource ( 'microsoft.ace.oledb.12.0 ', ' Data source= ' D:\KK.xlsx "; User id=admin; password=; Extended properties=excel 12.0 ') ... [kk$]

Error:

Msg 7302, Level 16, State 1, line 1th
Unable to create an instance of the OLE DB provider "MSDASC" for the linked server "(null)".

SELECT * from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' Excel 12.0;database=d:\kk.xlsx; Hdr=yes ', ' SELECT * from [kk$] ')

Error:

Msg 7302, Level 16, State 1, line 1th
Unable to create an instance of the OLE DB provider "microsoft.ace.oledb.12.0" for the linked server "(null)".

Querying data in an Excel table in SQL Server for various problems encountered