SQL2005 "Profile name is not valid" when trying to send email

[chpicker]

I configured a SQL Server to send email on Friday. It was working just fine. The "Send test email..." selection in Management Studio worked, as did this:

exec msdb.dbo.sp_send_dbmail 'user@contoso.com','Test body','Test subject','TestProfile'

As of today, the above code no longer works. It results in the following error:

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

The "Send test email..." function still works properly.

My research into the error has turned up nothing that works. On Microsoft's own web site, they claim that there are only 2 possible causes of this message: either the profile doesn't actually exist, or the user doesn't have permission to use it. As far as I can tell, neither of these is correct; I've verified that the profile still exists, and I'm connected as a sysadmin on the database instance, which is supposed to have permission to all profiles.

Any ideas? I've been beating my head against this wall all day.

 

[pandatime]

I would recreate the profile and try again.

 

[chpicker]

Ugh...turns out I messed up the parameters. The code I posted above was from memory, not verbatim. The parameters are in the wrong order. I added their names to the code and now it works again.

 

[SQLBill]

Here's a best practise suggestion when using parameters.

If you include the parameter name, then it doesn't matter what order is used to input the parameter values. For example:

exec msdb.dbo.sp_send_dbmail @recipients = 'user@contoso.com', @body = 'Test body', @subject = 'Test subject', @profile = 'TestProfile'

Doing that anytime you use parameters, not just with mail, will protect your scripts from failing if the 'wrong' order is used.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[chpicker]

Thanks for the advice. I'm new to SQL stored procedures; I'm used to programming in C and BASIC. It never occurred to me that the order of parameters wouldn't matter!