Wednesday, October 17, 2012

Troubleshooting ‘profile name is not valid’ error

I got this message when I tried to call the msdb.dbo.sp_send_dbmail stored procedure. I had setup my profile (let’s call it Profile1). I even gave my user (let’s call it User1) the permissions to send mail as described here. I then called it from some C# code and got the following message.

profile name is not valid

Here are the steps I used to figure out what my problem was.

  1. Double check that the name of the profile is correct and is being passed correctly to the stored proc using the @profile_name parameter.
  2. The next step is to figure out if it is my code or SQL server side. The easiest way to do that is to open SQL Management Studio and connect to your database server using the exact user/login you were using when you got the error. Then try to send the mail message using the following (tweak as necessary):

        @profile_name = 'Profile1',
        @recipients = '',
        @subject = 'Test subject',
        @body = '<p>Test Body</p>',
        @body_format = 'HTML'

    If you still get the error, it is something to do with SQL Server, otherwise, it is somewhere in your code that is called the stored proc. If it is in your code, you are on your own. If it is on the server (still getting the error) then proceed to the next step.

  3. In SQL Management Studio, open another connection, but this time connect as a user that has more rights (probably yourself). Now execute the following stored proc.

    msdb.dbo.sysmail_help_principalprofile_sp @principal_name = 'User1', @profile_name = 'Profile1'

    If you get no results back then that means you have a permission problem. This likely means that your profile is NOT public or the user (User1) don’t have access to it. You an change the permission by going to the Database Mail Configuration Wizard | Manage profile security radio button | Next button. Here you can click the profile to make it public. You can alternatively go to the Private Profiles tab and select the user and then the profile. Either way should work.

    If you run the stored procedure again, it should show that you now have access to the profile now. You can also re-run the sp_send_dbmail and it should work this time.

No comments: