创建作业定时执行SQL语句同步数据

DECLARE @ServiceUrl AS VARCHAR(1000)
DECLARE @UrlAddress VARCHAR(500)
SET @UrlAddress='http://47.96.236.193:88/OA/Common/API/HRAttendance.ashx'
DECLARE @Object AS INT
DECLARE @ResponseText AS VARCHAR(8000) 
DECLARE @Body AS VARCHAR(MAX) 
DECLARE @temp TABLE
    (
      empno VARCHAR(50) ,
      empname VARCHAR(50) ,
      worktime int ,
      workdate DATETIME,
      fk_id VARCHAR(36)
    )
--以下参数对应WebService中4个参数的[参数名]
DECLARE @type VARCHAR(4) ,
    @productkey VARCHAR(10) ,
    @hard VARCHAR(4) ,
    @log VARCHAR(3)
SET @type = 'type'
SET @productkey = 'productkey'
SET @hard = 'hard'
SET @log = 'log'
DECLARE @type_value VARCHAR(10) ,
    @productkey_value VARCHAR(100) ,
    @hard_value VARCHAR(10) ,
    @log_value VARCHAR(MAX)
SET @type_value = 'zk'
SET @productkey_value='2a087045-2c2d-4f75-af3d-6ba7c67fa8f8'
SET @hard_value = '1'
SET @log_value = ''

--生成LOG 
DECLARE @empno VARCHAR(50) ,
    @empname VARCHAR(50) 
 
DECLARE @worktime int ,
    @workdate DATETIME,
    @fk_id VARCHAR(36)
DECLARE @userID INT
SET @userID = 0
DECLARE @dWKTime DATETIME
DECLARE @pageSize INT
SET @pageSize = 0

--创建游标
DECLARE cur CURSOR
FOR
    SELECT TOP 2 t2.empno ,
            t2.empname ,
            t1.LogDate ,
            t1.LogTime,t1.[GUID]  AS fk_id
    FROM    mj_mjdata t1 LEFT JOIN dbo.rs_emp t2 ON t1.EmpSysID=t2.EmpSysID
    WHERE   iSyncStatus = 0 and empno is NOT NULL AND EmpNo='134343434'
    ORDER BY LogDate ,
            LogTime
OPEN cur
FETCH NEXT  FROM cur  INTO @empno, @empname,
    @workdate,@worktime,@fk_id
WHILE ( @@fetch_status = 0 ) 
    BEGIN        
             --(1)生成所需要的数据 userID  dWKTime
        SET @dWKTime = CONVERT(DATETIME, CONVERT(VARCHAR(100), @workdate, 23)
            + ' ' + dbo.GetTimeStrEx(@worktime), 20);     
        IF @empno IS NOT NULL
        BEGIN
                        SET @log_value = @log_value
                            + CONVERT(VARCHAR(100), @empno) + '|'
                            + CONVERT(VARCHAR(100), @dWKTime, 120) + ';'
                        INSERT  INTO @temp
                                ( empno ,
                                  empname ,
                                  worktime ,
                                  workdate,
                                  fk_id
                                )
                        VALUES  ( @empno , -- empno - varchar(50)
                                  @empname , -- empname - varchar(50)
                                  @worktime , -- worktime - int
                                  @workdate,  -- workdate - datetime
                                  @fk_id  --考勤数据主键id
                                )
                        SET @pageSize = @pageSize + 1
            --分页
                        IF @pageSize =1000
                            BEGIN                 
                                SET @ServiceUrl = @UrlAddress 
                                SET @Body = @type + '=' + @type_value + '&'
                                    + @productkey + '=' + @productkey_value
                                    + '&' + @hard + '=' + @hard_value + '&'
                                    + @log + '=' + @log_value  
                            
                                EXEC sp_OACreate 'MSXML2.ServerXMLHTTP',
                                    @Object OUT;
                                EXEC sp_OAMethod @Object, 'open', NULL, 'post',
                                    @ServiceUrl, 'false'
                                EXEC sp_OAMethod @Object, 'setRequestHeader',
                                    NULL, 'Content-Type',
                                    'application/x-www-form-urlencoded'
                                EXEC sp_OAMethod @Object, 'send', NULL, @Body
                                EXEC sp_OAMethod @Object, 'responseText',
                                    @ResponseText OUTPUT
                                EXEC sp_OADestroy @Object                        
                        --更新同步状态
                                IF LTRIM(RTRIM(@ResponseText)) = '0' 
                                    BEGIN       
                                        UPDATE  mj_mjdata 
                                        SET     mj_mjdata.iSyncStatus = 1
                                        FROM    @temp AS t
                                        WHERE   mj_mjdata.[GUID] = t.fk_id
                                        DELETE  @temp
                                    END
                              
                                SET @log_value = ''
                                SET @pageSize = 0    
                                                    
                            END 
                    END
            --分页结束                 
         
        FETCH NEXT FROM cur INTO @empno, @empname,
             @workdate,@worktime,@fk_id
    END
