本地调试及服务器需要安装Oracle数据源连接环境才能连接Oracle数据库。
安装配置过程中有很多坑，特记录一下。
SQL Server 备份检查及监控
对数据库定期备份进行检查,并监控容量增长.可以用以下SQL查询并推送消息。
|
1 2 3 |
select database_name,name,backup_start_date,CAST(backup_size/(1024*1024*1024) AS DEC(28,2))AS [备份大小(GB)],CAST(compressed_backup_size /(1024*1024*1024) AS DEC(28,2)) AS [压缩后(GB)] from msdb..backupset where DATEDIFF(day,backup_start_date,GETDATE())=0 --当天的备份 |
WebApi+SQLServer定时任务方案
工作中有很多定期执行、定时同步的需求，最适合的方案是SqlServer+Topshelf+Quartznet ，用SqlServer做任务池，用Quartznet做调度计划，用Topshelf将项目部署为系统服务后台运行。
但是整个项目比较庞大，一时半会做不出来。结合手工已经在用的组件，设计了WebApi+SQLServer 的方案。在WebApi里写工作任务，利用SQLServer的维护计划做调度，需要有安装IIS和SQL数据库。
1.首先是写一个可以http调用的api程序。以下程序调用路径是 /api.aspx?act=GO
当然也可以写一个规范的MVC api程序。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
using System; using System.Reflection; using System.Web.UI; using Newtonsoft.Json; namespace MIS.范例 { public partial class api : Page { private string errcode; private string errmsg; private string info; protected void Page_Load(object sender, EventArgs e) { this.errcode = "0"; this.errmsg = ""; this.info = ""; string act = Request.QueryString["act"]; if (!string.IsNullOrEmpty(act)) { try { //执行act动作 var result = GetType().GetMethod(act, BindingFlags.Instance | BindingFlags.Public).Invoke(this, null) as string; if (!string.IsNullOrEmpty(result)) { Context.Response.ContentType = "text/plain"; Response.Write(result); } } catch (Exception ex) { //错误 Context.Response.ContentType = "text/plain"; Response.Write(JsonConvert.SerializeObject(new { this.errcode, errmsg = ex.ToString(), this.info })); } Response.End(); } } //任务 /act=GO public string GO() { //执行动作 //返回JSON结果 return JsonConvert.SerializeObject(new { this.errcode, this.errmsg, this.info }); } } } |
2. 在SQLServer维护计划里添加T-SQL语句任务，并设置按周期执行。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
use DATABASE declare @url as Varchar(200) ,@PostData Varchar(10),@ResponseText as Varchar(8000) declare @errcode nvarchar(20),@errmsg nvarchar(2000) set @url='http://10.0.0.1:8080/api?act=GO' exec P_Url_SendRequest @url ,@PostData,@ResponseText OUTPUT select @errcode=StringValue from parsejson(@ResponseText) where NAME='errcode' select @errmsg='考勤同步失败,'+StringValue from parsejson(@ResponseText) where NAME='errmsg' --errcode errmsg info if @errcode<>'0' begin exec P_Send_TextMsg '企业微信消息','wxuser1,wxuser2',@errmsg end |
3. 以上SQL里用到的函数组件
P_Url_SendRequest 连接URL，支持POST\GET 支持返回值
parsejson JSON解析函数
P_Send_TextMsg 企业微信消息推送执行结果
SQL Server 智能重建所有表索引
一般使用 dbcc dbreindex(@TABLENAME,”,90) 来重建表索引，但数据库中表较多需要批量全部重建，有的碎片程度不高或数据行数不多，不需要重建时，可以参考使用如下批处理。
SQL Server 防注入参数化之 Where in
程序中执行SQL时，可以使用参数化防注入，如：
|
1 |
select * from tablenmae where id = @a --@a=123 |
但是where id形式不可以直接使用参数化：
|
1 |
select * from tablenmae where id in('0,1,2,3') |
将逗号分隔的字符串转换为参数table，再使用参数化调用，如：
|
1 2 3 |
declare str vchar(100) set str='1,2,3' select * from tablename where id in (select toint from StrTointTable(@str,',') ) |
where in 字符串：
|
1 2 3 |
declare str vchar(100) set str='abc,bbb,ccc' select * from tablename where name in (select string from StrToTable(@str,',') |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
IF OBJECT_ID('dbo.StrToTable', 'TF') IS NOT NULL DROP FUNCTION dbo.StrToTable; GO CREATE Function StrToTable(@str nvarchar(2000),@split nvarchar(100)) Returns @tableName Table ( string varchar(50) ) As --该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表 Begin set @str = @str+@split declare @splen int --分割符长度 set @split=ltrim(rtrim(@split)) set @splen=len(@split) Declare @insertStr nvarchar(50) --截取后的第一个字符串 Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串 set @insertStr = left(@str,charindex(@split,@str)-1) set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'') Insert @tableName Values(@insertStr) while(len(@newstr)>0 and charindex(@split,@newstr)>0) begin set @insertStr = left(@newstr,charindex(@split,@newstr)-1) Insert @tableName Values(@insertStr) set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'') end if(len(@newstr)>0) begin Insert @tableName Values(@newstr) end delete from @tableName where isnull(string,'')='' Return End |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
IF OBJECT_ID('dbo.StrToIntTable', 'TF') IS NOT NULL DROP FUNCTION dbo.StrToIntTable; GO create Function StrToIntTable(@str nvarchar(2000),@split nvarchar(100)) Returns @tableName Table ( string varchar(50), toint int ) As --该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列，例如字符串'1,2,3,4,5' 将变成一个表 Begin set @str = @str+@split declare @splen int --分割符长度 set @split=ltrim(rtrim(@split)) set @splen=len(@split) Declare @insertStr nvarchar(50) --截取后的第一个字符串 Declare @newstr nvarchar(2000) --截取第一个字符串后剩余的字符串 set @insertStr = left(@str,charindex(@split,@str)-1) set @newstr = stuff(@str,1,charindex(@split,@str)-1+@splen,'') Insert @tableName Values(@insertStr,null) while(len(@newstr)>0 and charindex(@split,@newstr)>0) begin set @insertStr = left(@newstr,charindex(@split,@newstr)-1) Insert @tableName Values(@insertStr,null) set @newstr = stuff(@newstr,1,charindex(@split,@newstr)-1+@splen,'') end if(len(@newstr)>0) begin Insert @tableName Values(@newstr,null) end update @tableName set toint=cast(string as int) where PATINDEX('%[^0-9]%', string)=0 and PATINDEX('%[0-9]%', string)>0 delete from @tableName where toint is null Return End |
SQL Server float 四舍五入陷阱
检查发票里的金额税额计算SQL的时候，发现金额、税额四舍五入为2位小数的结果，有的有进位，有的没有进位。
最后检查出其中一个计算变量是float类型，乘了以后再四舍五入必出异常。
自动补卡
曾经有一家公司考勤制度很苛刻，迟到1秒、缺卡都扣钱，加班就免费做贡献。
然后就发现他们考勤数据库里潜伏了一个程序：
关于企业密码管理的思考
关键服务器、设备密码较多，不方便记忆，只能使用简单密码，容易被猜测破解；
如用复杂密码，又担心记不住，明文记录又担心被非法获取一锅端。
该方案将密码使用私人密钥加密存储密码，持有私人密钥才能解密读取，
适用多设备、多账户多人共同适用情况下的密码管理。
MSSQL调用企业微信发送消息接口封装-P_Send_TextMsg
以下是封装后的发送消息接口SQL代码。
可以直接调用 exec P_Send_TextMsg ‘应用名称’,’接收者’,’消息内容’
发送企业微信消息。
MSSQL 获取企业微信Access_Token-P_Get_Access_Token
为避免重复调用接口获取Access_Token，建立数据表存储获取的Access_Token，并记录过期时间。当在有效期内再次调用时，直接返回数据表中的Access_Token。