Solve some problems that occur when reading an Excel table in the SQL Server Query analyzer.

Several problems encountered when reading Excel in the sql2005 query analyzer, collected all the resources on the Internet, and finally solved the problem. Many answers on the Internet are pasted and copied, therefore, it cannot be used in the actual environment. I believe many of my friends have encountered the same mistakes. To avoid the same mistakes, I would like to share them with you:
Environment:
System: windows7,
Database: Sqlserver2005,
Office: office2003

SQLCommonly used Excel reading methods include:
A. Use the OpenRowSet and OpenDataSource Functions
B. query Excel by using the linked server

Select * From OpenRowSet
('Microsoft. Jet. oledb.4.0 ',
'Excel 8.0; HDR = yes; IMEX = 1; database = E: \ bb.xls ',
[Sheet1 $]
)
General error:
Message 7399, level 16, state 1, 1st rows
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" reports an error. The provider does not provide any information about the error.
Message 7303, level 16, state 1, 1st rows
You Cannot initialize the data source object of the ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null.

Select * From OpenDataSource
('Microsoft. Jet. oledb.4.0 ',
'Data source = E: \ bb.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 1 "'
)... [Sheet1 $]
General error:
Message 7399, level 16, state 1, 1st rows
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" reports an error. The provider does not provide any information about the error.
Message 7303, level 16, state 1, 1st rows
You Cannot initialize the data source object of the ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null.

Select * From OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = E: \ bb.xls; user id = admin; Password =; extended properties = Excel 5.0'
)... [Sheet1 $]
General error:
Message 7308, level 16, state 1, 1st rows
Because the ole db access interface 'Microsoft. Jet. oledb.4.0 'is configured to run in single-threaded unit mode, this access interface cannot be used for Distributed queries.

Declare @ RC int
Declare @ server nvarchar (128)
Declare @ srvproduct nvarchar (128)
Declare @ provider nvarchar (128)
Declare @ datasrc nvarchar (4000)
Declare @ location nvarchar (4000)
Declare @ provstr nvarchar (4000)
Declare @ catalog nvarchar (128)

Set @ Server = 'excel SQL'
Set @ srvproduct = 'excel'
Set @ provider = 'Microsoft. Jet. oledb.4.0'
Set @ datasrc = 'e: \ bb.xls'
Set @ provstr = 'excel 8.0'
Exec @ rc = [Master]. [DBO]. [sp_addmediaserver] @ server, @ srvproduct, @ provider,
@ Datasrc, @ location, @ provstr, @ catalog

Query:
Select * From exceltosql... sheet1 $

Error:
The ole db access interface "Microsoft. Jet. oledb.4.0" of "exceltosql" cannot start the application. The workgroup information file is lost or exclusive to other users. ".
Message 7399, level 16, state 1, 1st rows
An error is returned when the ole db access interface "Microsoft. Jet. oledb.4.0" of "exceltosql" is linked to the server. Authentication failed.
Message 7303, level 16, state 1, 1st rows
Unable to initialize the data source object of the ole db access interface "Microsoft. Jet. oledb.4.0" of the connection server "exceltosql.

The following is a summary of the solution:
1. In SQLServerEnable OpenRowSet and OpenDataSource functions in the peripheral application Configurator
2. The database that executes the preceding SQL statement must be a local database. If it is a remote database, the above error will be reported.
3. The content of the link string extended properties must be separated by semicolons and enclosed in double quotation marks. sheet1 $ is out of brackets.

4. Note that office version 4.0 is office2003 and office version 12.0 is office2007. Check whether the driver is installed.

5. The most important thing is to check whether the SQL Server version number is 32-bit or 64-bit. Many office drivers of x64bit SQL Server are not supported.

So if you can't do it, you may wish to put it in a 32-bit sqlserver, and there will be a lot of GAINS.

Original blog address: http://wep2008.blogbus.com/logs/35390970.html

64-bit OpenRowSet running error

In SSMs, run: Select mermerid, companyName
From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'D: \ nwind. mdb ';
'Admin'; '', MERS mers)

Message 7308, level 16, state 1, 1st rows
Because the ole db access interface 'Microsoft. Jet. oledb.4.0 'is configured to run in single-threaded unit mode, this access interface cannot be used for Distributed queries.
My environment SQL Server 2008 (64-bit) + windows2008r2 (64-bit)

Solution:

Http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/58c4c61e-fa86-4809-bf7d-21bacb055d3e/

Download the latest driver

The reason is: no driver for jet. oledb.4.0 is provided in 64 SQL engine.
Solution: download an ace. oledb.12.0 for x64 driver and change the connection string Microsoft. Jet. oledb.4.0 to Microsoft. Ace. oledb.12.0.