链接服务器相关

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;  
GO  
RECONFIGURE;  

 

连接excel2007
select * from   
OpenDataSource('Microsoft.Ace.OLEDB.12.0','Data Source="e:/aa.xlsx";Extended properties="Excel 12.0;HDR=Yes"')...[Sheet1$]  
 

 

 97-2003版本

EXCEL

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=文件位置;Extended Properties=Excel 8.0;HDR=Yes

ACCESS

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=文件位置;Jet OLEDB:Database Password=密码;

2007版本

EXCEL

Provider=Microsoft.Ace.OleDb.12.0;Data Source=文件位置;Extended Properties=Excel 12.0;HDR=Yes

ACCESS

Provider=Microsoft.Ace.OleDb.12.0;Data Source=文件位置;Jet OLEDB:Database Password=密码;

【其他说明】

HDR=Yes/NO 表示是否将首行做标题。

不支持文件带密码的EXCEL文件，哪怕知道正确密码，除非自行先打开该文件

 

 

Msg 7399, Level 16, State 1, Line 2

The OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

 

Solution 1

First you need to check whether you have installed the 2007 Office System Driver: Data Connectivity Components which is necessary for Microsoft OLEDB ACE 12.0 driver to work. So if you have not done that download it by clicking the link below.

2007 Office System Driver: Data Connectivity Components

Once you downloaded and installed it on your system restart your machine to see if the issue is resolved for you or not.

 

Solution 2

Check whether the user login that you are accessing has rights on SQL Server Service or not. If not you will have to give your credentials so that the SQL Service runs using your login credentials.

Steps

1. Close SQL Server Management Studio. Type Services.msc in the run command to open the services window

2. Search for SQL Server Service and right click it and select properties as shown below

3. In the Log On Tab, select This Account. Click on Browse and type your login name there and the click on Check Names

4. Once it finds your login name press OK.

5. Now type your login’s passwords in both the fields as shown below press apply and then ok

 

 

6. Restart the services so that the new changes are applied as shown in figure below.

7. Now start SQL Server Management Studio and try to run the query if still not working try a system restart

 

 

 

Solution 3

Run the following Query in your SQL Server Management Studio close it and start again

USE [master] 

GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 

GO 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

GO