none
send email from sql. RRS feed

  • Question

  • Hi experts,

     

    What would be the most simple way of doing this:

     

    -execute a script every 4 hours; and if the script returns any results, send an email with an alert.

     

    99% of the times it would return no results…

     

    Ps: I said “simple”: because I will do it  on a server, I will soon give to other team; so I would like to be easy to maintain for the next guy... (using sql 2014).

    Wednesday, November 30, 2016 11:43 AM

All replies

  • Hi Maca18,

    You need create account and profile using database mail wizard 

    you need schedule sql job send email after 4 hours interval.

    USE msdb
    GO
    EXEC sp_send_dbmail @profile_name='DBA',
    @recipients='test@Example.com',
    @subject='alert mail',
    @body='This is the mail alert.'


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Wednesday, November 30, 2016 11:52 AM
  • Well, luckily I would send the email once a year; I dont want to send an email every four hours...

    Can I fit that code inside an IF?

    Wednesday, November 30, 2016 11:57 AM
  • So you have some SELECT, and if that returns more than zero rows, then you want to send an email, right?

    Do you want to include anything from the SELECT in the email? If so, how many columns and how many rows can/will the SELECT return?


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 30, 2016 12:00 PM
    Moderator
  • no rows from the select, just an email saying "check xxx"
    Wednesday, November 30, 2016 12:06 PM
  • So if you have database email working properly and you have code to send an email, it is very easy. 

    You can mentioned this inside an IF clause with condition if any records generated by previous SELECT> Send an Email else exit. Something like this:

    declare @rowcount int

    select * from [dbo].[test]

    set @rowcount = @@ROWCOUNT

    select @rowcount

    If @rowcount = 0

    begin

    EXEC msdb.dbo.sp_send_dbmail

    @recipients = 'emailid',

    @body = 'test',

    @subject = 'test'

    end


    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Wednesday, November 30, 2016 12:11 PM no row condition
    • Proposed as answer by SumitDBA Thursday, December 1, 2016 5:19 AM
    Wednesday, November 30, 2016 12:10 PM
  • Then you can do something like:

    IF EXISTS (your_SELECT_goes_here)

    BEGIN

      EXEC sp_sendmail ...

    END


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 30, 2016 12:24 PM
    Moderator
  • Refer same link

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b2f4dc8-c83c-4188-96db-4570560fc75c/email-from-mssql-if-some-condition-is-true?forum=transactsql


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Wednesday, November 30, 2016 12:41 PM
  • if @rowcount <> 0 should be, right?

    why couldn't it be

    if @@ROWCOUNT<>0

    Wednesday, November 30, 2016 4:42 PM
  • I would configure your agent to send mail notification.

    Then write a job with a step raising an error under the condition you seek to identify.  Set your job notification to email on failure. Schedule it to run every 4 hours.

    This way you would both see the error in the agent history, have your 4 hours recurrence and receive your email alerts with minimal effort.

    Thursday, December 1, 2016 2:02 AM
  • Hi Maca

    @@Rowcount will be set to 0 if there are no row effected in previous TSQL operation. So it will be always either 0 or greater than 0.

    In your case if you want to send an email when NO ROWs returned checking @@Rowcount = 0 will work fine.  Below is code Sunil has mentioned above

    declare @rowcount int
    --select * from [dbo].[test]
    --mention you SELECT query here
    set @rowcount = @@ROWCOUNT
    select @rowcount

    If @rowcount = 0
    begin
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'emailid',
    @body = 'test',
    @subject = 'test'
    end


    • Edited by SumitDBA Thursday, December 1, 2016 5:18 AM edit
    Thursday, December 1, 2016 5:17 AM
  • Antoine,

    This approach may not be efficient as will not sent email immediately plus introduce more pieces into system. Why not use directly from TSQL code with just checking @@rowcount?


    Kindly mark the reply as answer if they help

    Thursday, December 1, 2016 6:40 AM
  • Antoine,

    This approach may not be efficient as will not sent email immediately plus introduce more pieces into system. Why not use directly from TSQL code with just checking @@rowcount?


    Kindly mark the reply as answer if they help

    Because the poster clearly wrote he wants to send the email every 4 hours which is clearly a job for a scheduler...

    He also wrote this would be an alert.  Hence the bonus of having a job history which would allow to display the history of all failures.


    • Edited by Antoine F Friday, December 2, 2016 5:18 AM
    Friday, December 2, 2016 1:33 AM