SQL Server 2014中用EXECUTE自动执行OPENQUERY查询Oracle链接服务器用户状态

你可以通过「临时表存储汇总结果+游标遍历动态执行SQL」的方式完成需求，完整实现代码如下：

步骤1：创建汇总结果临时表 #

首先创建临时表，结构和Oracle的USER_USERS视图一致，额外增加linked_server_name字段区分数据来源：

CREATE TABLE #OracleUserStatus (
    linked_server_name NVARCHAR(128), -- 存储链接服务器名称
    USERNAME NVARCHAR(128),
    USER_ID INT,
    ACCOUNT_STATUS NVARCHAR(32),
    LOCK_DATE DATETIME,
    EXPIRY_DATE DATETIME,
    DEFAULT_TABLESPACE NVARCHAR(30),
    TEMPORARY_TABLESPACE NVARCHAR(30),
    CREATED DATETIME,
    PROFILE NVARCHAR(30)
)

可根据你实际需要查询的字段调整临时表结构，不用完全照搬所有字段

步骤2：游标遍历执行动态查询并汇总结果 #

通过游标遍历所有Oracle类型的链接服务器，动态拼接OPENQUERY语句执行，将结果插入临时表：

DECLARE @linked_server NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)

-- 定义游标，获取所有Oracle链接服务器
DECLARE oracle_server_cursor CURSOR FOR
SELECT name FROM sys.servers WHERE provider = 'OraOLEDB.Oracle'

OPEN oracle_server_cursor
FETCH NEXT FROM oracle_server_cursor INTO @linked_server

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 拼接动态SQL，注意单引号转义，同时把链接服务器名称作为固定值插入结果
    SET @sql = N'
    INSERT INTO #OracleUserStatus(linked_server_name, USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, CREATED)
    SELECT ''' + @linked_server + N''', USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, CREATED
    FROM OPENQUERY(' + QUOTENAME(@linked_server) + N', ''SELECT USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, CREATED FROM SYS.USER_USERS'')
    '
    -- 执行动态SQL，加异常处理避免单个链接服务器故障导致整体中断
    BEGIN TRY
        EXEC sp_executesql @sql
    END TRY
    BEGIN CATCH
        PRINT '链接服务器【' + @linked_server + '】查询失败，错误信息：' + ERROR_MESSAGE()
    END CATCH

    FETCH NEXT FROM oracle_server_cursor INTO @linked_server
END

-- 关闭释放游标
CLOSE oracle_server_cursor
DEALLOCATE oracle_server_cursor

步骤3：查询汇总结果 #

执行完上述逻辑后，直接查询临时表即可得到所有链接服务器的用户状态汇总：

SELECT * FROM #OracleUserStatus
-- 用完记得删除临时表
-- DROP TABLE #OracleUserStatus

额外说明 #

• 如果需要定期执行，可以把上述逻辑封装成存储过程，配合SQL Server代理作业定时调度即可实现自动核查。
• 代码中加入了异常捕获逻辑，单个链接服务器连接失败只会打印错误信息，不会影响其他服务器的查询结果。

内容的提问来源于stack exchange，提问作者Depth of Field