用sp_send_dbmail校验错误

Question

这个问题是多方面的。首先，我想要实现的是：

在我所处的环境中，输入方面没有真正的大量数据验证。因此，从业务逻辑的角度来看，有很多错误的机会。这本身并不是最大的罪过，但没有任何形式的后续/协调检查是否存在违反业务规则的条件。当人们看到“闻起来不对”的报告时，人们就会发现它们。

没有讨论对基础设施的升级(这将是防止错误数据发生的最合适的解决方案)，我所看到的第一步是对错误或可能的错误进行一些说明，以便能够主动地解决这些错误，而不是等待最终用户“发现”它们(并且觉得有必要“嗅探测试”他们使用的每一个报告)。因此，我要做的是为非sql天才创建一个易于使用的模板，以便能够设置基本的错误监视。

所以，这是我的想法，我不喜欢它，然后我欢迎任何其他的想法或替代。

检查错误很简单。有一些非常基本的测试可以运行如下：

select InterestingField
from TableWithErrors
where ConditionThatRepresentProblems

根据定义，我们期望查询不会返回任何结果。本质上，几乎所有检查错误的查询几乎都是小/简单的。一个真实的例子看起来可能更像：

--look for foreignpin duplicates where there should not be any
select
    ai.ForeignPin
    , ai.AccountName    
    , ai.AcctURL
    , ai.AccountManager
from
    DB.dbo.vAccountIndex ai

where
    ai.ForeignPin in
    (
        select ai.ForeignPin
        from DB.dbo.vAccountIndex ai 
        where ai.ForeignPin is not null
        group by ai.ForeignPin 
        having count(ai.Foreignpin) > 1
    )
order by 
    ai.ForeignPin
    , ai.AccountName

我希望能够建立一个简单的模板，上面这样的查询可以逐字粘贴，并且一对变量的值可以更新为emailto等等。这样做的想法是，如果存在错误，适当的人将通过电子邮件发送违规记录，否则不会发生任何事情(或者传递被动的“绿灯”消息)。

这将在工作中设置，每当有人想要监视新的错误时，他们就可以提出一个简单的查询，检查错误条件是否存在，并自动发送电子邮件给他们(或负责数据的一方)以求解决。

下面是我放在一起的不起作用的命令：

declare
    @SendTo varchar(255) 
    , @from_address varchar(255)
    , @Subject varchar(255)
    , @body varchar(8000) 
    , @@SQL nvarchar(4000)  

set @SendTo     = 'AccountManagement@mycompany.com'
set @from_address = 'sysadmin@mycompany.com'
set @Subject    = 'Foregin pin duplicate'
set @body       = 'There is a duplicate value in Foreign pin field of Account table. Duplicates in this field may result in double counting of sales, or failure of sales transaction-related tasks.'
set @@SQL       = 'select * from @@results'



--put your error sniffing query here, stuffing results into this table variable
declare @@results table
(
    ForeignPin varchar(255)
    , AccountName varchar(36)
    , AcctURL varchar(255)
    , AccountManager varchar(255)
)

insert into @@results

select
    ai.ForeignPin
    , ai.AccountName    
    , ai.AcctURL
    , ai.AccountManager
from
    DB.dbo.vAccountIndex ai

where
    ai.ForeignPin in
    (
        select ai.ForeignPin
        from DB.dbo.AccountIndex ai 
        where ai.ForeignPin is not null
        group by ai.ForeignPin 
        having count(ai.Foreignpin) > 1
    )
order by 
    ai.ForeignPin
    , ai.FuneralHome


--the dbmail doesn't seem to like the @@sql variable...
if exists (select * from @@results)

BEGIN

    EXEC msdb.dbo.sp_send_dbmail
     --   @profile_name = 'your mail profile',
        @recipients = @SendTo
        , @body = @body
        , @subject = @Subject

      -- to send file attachments (if any)
      --, @file_attachments = 'L:\logs.TXT',

      -- send query results 
        , @query = @@SQL

END

else print 'No Error Found';

我很快发现，由于有良好文档的sp_send_dbmail technet文章明确指出：

“请注意，查询是在单独的会话中执行的，因此调用sp_send_dbmail的脚本中的局部变量对查询不可用。”

到目前为止，我发现的这个问题的解决方案是简单地将整个查询、sp_send_dbmail和所有内容包装到动态的sql文本中。

