locked
Create dbmail send stored procedure RRS feed

  • Question


  • Goal: Send an email when a specific stored procedure is run

    1.  Create dbmail send stored procedure
    2.  Call or execute the mail stored procedure from the other stored procedure

    I can't even create the stored procedure.  It's telling me the EXEC line is incorrect.  Here is the code:

     

    USE [CVEpi]
    GO
    /****** Object: StoredProcedure [dbo].[EmailEventSend] Script Date: 02/25/2010 13:23:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Version 0.1 common email procedure
    CREATE PROCEDURE [dbo].[EmailEventSend]
    --Send Email Notification
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name='Basic',
    @recipients='mike@somewhere.com',
    @subject='You have a new event',
    @body='A new engagement has been requested. Please check the website'
    --EndEmail

     

    Then, how do I call or execute this stored procedure from within another stored procedure.

    Thanks so much for your help.

    • Moved by Pawel Marciniak Friday, February 26, 2010 5:00 PM This is not really a service broker question (From:SQL Service Broker)
    Friday, February 26, 2010 2:47 PM

Answers

  • You are missing AS. Try the following

    USE [CVEpi]
    GO
    /****** Object: StoredProcedure [dbo].[EmailEventSend] Script Date: 02/25/2010 13:23:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Version 0.1 common email procedure
    CREATE PROCEDURE [dbo].[EmailEventSend]
    AS
    --Send Email Notification
    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='Basic',
    @recipients='mike@somewhere.com',
    @subject='You have a new event',
    @body='A new engagement has been requested. Please check the website' 
    --EndEmail
    
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Naomi N Friday, February 26, 2010 7:25 PM
    • Marked as answer by Nai-dong Jin - MSFT Wednesday, March 3, 2010 2:52 AM
    Friday, February 26, 2010 5:16 PM

All replies

  • You are missing AS. Try the following

    USE [CVEpi]
    GO
    /****** Object: StoredProcedure [dbo].[EmailEventSend] Script Date: 02/25/2010 13:23:20 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Version 0.1 common email procedure
    CREATE PROCEDURE [dbo].[EmailEventSend]
    AS
    --Send Email Notification
    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name='Basic',
    @recipients='mike@somewhere.com',
    @subject='You have a new event',
    @body='A new engagement has been requested. Please check the website' 
    --EndEmail
    
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Proposed as answer by Naomi N Friday, February 26, 2010 7:25 PM
    • Marked as answer by Nai-dong Jin - MSFT Wednesday, March 3, 2010 2:52 AM
    Friday, February 26, 2010 5:16 PM
  • The create statement for your stored procedure is missing the AS keyword:

    CREATE PROCEDURE [dbo].[EmailEventSend]
    AS
    --Send Email Notification
    EXEC msdb.dbo.sp_send_dbmail 
    @profile_name = 'Basic',
    @recipients = 'mike@somewhere.com',
    @subject = 'You have a new event',
    @body = 'A new engagement has been requested. Please check the website';
    --EndEmail
    GO

    To execute from the other stored procedure just use:

    EXEC [dbo].[EmailEventSend];


    Plamen Ratchev
    Friday, February 26, 2010 5:18 PM