sqlserver 实现数据变动触发信息

ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create proc [dbo].[SP_SendMagToFile] ( @path varchar(100), @fileName varchar(100), @msg varchar(200) ) as begin DECLARE @object int DECLARE @hr int DECLARE @src varchar(255), @desc varchar(255) Declare @tmp int declare @strPath nvarchar(512) Set @strPath=@path+‘\‘+@fileName --組成文件名 EXEC @hr = sp_OACreate ‘Scripting.FileSystemObject‘, @object OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END --創建文件 EXEC @hr = sp_OAMethod @object, ‘CreateTextFile‘, @tmp OUTPUT , @strPath IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object RETURN END --把@msg寫到文件里面去 EXEC @hr = sp_OAMethod @tmp, ‘Write‘,NULL, @msg IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object RETURN END --關閉文件 EXEC @hr = sp_OAMethod @tmp, ‘Close‘,NULL IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object RETURN END end

2、服务端监听给文件夹是否被更新，如果更新就发送信息或执行程序等。

3、这样可以减少网络传输的压力。

4、也可以执行发送http，实现更新数据的功能

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

/*
参数说明?
@URL=http请求地址
@status=状态代?
@returnText=返回?
@object=对象令牌
@errSrc=错误源编?
*/
CREATE PROCEDURE P_GET_HttpRequestData(
    @URL varchar(500),
    @status int=0 OUT,
    @returnText varchar(2000)=‘‘ OUT
)
AS
BEGIN
    DECLARE @object int,
    @errSrc int
    /*初始化对*/
    EXEC @status = SP_OACreate ‘Msxml2.ServerXMLHTTP.3.0‘, @object OUT
    IF @status <> 0
    BEGIN
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
     RETURN
    END

    /*创建链接*/
    EXEC @status= SP_OAMethod @object,‘open‘,NULL,‘GET‘,@URL
    IF @status <> 0
    BEGIN
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
     RETURN
    END
    EXEC @status=SP_OAMethod @object,‘setRequestHeader‘,‘Content-Type‘,‘application/x-www-form-urlencoded‘
    /*发起请求*/
    EXEC @status= SP_OAMethod @object,‘send‘,NULL 
    IF @status <> 0 
    BEGIN 
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
     RETURN
    END
     
    /*获取返回*/
    EXEC @status= SP_OAGetProperty @object,‘responseText‘,@returnText OUT
    IF @status <> 0 
    BEGIN 
     EXEC SP_OAGetErrorInfo @object, @errSrc OUT, @returnText OUT
     RETURN
    END
END;

 

sqlserver 实现数据变动触发信息

标签：