如何在SQL Server 2014中编写内置轮询服务调用Web服务？

嘿，这个需求完全可以在SQL Server端搞定，而且SQL Server 2014是完全支持的！下面我给你一步步拆解具体实现步骤：

1. 启用OLE自动化存储过程 #

因为要调用外部Web服务，我们需要用到SQL Server的OLE自动化功能（默认是禁用的）。执行以下语句启用：

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
GO

注意：执行这个需要sysadmin权限，启用后要确保安全，避免滥用。

2. 创建处理逻辑的存储过程 #

我们需要写一个存储过程，用来执行查询、处理员工记录并调用Web服务。这里假设Web服务接受JSON格式的请求体（SQL Server 2014没有原生JSON支持，我们可以手动拼接或者用XML转换）：

CREATE PROCEDURE dbo.ProcessSpecialDepartmentEmployees
AS
BEGIN
    SET NOCOUNT ON;

    -- 声明变量
    DECLARE @EmployeeId INT, @EmployeeName NVARCHAR(100), @JoiningDate DATETIME, @DepartmentId INT;
    DECLARE @RequestBody NVARCHAR(MAX), @ResponseText NVARCHAR(MAX);
    DECLARE @Obj INT, @Result INT;

    -- 游标遍历符合条件的员工（也可以用WHILE循环，游标更直观）
    DECLARE EmployeeCursor CURSOR FOR
        SELECT e.Id, e.Name, e.JoiningDate, e.DepartmentId
        FROM Employee e 
        INNER JOIN Department d ON e.DepartmentId = d.DepartmentId 
        WHERE e.DepartmentId = 2 
          AND e.JoiningDate > CAST(GETDATE() AS DATE) 
          AND e.SpecialRegistrationDone = 0;

    OPEN EmployeeCursor;
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeId, @EmployeeName, @JoiningDate, @DepartmentId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 拼接请求体（这里模拟JSON格式，根据Web服务实际要求调整）
        SET @RequestBody = N'{
            "Id": ' + CAST(@EmployeeId AS NVARCHAR) + ',
            "Name": "' + REPLACE(@EmployeeName, '"', '\"') + '",
            "JoiningDate": "' + CONVERT(NVARCHAR, @JoiningDate, 126) + '",
            "DepartmentId": ' + CAST(@DepartmentId AS NVARCHAR) + '
        }';

        -- 调用Web服务
        BEGIN TRY
            -- 创建HTTP请求对象
            EXEC @Result = sp_OACreate 'MSXML2.XMLHTTP', @Obj OUT;
            IF @Result <> 0 GOTO Cleanup;

            -- 打开请求
            EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'POST', 
                'http://specialdepartmentregistration/register', 'false';
            IF @Result <> 0 GOTO Cleanup;

            -- 设置请求头
            EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 
                'Content-Type', 'application/json';
            IF @Result <> 0 GOTO Cleanup;

            -- 发送请求
            EXEC @Result = sp_OAMethod @Obj, 'send', NULL, @RequestBody;
            IF @Result <> 0 GOTO Cleanup;

            -- 获取响应（可选，根据需要处理）
            EXEC @Result = sp_OAGetProperty @Obj, 'responseText', @ResponseText OUT;

            -- 如果调用成功，更新员工的SpecialRegistrationDone为1
            UPDATE Employee 
            SET SpecialRegistrationDone = 1 
            WHERE Id = @EmployeeId;

        END TRY
        BEGIN CATCH
            -- 错误处理：可以记录日志到表，或者重试（这里简化处理）
            INSERT INTO dbo.ProcessingLog (EmployeeId, ErrorMessage, ProcessDate)
            VALUES (@EmployeeId, ERROR_MESSAGE(), GETDATE());
        END CATCH

Cleanup:
        -- 释放对象
        EXEC sp_OADestroy @Obj;

        FETCH NEXT FROM EmployeeCursor INTO @EmployeeId, @EmployeeName, @JoiningDate, @DepartmentId;
    END

    CLOSE EmployeeCursor;
    DEALLOCATE EmployeeCursor;
END
GO

提示：如果Web服务接受XML格式，你可以用FOR XML PATH来生成更规范的XML请求体，避免手动拼接字符串的风险。

3. 创建SQL Server代理作业实现15分钟轮询 #

SQL Server代理是用来执行定时任务的工具，2014完全支持：

1. 打开SQL Server Management Studio（SSMS），展开SQL Server代理 -> 作业，右键选择新建作业
2. 在常规选项卡，给作业起个名字（比如“SpecialDepartmentRegistrationJob”）
3. 切换到步骤选项卡，点击新建：
   • 步骤名称：比如“ExecuteProcessingSP”
   • 类型：Transact-SQL脚本(T-SQL)
   • 数据库：选择你的业务数据库
   • 命令：输入EXEC dbo.ProcessSpecialDepartmentEmployees;
4. 切换到计划选项卡，点击新建：
   • 计划名称：比如“15MinutePolling”
   • 频率：选择每天
   • 每天频率：选择执行频率为15分钟，持续时间设置为全天（比如00:00到23:59）
5. 保存作业即可。

关键注意事项 #

• 权限：SQL Server代理的作业执行账户需要有以下权限：
  • 执行存储过程的权限
  • 访问OLE自动化存储过程的权限
  • 访问外部Web服务的网络权限（如果Web服务不在本地）
• 幂等性：一定要更新SpecialRegistrationDone字段，避免同一条员工记录被重复处理
• 错误日志：建议创建一个日志表，记录处理失败的情况，方便排查问题
• 性能：如果员工记录很多，要注意游标或者循环的性能，也可以考虑批量处理

内容的提问来源于stack exchange，提问作者userx