在使用 SQL Server 的 OPENROWSET 函数时，可能会遇到各种报错，这些错误可能由多种原因引起，包括配置问题、权限问题、数据源问题等，本文将详细探讨这些问题并提供解决方案。
常见 OPENROWSET 报错类型及解决方法
1、数据库未启用 Ad Hoc Distributed Queries
错误信息：SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server。
解决方法：
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;2、OLE DB 提供程序错误
错误信息：The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" reported an error. Access denied。
解决方法：
确保在 SSMS 中为链接服务器勾选了“Allow inprocess”选项，并重启 SSMS。
3、TNS 配置文件问题
错误信息：ORA12154: TNS: 无法解析指定的连接标识符。
解决方法：
确保在 TNS 配置文件中正确配置了 ORACLE 实例信息。
SQL Server 是 64 位，确保配置了 64 位的 TNS。
4、普通账号访问被拒绝
错误信息：Ad hoc access to OLE DB provider 'OraOLEDB.Oracle' has been denied。
解决方法：
在注册表中设置 DisallowAdHocAccess 属性为 1：
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\Providers\OraOLEDB.Oracle\DisallowAdHocAccess5、登录超时或网络问题
错误信息：登录超时已过期，与 SQL Server 建立连接时发生了与网络相关的或特定于实例的错误。
解决方法：
确保实例名称正确，SQL Server 配置为允许远程连接。
检查网络连接是否正常。
6、工作组信息文件丢失或被独占打开
错误信息：无法启动应用程序，工作组信息文件丢失，或是已被其它用户以独占方式打开。
解决方法：
确保 Access 数据库文件未被其他用户独占打开，并且工作组信息文件存在。
7、触发器中使用 OPENROWSET 报错
错误信息：提供程序报告了意外的灾难性错误。
解决方法：
确保 FROM 子句是一个单一的简单表名。
使用 TRY...CATCH 结构捕获并处理错误。
示例代码
以下是一些常见的 OPENROWSET 使用示例和错误处理代码：
示例 1：从 Excel 文件中读取数据
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Finance\account.xls',
'SELECT * FROM [Sheet1$]')示例 2：从 Access 数据库中读取数据
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'D:\aa.mdb';'admin';'12345',
'SELECT * FROM TableName')示例 3：在触发器中使用 OPENROWSET
CREATE TRIGGER trg_DataInsert
ON YourTableName
AFTER INSERT
AS
BEGIN
BEGIN TRY
INSERT INTO TargetTable (Column1, Column2)
SELECT Column1, Column2
FROM OPENROWSET('SQLNCLI',
'Server=YourServer;Database=YourDatabase;Trusted_Connection=yes;',
'SELECT Column1, Column2 FROM SourceTable')
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END;FAQs
Q1: 如何在 SQL Server 中启用 Ad Hoc Distributed Queries？
A1: 可以通过以下命令启用 Ad Hoc Distributed Queries：
EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
Q2: 如何解决 OPENROWSET 访问 ORACLE 数据库时遇到的 ORA12154 错误？
A2: 确保在 TNS 配置文件中正确配置了 ORACLE 实例信息，并且如果 SQL Server 是 64 位，确保配置了 64 位的 TNS，确保在 SSMS 中为链接服务器勾选了“Allow inprocess”选项，并重启 SSMS。
