An error occurred while sorting out the SQL Server OpenDataSource OpenRowSet connection to excel.

One TB-SQL

Select * From OpenDataSource
('Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/test2.xls"; user id = admin; Password =; extended properties = Excel 8.0 ')... [ta $]

 

Select * From OpenRowSet
('Microsoft. Jet. oledb.4.0 ', 'excel 8.0; database = C:/test2.xls', [ta $])

1. When Excel has been opened:
-- 2005
Server: Message 7399, level 16, status 1, Row 1
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.
Server: Message 7303, level 16, status 1, Row 1
You Cannot initialize the data source object of the ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null.
-- 2000
Server: Message 7399, level 16, status 1, Row 1
The ole db Provider 'Microsoft. Jet. oledb.4.0 'reports an error. The provider does not provide any information about the error.
Ole db Error Tracking [OLE/DB Provider 'Microsoft. Jet. oledb.4.0 'idbinitialize: Initialize returned 0x80004005: The provider does not provide any information about the error.]

 

2. There is no TA tag in execl:
-- 2005
Server: Message 7314, level 16, status 1, Row 3
The ole db access interface of the linked server "(null)" "Microsoft. Jet. oledb.4.0" does not contain the table "ta $ ". The table does not exist, or the current user does not have the permission to access the table.
-- 2000
Server: Message 7314, level 16, status 1, Row 1
The ole db Provider 'Microsoft. Jet. oledb.4.0 'does not contain the table 'ta $ '. The table may not exist, or the current user does not have the permission to use the table.
Ole db Error Tracking [non-interface error: ole db provider does not contain the table: providername = 'Microsoft. Jet. oledb.4.0 ', tablename = 'ta $'].

3. the Excel file does not exist:
-- 2005
Server: Message 7399, level 16, status 1, Row 1
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.
Server: Message 7303, level 16, status 1, Row 1
You Cannot initialize the data source object of the ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null.
-- 2000
Server: Message 7314, level 16, status 1, Row 1
The ole db Provider 'Microsoft. Jet. oledb.4.0 'does not contain the table 'ta $ '. The table may not exist, or the current user does not have the permission to use the table.
Ole db Error Tracking [non-interface error: ole db provider does not contain the table: providername = 'Microsoft. Jet. oledb.4.0 ', tablename = 'ta $'].

 

 

Another 2005 required

-- Enable ad hoc distributed queries first:
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure

-- Disable ad hoc distributed queries after use:
Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure

 

 

NTFS directory and file permission settings can also cause problems