What to look for when you first view your local Excel file with SQL Server

Daily use of the case to read Excel files through SQL Server, recorded

File path: C:\Users\Administrator\Desktop\icd10.xls

1. Query statement:

SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' C:\Users\Administrator\Desktop\icd10.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... [sheet1$]

The following error is reported first

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.

Workaround:

exec sp_configure ' show advanced Options ', 1

Reconfigure

GO

exec sp_configure ' Ad Hoc distributed Queries ', 1

Reconfigure

GO

Can be set back after the query

exec sp_configure ' show advanced Options ', 0

Reconfigure

GO

exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
GO

Next will be an error:

Msg 7399, Level 16, State 1, line 2nd
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 2nd
Unable to initialize the data source object for the OLE DB provider "Microsoft.Jet.OLEDB.4.0" of the linked server "(null)".

Workaround:

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

Remove All √

Problem:

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

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 cannot initialize the data source object of 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 of the linked server "(null)" OLE DB provider "microsoft.ace.oledb.12.0" 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):

[SQL]View Plaincopyprint?

2. --
8. exec sp_configure ' show advanced Options ', 1
10. Reconfigure
12. GO
14. exec sp_configure ' Ad Hoc distributed Queries ', 1
16. Reconfigure
18. GO
24. SELECT * from opendatasource ( ' microsoft.jet.oledb.4.0 ',' Data source= ' D:\KK.xls "; User id=admin; password=; Extended properties=excel 5.0 ') ... [kk$]
26. SELECT * from opendatasource ( ' microsoft.ace.oledb.12.0 ',' Data source= ' D:\KK.xlsx "; User id=admin; password=; Extended properties=excel 12.0 ') ... [kk$]
30. SELECT * from OPENROWSET (' microsoft.jet.oledb.4.0 ', ' Excel 8.0;database=d:\kk.xls ',' SELECT * FROM [kk$ ] ')
32. SELECT * from OPENROWSET (' microsoft.ace.oledb.12.0 ',' Excel 12.0;database=d:\kk.xlsx; Hdr=yes ',' SELECT * from [kk$] ')
38. exec sp_configure ' Ad Hoc distributed Queries ', 0
40. Reconfigure
42. GO
44. exec sp_configure ' show advanced Options ', 0
46. Reconfigure
48. GO
54. --

--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 cannot create an instance of the linked server "(null)" OLE DB provider "MSDASC".

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 cannot create an instance of the linked server "(null)" OLE DB provider "microsoft.ace.oledb.12.0".