sp_send_dbmail not working but send test e-mail for database mail working

  • I set up database mail on a sql server 2005 cluster and when I send a test e-mail form SSMS it works flawlessly. When I try to send an email using msdb.dbo.sp_send_dbmail via the query window or in a job I get the following error message

    Msg 14607, level 16, state 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid.

    I tried adding @profile_name and then I get the following error message

    Msg 8143, Level 16, State 1, Procedure sp_send_dbmail, line 0 Parameter ‘@profile_name’ was supplied multiple times

    I am pulling out what little hair I have left trying to figure out what is wrong.

    Thanks

    David Weil

  • Is one of your profiles (or maybe the only profile) defined as the default? If not you might try that..

    CEWII

  • That was one of the first things I checked.

    In profile security/public profiles the profile is defined as the default profile.

    thanks

    David Weil

  • What is the exact command you are trying to execute?

    CEWII

  • simply

    exec msdb.dbo.sp_send_dbmail 'dweil@jewishhome.org', @body = 'Let keep it simple'

    thanks

    David Weil

  • Wow, that is about as simple as it gets.. And since you already checked for default, that is really odd.

    CEWII

  • A trick you can try is as below.

    Start a trace in the background while sending test mail through the GUI, it will allow u to see the command that is being executed.

    You can then copy and paste the same thing and try sending the mail using sp_send_mail. If it doesn't work, have a look at the error in the mail event log and post it here.

    Regards

    Akhil

  • the 1st parameter for sp_send_dbmail is profile name,

    so try this instead:

    exec msdb.dbo.sp_send_dbmail @recipients='dweil@jewishhome.org', @body = 'Let keep it simple'

    u can see its syntax here:

    use msdb

    go

    exec sp_helptext sp_send_dbmail

  • Well, maybe you can have a look at my blog :

    http://www.incrediblestuff1.blogspot.com

    I have given a detailed description about db mail.Maybe that should be giving you a clue.

    You might as well ensure the following are present :

    1)ensure the profile has been set with the public access or atleast you should be having access.

    run : exec msdb.dbo.sysmail_help_principalaccount_sp.

    2)ensure the db mail has started

    run : exec msdb.dbo.sysmail_start_sp

    Hope that helps.

    In case of further issue or error,post the error message here.

  • Thank you asdfgh-617842

    exec msdb.dbo.sp_send_dbmail @recipients='dweil@jewishhome.org', @body = 'Let keep it simple'

    worked like a charm.

    My mistake was using syntax that had always worked for xp_sendmail which is SIMILAR to exec msdb.dbo.sp_send_dbmail, bu not exactly the same. It thought that my email address was my profile.

  • Hi, try the solution mentioned in this blog post:

    https://cms4j.wordpress.com/2013/12/17/msg-14607-level-16-state-1-profile-name-is-not-valid/

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply