时间:2021-07-01 10:21:17 帮助过:11人阅读
2.2 归档运行的Log表
- <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">DBData_ArchiveLog</span><span style="color: #ff0000">]</span><span style="color: #000000">(
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">ID</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">int</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">IDENTITY</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">IP</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DBName</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DataTable</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">80</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BakQTY</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BakStartDate</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">datetime</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">BakEndDate</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">datetime</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
- ) </span><span style="color: #0000ff">ON</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span>
- <span style="color: #0000ff">GO</span><span style="color: #0000ff"><br></span>
2.3 异常错误信息表
执行的过程中会外包一层 try...catch,将操作过程中的错误信息保存在表 DBData_ArchiveErrLog。表结构如下:
- <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">DBData_ArchiveErrLog</span><span style="color: #ff0000">]</span><span style="color: #000000">(
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">ID</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">int</span><span style="color: #ff0000">]</span> <span style="color: #ff00ff">IDENTITY</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">IP</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DBName</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">60</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">DataTable</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">80</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TargetIP</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TargetDB</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">60</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TargetTable</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">80</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Errormsg</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">nvarchar</span><span style="color: #ff0000">]</span>(<span style="color: #ff00ff">max</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">,
- </span><span style="color: #ff0000">[</span><span style="color: #ff0000">TransDateTime</span><span style="color: #ff0000">]</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">varchar</span><span style="color: #ff0000">]</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #0000ff">NULL</span><span style="color: #000000">
- ) </span><span style="color: #0000ff">ON</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span> TEXTIMAGE_ON <span style="color: #ff0000">[</span><span style="color: #ff0000">PRIMARY</span><span style="color: #ff0000">]</span>
- <span style="color: #0000ff">GO</span>
- <span style="color: #0000ff">SET</span> ANSI_NULLS <span style="color: #0000ff">ON</span>
- <span style="color: #0000ff">GO</span>
- <span style="color: #0000ff">SET</span> QUOTED_IDENTIFIER <span style="color: #0000ff">ON</span>
- <span style="color: #0000ff">GO</span>
- <span style="color: #008080">--</span><span style="color: #008080"> =============================================</span><span style="color: #008080">
- --</span><span style="color: #008080"> Author: <Author,,Name></span><span style="color: #008080">
- --</span><span style="color: #008080"> Create date: <Create Date,,></span><span style="color: #008080">
- --</span><span style="color: #008080"> Description: <Description,,></span><span style="color: #008080">
- --</span><span style="color: #008080"> =============================================</span>
- <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">PROCEDURE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">SP_XXXXX_DataArchive</span><span style="color: #ff0000">]</span>
- <span style="color: #0000ff">AS</span>
- <span style="color: #0000ff">SET</span> NOCOUNT <span style="color: #0000ff">ON</span><span style="color: #000000">;
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql1</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql2</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@IP</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@DBName</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@DataTable</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@TargetIP</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@TargetDB</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@TargetTable</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@DelMaxQTY</span> <span style="color: #0000ff">INT</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@StartTime</span> <span style="color: #0000ff">DATETIME</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@EndTime</span> <span style="color: #0000ff">DATETIME</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@qty</span> <span style="color: #0000ff">INT</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@ISCHECKORDERID</span> <span style="color: #0000ff">INT</span>
- <span style="color: #008080">--</span><span style="color: #008080">--Carson 2018-12-17 备份数据的时间往往比删除的时间长3倍,因此,如果考虑将备份的操作转移到辅助库,将会对线上的操作影响降至更低</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@BakDateIP</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">30</span><span style="color: #000000">)
- </span><span style="color: #0000ff">set</span> <span style="color: #008000">@BakDateIP</span><span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">[XXX.XXX.XXX.XXX].</span><span style="color: #ff0000">‘</span><span style="color: #008080">--</span><span style="color: #008080">-----后面一定要有一个点</span><span style="color: #008080">
- --</span><span style="color: #008080">------------------------------------------------归档操作---------------------------------</span>
- <span style="color: #0000ff">DECLARE</span> DBName <span style="color: #0000ff">CURSOR</span>
- <span style="color: #0000ff">FOR</span>
- <span style="color: #0000ff">SELECT</span><span style="color: #000000"> IP ,
- DBName ,
- DataTable ,
- TargetIP ,
- TargetDB ,
- TargetTable ,
- Prerequisite ,
- DelMaxQTY ,
- ISCHECKORDERID ,
- StartTime ,
- EndTime
- </span><span style="color: #0000ff">FROM</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理器</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理数据库</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">DBData_ArchiveConfig</span><span style="color: #ff0000">]</span>
- <span style="color: #0000ff">WHERE</span> DataTable <span style="color: #808080"><></span> <span style="color: #ff0000">‘‘</span>
- <span style="color: #808080">AND</span> TargetTable <span style="color: #808080"><></span> <span style="color: #ff0000">‘‘</span>
- <span style="color: #808080">AND</span> DBNAME <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">XXXXXXXXX</span><span style="color: #ff0000">‘</span> <span style="color: #808080">and</span> SP_Name<span style="color: #808080">=</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">?????</span><span style="color: #ff0000">‘</span>
- <span style="color: #0000ff">OPEN</span><span style="color: #000000"> DBName
- </span><span style="color: #0000ff">FETCH</span> <span style="color: #0000ff">NEXT</span> <span style="color: #0000ff">FROM</span> DBName <span style="color: #0000ff">INTO</span> <span style="color: #008000">@IP</span>, <span style="color: #008000">@DBName</span>, <span style="color: #008000">@DataTable</span>, <span style="color: #008000">@TargetIP</span>, <span style="color: #008000">@TargetDB</span><span style="color: #000000">,
- </span><span style="color: #008000">@TargetTable</span>, <span style="color: #008000">@Prerequisite</span>, <span style="color: #008000">@DelMaxQTY</span>, <span style="color: #008000">@ISCHECKORDERID</span><span style="color: #000000">,
- </span><span style="color: #008000">@StartTime</span>, <span style="color: #008000">@EndTime</span>
- <span style="color: #0000ff">WHILE</span> ( <span style="color: #008000; font-weight: bold">@@fetch_status</span> <span style="color: #808080">=</span> <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> )
- </span><span style="color: #0000ff">BEGIN</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@datetime</span> <span style="color: #0000ff">DATETIME</span>
- <span style="color: #0000ff">IF</span> <span style="color: #008000">@ISCHECKORDERID</span> <span style="color: #808080"><></span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080"><></span> <span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">BEGIN</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@datetime</span> <span style="color: #808080">=</span> <span style="color: #ff00ff">CONVERT</span>(<span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>), <span style="color: #ff00ff">GETDATE</span>() <span style="color: #808080">-</span> <span style="color: #800000; font-weight: bold">30</span>, <span style="color: #800000; font-weight: bold">120</span><span style="color: #000000">)
- </span><span style="color: #0000ff">SET</span> <span style="color: #008000">@sql</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Insert into [</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@TargetIP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">].</span><span style="color: #ff0000">‘</span>
- <span style="color: #808080">+</span> <span style="color: #008000">@TargetDB</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@TargetTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
- select * FROM </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@BakDateIP</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
- with(nolock) where </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@sql1</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">DECLARE @icount INTEGER
- SELECT @icount = COUNT(1)
- FROM </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@BakDateIP</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
- where </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
- insert into [中央管理器].[中央管理数据库].dbo.DBData_ArchiveLog (IP, DBName, DataTable, BakQTY, BakStartDate, BakEndDate)
- select </span><span style="color: #ff0000">‘‘‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@IP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘‘‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘‘‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span>
- <span style="color: #808080">+</span> <span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,@icount,getdate(),null
- WHILE @icount > 0
- BEGIN
- DELETE TOP (</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DelMaxQTY</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)
- FROM </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DataTable</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
- where </span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@Prerequisite</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
- SET @icount = @icount -(</span><span style="color: #ff0000">‘</span>
- <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DelMaxQTY</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">)
- WAITFOR DELAY </span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">00:00:01</span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">
- END </span><span style="color: #ff0000">‘</span>
- <span style="color: #0000ff">BEGIN</span><span style="color: #000000"> TRY
- </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql</span><span style="color: #000000">)
- </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql1</span><span style="color: #000000">)
- </span><span style="color: #0000ff">END</span><span style="color: #000000"> TRY
- </span><span style="color: #0000ff">BEGIN</span><span style="color: #000000"> CATCH
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Errmsg</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@Errmsg</span><span style="color: #808080">=</span><span style="color: #000000">ERROR_MESSAGE()
- </span><span style="color: #008080">--</span><span style="color: #008080">----0001 BEGIN SAVE ERR LOG IN TABLE</span>
- <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理器</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">中央管理数据库</span><span style="color: #ff0000">]</span>.<span style="color: #ff0000">[</span><span style="color: #ff0000">dbo</span><span style="color: #ff0000">]</span>.DBData_ArchiveErrLog (<span style="color: #ff0000">[</span><span style="color: #ff0000">IP</span><span style="color: #ff0000">]</span> ,<span style="color: #ff0000">[</span><span style="color: #ff0000">DBName</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">DataTable</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TargetIP</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TargetDB</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">TargetTable</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">Errormsg</span><span style="color: #ff0000">]</span> ,<span style="color: #ff0000">[</span><span style="color: #ff0000">TransDateTime</span><span style="color: #ff0000">]</span><span style="color: #000000">)
- </span><span style="color: #0000ff">VALUES</span>(<span style="color: #008000">@IP</span>, <span style="color: #008000">@DBName</span>, <span style="color: #008000">@DataTable</span>, <span style="color: #008000">@TargetIP</span>, <span style="color: #008000">@TargetDB</span>, <span style="color: #008000">@TargetTable</span>,<span style="color: #008000">@Errmsg</span>,<span style="color: #ff00ff">convert</span>(<span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">25</span>),<span style="color: #ff00ff">GETDATE</span>(), <span style="color: #800000; font-weight: bold">120</span><span style="color: #000000">))
- </span><span style="color: #008080">--</span><span style="color: #008080">----0001 END</span>
- <span style="color: #008080">--</span><span style="color: #008080">-----------0002 BEGIN SEND EMAIL MESSAGE---------------- </span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Subject</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #800000; font-weight: bold">200</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Body</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@SPName</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">nvarchar</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
- </span><span style="color: #0000ff">SET</span> <span style="color: #008000">@Subject</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">数据库归档异常 -重要!;ServerIP:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@IP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> DB:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@Body</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"><html><body>Dear All,<br> <br> ServerIP:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span><span style="color: #008000">@IP</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> ; DataBase:</span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #008000">@DBName</span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">上的Table归档异常,请及时检查!!!
- <br> You can get detail information from the table. <br><br><table border=1 bgcolor=#aaff11></span><span style="color: #ff0000">‘</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@Body</span> <span style="color: #808080">=</span> <span style="color: #008000">@Body</span><span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"><tr bgcolor=#ff3311><td>ServerIP</td><td>DBName</td><td>TableName</td><td>TargetIP</td><td>TargetDB</td><td>Errmsg</td><td>TransDateTime</td></tr></span><span style="color: #ff0000">‘</span>
- <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">=</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"><tr bgcolor=#ffaa11><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@IP</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span> <span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DBName</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>)) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@DataTable</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td>
- <td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@TargetIP</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CAST</span>(<span style="color: #008000">@TargetDB</span> <span style="color: #0000ff">AS</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #800000; font-weight: bold">50</span>))<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">SUBSTRING</span>(<span style="color: #008000">@Errmsg</span>,<span style="color: #800000; font-weight: bold">1</span>, <span style="color: #800000; font-weight: bold">100</span>)<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td><td></span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #ff00ff">CONVERT</span>(<span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span>), <span style="color: #ff00ff">GETDATE</span>(), <span style="color: #800000; font-weight: bold">21</span>)<span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></td></tr></span><span style="color: #ff0000">‘</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@Body</span> <span style="color: #808080">=</span> <span style="color: #008000">@Body</span> <span style="color: #808080">+</span> <span style="color: #008000">@SPName</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"></table></span><span style="color: #ff0000">‘</span>
- <span style="color: #0000ff">SET</span> <span style="color: #008000">@BODY</span><span style="color: #808080">=</span><span style="color: #ff00ff">REPLACE</span>(<span style="color: #008000">@BODY</span>,<span style="color: #ff0000">‘‘‘‘</span>,<span style="color: #ff0000">‘‘</span><span style="color: #000000">)
- </span><span style="color: #0000ff">IF</span> <span style="color: #ff00ff">REPLACE</span>(<span style="color: #008000">@BODY</span>,<span style="color: #ff0000">‘</span> <span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘‘</span>)<span style="color: #808080"><></span><span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">BEGIN</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@AllEmailToAddress</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">3000</span>)<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@AllEmailCcAddress</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">3000</span>)<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@Allprofile_name</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">100</span>)<span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@AllEmailToAddress</span><span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@AllEmailCcAddress</span><span style="color: #808080">=</span><span style="color: #ff0000">‘‘</span>
- <span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">TOP</span> <span style="color: #800000; font-weight: bold">1</span> <span style="color: #008000">@Allprofile_name</span><span style="color: #808080">=</span>NAME <span style="color: #0000ff">FROM</span><span style="color: #000000"> msdb.dbo.sysmail_profile
- </span><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span><span style="color: #000000"> profile_id
- </span><span style="color: #0000ff">EXEC</span> msdb..sp_send_dbmail <span style="color: #008000">@profile_name</span> <span style="color: #808080">=</span> <span style="color: #008000">@Allprofile_name</span> <span style="color: #008080">--</span><span style="color: #008080"> profile 名称 </span>
- ,<span style="color: #008000">@recipients</span> <span style="color: #808080">=</span> <span style="color: #008000">@AllEmailToAddress</span> <span style="color: #008080">--</span><span style="color: #008080"> 收件人邮箱 </span>
- ,<span style="color: #008000">@copy_recipients</span><span style="color: #808080">=</span><span style="color: #008000">@AllEmailCcAddress</span><span style="color: #000000">
- ,</span><span style="color: #008000">@subject</span> <span style="color: #808080">=</span> <span style="color: #008000">@Subject</span> <span style="color: #008080">--</span><span style="color: #008080"> 邮件标题 </span>