SQL Server Performance

Dynamic SQL in Send Mail

Discussion in 'ALL SQL SERVER QUESTIONS' started by Hrishi_nk, Oct 28, 2013.

  1. Hrishi_nk New Member

    Hello Experts,
    I had configured Send Mail on one of my Database servers.There is procedure which sends mail after doing its work.When i run the proc in SSMS with sysadmin login it works fine and I get the mail and there is an entry for the same in sysmail_allitems.However when the same proc is invoked from a job the proc is sucessfully executed but i dont receive mail and also there is no entry in sysmail_allitems.
    All jobs owner is Administartor. SQL server Agent is running under Local System.
    Below is the sample code for send mail

    DECLARE @subject VARCHAR(500)
    DECLARE @query NVARCHAR(max)

    SELECT @subject = 'some subject '
    SET @query = 'some dynamic Query'
    --print @query
    --select @query

    EXECUTE msdb..sp_send_dbmail
    @recipients = 'recipients '
    ,@body = @error_message
    ,@query = @query
    ,@execute_query_database = 'database_name'

    Any help will be highly appreciated.
  2. Gopi Krishnan New Member

    @error_message is not defined. Also you need to pass a profile parameter. Just run the following query through job and check the mail. You need to change the profile name, reciepient list.

    DECLARE @subject VARCHAR(500)
    DECLARE @query NVARCHAR(max)
    SELECT @subject = 'test '
    SET @query = 'select getdate()'
    --print @query
    --select @query
    EXECUTE msdb..sp_send_dbmail
    @recipients = 'xxxxxx.xxx.com '
    ,@body = @subject
    ,@query = @query
    ,@execute_query_database = 'master'
    ,@profile_name ='mailprofile'

Share This Page