This is SQL Server notes

定时备份JOB

ALTER proc [dbo].[proc_BackUpDB]
  @bktype nvarchar(10)='FULL'  --备份类型:'FULL' 完整备份数据库
as 
declare @dbName varchar(200)
declare @bkPath varchar(1000)
declare @dtCur datetime
declare @sql varchar(8000) 
declare @bkFileName varchar(1000)

  set @dbName= db_name() 
  set @dtCur = getdate()
  select  @bkPath= 'D:\bakList' --备份文件存放地址

  if @bkPath is null
    return 'a'
  if substring(@bkPath,len(@bkPath),1)<>'\'
    set @bkPath=@bkPath +'\'
  
  set @bkFileName= @dbName+ cast(datepart(yyyy,@dtCur) as varchar(4)) 
  + left('00',2-len(cast(datepart(mm,@dtCur) as varchar(2)))) + cast(datepart(mm,@dtCur) as varchar(2)) 
  + left('00',2-len(cast(datepart(dd,@dtCur) as varchar(2)))) + cast(datepart(dd,@dtCur) as varchar(2))
  + left('00',2-len(cast(datepart(hh,@dtCur) as varchar(2)))) + cast(datepart(hh,@dtCur) as varchar(2))
  + left('00',2-len(cast(datepart(mi,@dtCur) as varchar(2)))) + cast(datepart(mi,@dtCur) as varchar(2))
  + left('00',2-len(cast(datepart(ss,@dtCur) as varchar(2)))) + cast(datepart(ss,@dtCur) as varchar(2))
         
  if @bktype = 'FULL'
    set @bkFileName=@bkFileName  +'FULL' + '.bak'
  else
    set @bkFileName=@bkFileName + 'DIFF' + '.bak'
  print @bkFileName
  --set @sql='Backup  database '  + @dbname + ' To Disk=''' + @bkpath + @bkFileName + ''' with ' + case @bktype when 'FULL' then ' RETAINDAYS =2, '  else  '  RETAINDAYS=1, DIFFERENTIAL,'  end +  ' INIT' 
  set @sql='Backup  database '  + @dbname + ' To Disk=''' + @bkpath + @bkFileName + ''' with ' + case @bktype when 'FULL' then ''  else  'DIFFERENTIAL,'  end +  'INIT' 
  print @sql 
  exec(@sql)
  
  DECLARE @bkFileFullName varchar(2000)
  declare @ReturnValue varchar(3000)
  set  @bkFileFullName  = @bkpath + @bkFileName

  RESTORE VERIFYONLY FROM   DISK = @bkFileFullName

SQL Server定时调用接口

首先新建一个存储过程，可以调用接口

CREATE proc [dbo].[proc_SendRequest]
(@url VARCHAR(500))

AS
BEGIN
DECLARE @object int,@status int,@returnText NVARCHAR(max),
@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    

END;

存储过程建立完成后，使用EXEC调用该过程，事例：

exec proc_SendRequest 'http://10.66.52.117:888/api/test' --接口地址

执行SQL后会报SQL Server 阻止了对组件“Ole Automation Procedures”的 过程“sys.sp_OACreate”的访问的错误，需要我们开启Ole Automation Procedures，使用下面语句进行开启

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

现在执行此命令后可以成功调用接口,然后我们只要利用SQL Server的定时JOB即可实现定时调用接口的功能