How to send an email on finding bad data in a table in sql server

Question

I have below EmployeeData table and due to some known reasons BAD data is getting inserted and i am working on code fix, but meanwhile i want to check for bad data until the fix is deployed. If you observe John has 1 for both Active and IsEmpTermed columns.

Currently i am running the below query to check for Bad data manually in SQL Server. I want to automate this task and send an email to me if it finds bad data i.e. Active =1 and IsEmpTermed =1. What and How to do it?

select * from EmployeeData Where Active=1 and IsEmpTermed = 1;

Thanks in advance.

Answer 1

Quick way would be use the @query argument with sp_send_dbmail and put that code in a SQL Server Agent Job.

Here's a simple example of what that sp_send_dbmail looks like:

EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = ''  --This is specific to your setup
   ,@recipients = '[email protected]'
   ,@subject = 'This is the subject'
   ,@query = 'select EmpID, FirstName, LastName from EmployeeData Where Active=1 and IsEmpTermed = 1' --The query
   ,@execute_query_database = ''  --The name of your database goes here where the query should be executed

That will basically execute the query you specified in @query, dump the results into an email and send it to whomever is in @recipients

If you don't know what the @profile_name is for your server configuration you can run

EXEC msdb.dbo.sysmail_help_profileaccount_sp; 

That will return a list of the mail profiles configured on your server. You will use the value from the "profile_name" column. If there are multiples, this is something I can't tell you as it is specific to your server configuration.

Once you have all that defined and working by manually running it in SSMS, go create a SQL Server Agent Job with a T-SQL step and add the "EXEC msdb.dbo.sp_send_dbmail" code. Defined a schedule at whatever frequency you would like it to run.

A lot of times I'll code it to check if the data issue exists before sending an email, so it will only send an email when the data issue exists so it won't keep spamming me if there are no issues, something like this

--The IF EXISTS checks if the data issue exists before sending an email
IF EXISTS(SELECT TOP 1 'x' FROM dbname.dbo.EmployeeData Where Active=1 and IsEmpTermed = 1)
BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = ''  --This is specifc to your setup
           ,@recipients = '[email protected]'
           ,@subject = 'This is the subject'
           ,@query = 'select EmpID, FirstName, LastName from EmployeeData Where Active=1 and IsEmpTermed = 1' --The query
           ,@execute_query_database = ''  --The name of your database goes here
END