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

Question

Post reply

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

dweil

SSCrazy

Points: 2408
More actions
February 4, 2010 at 8:11 am

#137817

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

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

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

CEWII

dweil SSCrazy Points: 2408 More actions · Answer 2

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

What is the exact command you are trying to execute?

CEWII

dweil SSCrazy Points: 2408 More actions · Answer 4

simply

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

thanks

David Weil

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

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

CEWII

SQLbee Ten Centuries Points: 1009 More actions · Answer 6

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

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 7

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

black.clouds.999 Mr or Mrs. 500 Points: 501 More actions · Answer 8

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.

dweil SSCrazy Points: 2408 More actions · Answer 9

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.

cms4j SSC Enthusiast Points: 193 More actions · Answer 10

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/