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
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With