Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why am I getting the following error when using database mail -> Procedure sysmail_verify_profile_sp, profile name is not valid?

I wrote a trigger to grab a certain row of records after a specific column change and store the records into another table called Feedback.

Then I am trying to using the following code to email the changes to our users using sp_send_dbmail.

However, when testing the code, I keep getting the following error messages:

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

The profile is called Feedback Survey and it is set up correctly using Database Mail Configuration wizard.

What could I be doing wrong?

 Declare @email nvarchar(MAX),@content1 nvarchar(4000), @RequestID INT, @custname nvarchar(200)
  select @email = '', @content1 = '', @RequestID = 0, @custname = '' 
 
                   SET @content1 = 'SET QUOTED_IDENTIFIER OFF;
                    This is a computer generated email message.
            Please DO NOT use the REPLY button above to respond to this email.
            
            Dear '+ @custname +':
            
            Thank you for using the order processing system.
            
                Please click the link below to complete a survey
            
            http://satisfactionsurvey.php?wo=@RequestID
            
            Regards, 
           Order administrator. '


     SELECT top 1 @email = @email+';'+Email, @content1 = @content1
        FROM Feedback
    WHERE Status = 'Completed'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Feedback Survey', -- our defined email profile or static info
@recipients = @email, -- Authorized user email
@blind_copy_recipients = '[email protected]',
@subject = 'Feedback Survey',
@body = @content1;

--delete records after sending email.
Delete FROM Feedback
like image 779
Chidi Okeh Avatar asked Aug 10 '15 14:08

Chidi Okeh


1 Answers

In the Database Mail Configuration Wizard, go to Manage Profile Security and check the settings for public and private profiles.

Check if the Login that you use is connected to the profile 'Feedback Survey'.

More detailed information can be found in Msg 14607, Level 16, State 1 – profile name is not valid (Internet Archive link from 2014-01-15).

like image 149
CMS Avatar answered Oct 03 '22 07:10

CMS