SQL Server 2019 通过存储过程调用web http服务

SQLServer 通过触发器调用存储过程，同时在存储过程中调用web http服务。

1、sqlserver数据库开启相关服务

--sqlserver数据库开启相关服务
--查看 OLE Automation Procedures 的当前设置。0未启用。
exec sp_configure 'show advanced option', '1' --只有这个高级选项被打开的时候，才有权限修改其他配置。
go
reconfigure --运行reconfigure语句进行配置
go
exec sp_configure 'Ole Automation Procedures';
go

--启用Ole Automation Procedures
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'Ole Automation Procedures', 1;
go
reconfigure;
go

2、创建触发器

---通过sql server 调用应用程序

--触发器
create trigger notify_trigger on dbo.tab_bas_plc
   after update 
as   
begin  

    declare @taskno varchar(50)
    declare @planweight numeric(10, 2)
    declare @factweight numeric(10, 2)
    declare @taskstate int

    /*
        update触发器会在更新数据后，
        将更新前的数据保存在deleted表中，
        更新后的数据保存在inserted表中。
    */

    set @taskno=(select Deleted.taskno from Deleted)
    set @planweight=(select Deleted.planweight from Deleted)
    set @factweight=(select Inserted.factweight from Inserted)
    set @taskstate=(select Inserted.taskstate from Inserted)

    if @taskstate = '1' begin
        exec pro_NotifyApp @taskno, @planweight, @factweight
    end
end

3、创建存储过程

--存储过程
create procedure dbo.pro_NotifyApp(
    @taskno varchar(50),
    @planweight numeric(10, 2),
    @factweight numeric(10, 2)
)
as
begin
    declare @url as varchar(1000)
  
    --通过http协议调用的接口地址
    set @url = 'http://192.168.0.18:8080/esb/service?action=ws&from=db&to=all&taskno=' + @taskno + '&factweight=' + cast(@factweight as varchar(50))

    print @url
  
    declare @obj as int
    declare @res as varchar(4000)
  
    exec sp_OACreate 'MSXML2.XMLHTTP', @obj out;
    exec sp_OAMethod @obj, 'open', null, 'get', @url, 'false'
    exec sp_OAMethod @obj, 'send'
    exec sp_OAMethod @obj, 'responseText', @res output
    
    --过滤"
    set @res = replace(@res, '"', '')

    print @res

    --select @res

    insert into tab_bas_plc_log(taskno, planweight, factweight, webresult, logdate) values (@taskno, @planweight, @factweight, @res, getdate());

    exec sp_OADestroy @obj
end