Error checking with sp_send_dbmail

Question

This question is kind of multifaceted. First, what I'm trying to actually accomplish:

I am in an environment where there is not really heavy data validation on inputs. So there is lots of opportunity for errors from a business logic perspective. This alone is not the biggest sin, but there is no form of followup/reconciliation that checks for the existence of conditions that violate business rules. People just discover them when looking at reports that "don't smell right."

Without talking about the upgrades to infrastructure that would be the most suitable solution to prevent erroneous data in the first place, what I'm looking at as a first step is shedding some light on errors or possible errors so they can be proactively resolved, rather than waiting for end users to "discover" them (and feeling that it's necessary to "sniff test" every single report they consume). So what I'm trying to do is create an easy to use template for non-sql geniuses to be able to set up basic error monitoring.

So, here's my idea, what I don't like about it, and then I welcome any other ideas or alternatives.

Checking for errors is simple. There are very basic tests that can be run like:

select InterestingField
from TableWithErrors
where ConditionThatRepresentProblems

By definition, we expect the query to return no results. By nature, pretty much all of the queries checking for errors can be almost as small/simple. A real-life example may look more like:

--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

I'd like to be able to set up a simple template where a query like that above can be pasted in verbatim, and the value of a couple variables can updated for emailto, etc. The idea would be that if an error exists, the appropriate person will be emailed with the offending records, otherwise nothing would happen (or a passive "green light" message could be passed through).

This would be set up in a job, and any time someone has a new error that they want to monitor for, they can just come up with a simple query that checks if the error conditions exist, and emails them (or party responsible for data) automatically for resolution.

So here's the command I put together that does not work:

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

set @SendTo     = '[email protected]'
set @from_address = '[email protected]'
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';

I quickly discovered this would not work due to the well-documented sp_send_dbmail technet article saying explicitly:

"Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query."

The solution to this problem that I've found thus far have been to simply wrap the entire query, sp_send_dbmail and all into the dynamic sql text.

I do not like this solution for two basic reasons. 1) you'd have to write the query twice - once to check if the condition exists, outside of dynamic sql, then again inside the dynamic to send the email. 2) dynamic sql is ugly and hard to read. if you have any apostrophes in the code, you have to double those up for dynamic, then debug, etc, etc.

I wanted to have a simple to use solution for people who are not power users to be able to write a really simple query looking for errors, write in their email, and have that condition monitored for errors.

Can anyone suggest a different way of looking at this process with emphasis on simple for end users. It's ok if I have to set up something a little more complicated on the backend, as long as the end users can paste a simple query, enter their email, and be done, knowing that if the query ever returns results, they will be notified immediately?

For the record, this solution may be implemented on an instance running SQL Server 2005, or SQL Server 2012.

Answer 1

I would suggest one of the below:

Method 1

1. For each error condition, create an inline, table-valued user-defined function which generates the erroneous results.
2. Make a table which contains all of the function names, with a field for the email recipients.
3. Build a procedure (MailErrorResults) which takes a function name as a parameter. If will retrieve the email recipients from the table and execute msdb.dbo.sp_send_dbmail, sending in 'SELECT * FROM ' + FunctionName + '();' as the query for the attachment.

4. Build a procedure (MailErrorResultsAll) which uses a cursor to loop over the table with the function names. In the loop, you will execute dynamic SQL along these lines:

   'IF EXISTS(SELECT * FROM ' + FunctionName + '()) BEGIN EXEC MailErrorResults @FunctionName = ' + @FunctionName + '; END'

(Beware of SQL injection attacks possible by adjusting the FunctionName values in the table.)

You could omit building the separate procedure MailErrorResults, but then you are writing the entire msdb.dbo.sp_send_dbmail call in dynamic SQL, which is ugly and error prone.

You could avoid MailErrorResults hitting the function table again by sending it everything it needs (e.g. the recipients), but your dynamic SQL in MailErrorResultsAll would become more complicated, and the function table will likely be too small for this to matter. This is especially true if you end up having more information in the function table, such as a field describing the problem along with information on how to fix it.

The advantages of this method are that (1) the SQL is only written once; (2) the error detection SQL can be written using validation (no dynamic SQL quotation mark issues); (3) a reusable execution plan can be generated; and (4) eventually the check can be embedded into your application logic. The disadvantage is that the query is run twice for each check-- to see if there are errors and then during the error sending process. That disadvantage can be mitigated with Method 2.

Method 2

You would essentially reproduce Method 1, but you could eliminate the double call to the function by creating a permanent table to store the error results. Your dynamic SQL would look something like this:

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

You would have a separate table called ErrorRuns which would store each execution of this process for a particular function. You wouldn't need two procedures. Your single procedure would loop over the function table, running the above dynamic SQL for each function (getting a separate run number for each, of course), and then your msdb.dbo.sp_send_dbmail call would be passed a query like this:

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

This trades storage space and extra writes for the double reads of the existence check in Method 1. This could be superior if your error detection is resource intensive.

Method 3

One other option, which I don't recommend, is to simply put the raw SQL statements into a functions table. You could then pass them into dynamic SQL without worrying about the single quote issues, because they could be retrieved from the table as native strings. I would only explore this if you had an aversion to user-defined functions for some reason.

For example, if your end users were really writing the queries themselves and did not have user-defined function creation rights, you might have to explore this.

Another twist to this method would be to have the procedure responsible for saving the user-specific SQL execute dynamic SQL to create the user-defined function, and then use one of the methods above.

The easier you make it for end users to execute their own SQL in your environment, though, the more security concerns you will have.