CLOSE cur        
DEALLOCATE cur
--log结束
IF @log_value!=''
BEGIN
SET @ServiceUrl = @UrlAddress 
SET @Body = @type + '=' + @type_value + '&' + @productkey + '='
    + @productkey_value + '&' + @hard + '=' + @hard_value + '&' + @log + '='
    + @log_value
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @Object OUT;
EXEC sp_OAMethod @Object, 'open', NULL, 'post', @ServiceUrl, 'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', NULL, 'Content-Type',
    'application/x-www-form-urlencoded'
EXEC sp_OAMethod @Object, 'send', NULL, @Body
EXEC sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OADestroy @Object
PRINT LTRIM(RTRIM(@ResponseText)) ;
--更新同步状态
IF LTRIM(RTRIM(@ResponseText)) = '0' 
    BEGIN
        UPDATE  mj_mjdata 
        SET     mj_mjdata.iSyncStatus = 1
        FROM    @temp AS t
        WHERE   mj_mjdata.[Guid] = t.fk_id
        DELETE  @temp
    END
    END

--开启组件服务(执行上面作业步骤sql前执行下面语句)
exec sp_configure 'show advanced options', 1;
RECONFIGURE;exec sp_configure 'Ole Automation Procedures',1;RECONFIGURE; --开启

----------------(如果上面语句执行了,执行作业返回结果为空的话就执行下面语句)先执行下面的SQL---------------------------------------------------

IF EXISTS(SELECT name FROM sys.objects  
          WHERE name = N'dbo.sp_HexToChar')  
    DROP PROCEDURE HexToChar;  
GO  
CREATE PROCEDURE dbo.sp_HexToChar  
    @BinValue varbinary(255),  
    @HexCharValue nvarchar(255) OUTPUT  
AS  
    DECLARE @CharValue nvarchar(255);  
    DECLARE @Position int;  
    DECLARE @Length int;  
    DECLARE @HexString nchar(16);  
    SELECT @CharValue = N'0x';  
    SELECT @Position = 1;  
    SELECT @Length = DATALENGTH(@BinValue);  
    SELECT @HexString = N'0123456789ABCDEF';  
    WHILE (@Position <= @Length)  
    BEGIN  
        DECLARE @TempInt int;  
        DECLARE @FirstInt int;  
        DECLARE @SecondInt int;  
        SELECT @TempInt = CONVERT(int, SUBSTRING(@BinValue,@Position,1));  
        SELECT @FirstInt = FLOOR(@TempInt/16);  
        SELECT @SecondInt = @TempInt - (@FirstInt*16);  
        SELECT @CharValue = @CharValue +  
            SUBSTRING(@HexString, @FirstInt+1, 1) +  
            SUBSTRING(@HexString, @SecondInt+1, 1);  
        SELECT @Position = @Position + 1;  
    END  
    SELECT @HexCharValue = @CharValue;  
GO  
DECLARE @BinVariable varbinary(35);  
DECLARE @CharValue nvarchar(35);  

SET @BinVariable = 123456;  

EXECUTE dbo.sp_HexToChar  
    @binvalue = @BinVariable,  
    @HexCharValue = @CharValue OUTPUT;  

SELECT @BinVariable AS BinaryValue,  
    @CharValue AS CharacterRep;  
GO  

-----------------------------------------先执行上面的SQL 在执行下面的


    CREATE PROCEDURE dbo.sp_DisplayOAErrorInfo  
    @Object int,  
    @HResult int  
AS  
    DECLARE @Output nvarchar(255);  
    DECLARE @HRHex nchar(10);  
    DECLARE @HR int;  
    DECLARE @Source nvarchar(255);  
    DECLARE @Description nvarchar(255);  
    PRINT N'OLE Automation Error Information';  
    EXEC sp_HexToChar @HResult, @HRHex OUT;  
    SELECT @Output = N'  HRESULT: ' + @HRHex;  
    PRINT @Output;  
    EXEC @HR = sp_OAGetErrorInfo  
        @Object,  
        @Source OUT,  
        @Description OUT;  
    IF @HR = 0  
    BEGIN  
        SELECT @Output = N'  Source: ' + @Source;  
        PRINT @Output;  
        SELECT @Output = N'  Description: '  
               + @Description;  
        PRINT @Output;  
    END  
    ELSE  
    BEGIN  
       PRINT N' sp_OAGetErrorInfo failed.';  
       RETURN;  
    END  
GO  

 

--用get请求测试组件是否正常运行
declare @ServiceUrl as varchar(1000) 
set @ServiceUrl = 'http://localhost:8015/om/default.aspx'
DECLARE @data varchar(max);
set @data=''                  

Declare @Object as Int
Declare @ResponseText AS  varchar(8000)   ;      
Exec sp_OACreate 'Msxml2.ServerXMLHTTP.3.0', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'GET',@ServiceUrl,'false'
Exec sp_OAMethod @Object, 'send', NULL, @data --发送数据
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT
EXEC sp_OAGetErrorInfo @Object --异常输出
Select  @ResponseText 
Exec sp_OADestroy @Object
GO
 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页