我不喜欢这个解决办法，有两个基本原因。1)您必须编写两次查询--一次以检查条件是否存在，在dynamic sql之外，然后在dynamic中再次编写，以发送电子邮件。2)动态sql的丑陋性和易读性。如果代码中有任何撇号，则必须将其加倍用于动态、调试等。

我想为那些不是高级用户的人提供一个简单的解决方案，让他们能够编写一个非常简单的查询，查找错误，在他们的电子邮件中写，并对错误进行监控。

谁能建议一个不同的方法来看待这个过程，强调简单的最终用户。如果我必须在后端设置一些更复杂的东西，只要最终用户能够粘贴一个简单的查询，输入他们的电子邮件，然后完成，知道如果查询返回结果，他们就会立即收到通知。

作为记录，此解决方案可以在运行Server 2005或Server 2012的实例上实现。

Answer 1

我建议如下之一：

方法1

1. 对于每个错误条件，创建一个内联的表值用户定义函数，生成错误的结果。
2. 创建一个包含所有函数名的表，其中包含电子邮件收件人的字段。
3. 构建一个以函数名作为参数的过程(MailErrorResults)。If将从表中检索电子邮件收件人并执行msdb.dbo.sp_send_dbmail，将'SELECT * FROM ' + FunctionName + '();'作为附件的查询发送。
4. 构建一个过程(MailErrorResultsAll)，它使用游标在表上循环函数名。在循环中，您将按照以下代码执行动态SQL：‘如果存在(从’+ FunctionName +‘()开始执行MailErrorResults @FunctionName =’+ @FunctionName + '；END‘

(通过调整表中的FunctionName值，注意可能发生的SQL注入攻击。)

您可以省略构建单独的过程MailErrorResults，但是接下来要用dynamic编写整个msdb.dbo.sp_send_dbmail调用，这很难看，而且容易出错。

您可以通过发送函数表所需的一切(例如收件人)来避免MailErrorResults再次命中函数表，但是MailErrorResultsAll中的动态SQL会变得更加复杂，而且函数表可能太小，这无关紧要。如果最终函数表中有更多信息，例如描述问题的字段以及如何修复问题的信息，则尤其如此。

该方法的优点是：(1)只编写一次SQL；(2)可以使用验证(不存在动态SQL引号问题)编写错误检测SQL；(3)可以生成可重用的执行计划；(4)最终可以将检查嵌入到应用程序逻辑中。缺点是每次检查都运行两次查询--查看是否有错误，然后在错误发送过程中运行。这个缺点可以用方法2来减轻。

方法2

基本上可以复制方法1，但是可以通过创建一个永久表来存储错误结果，从而消除对函数的双重调用。动态SQL如下所示：

'INSERT INTO ErrorList (ErrorRunNumber, a, b, c) SELECT ' + CONVERT(varchar(50),@ErrorRunNumber) + ', a, b, c FROM ' + @FunctionName + '();'

您将有一个名为ErrorRuns的单独表，它将存储特定函数的该进程的每次执行。你不需要做两次手术。您的单个过程将遍历函数表，为每个函数运行上述动态SQL (当然，每个函数都获得一个单独的运行号)，然后您的msdb.dbo.sp_send_dbmail调用将被传递一个如下所示的查询：

'SELECT a, b, c FROM ErrorList WHERE ErrorRunNumber = ' + CONVERT(varchar(50),@ErrorRunNumber) + ';'

这将交换存储空间和额外的写入，以便在方法1中对存在性检查进行双读。如果您的错误检测是资源密集型的，这可能更好。

方法3

我不推荐的另一个选项是将原始SQL语句简单地放入函数表中。然后，您可以将它们传递到动态SQL中，而不必担心单引号问题，因为它们可以作为本机字符串从表中检索。只有在出于某种原因不愿使用用户定义的函数时，我才会探讨这个问题。

例如，如果您的最终用户确实是在自己编写查询，并且没有用户定义的函数创建权限，那么您可能不得不探究这一点。

这种方法的另一个转折是让过程负责保存特定于用户的SQL，执行动态SQL来创建用户定义的函数，然后使用上面的方法之一。

但是，对于最终用户来说，在您的环境中执行自己的SQL越容易，您就会有更多的安全问题。