Profile Name is Not Valid SQLSTATE 42000 Error 14607 | A Complete Walkthrough

The “profile name is not valid SQLSTATE 42000 error 14607” message often indicates an issue with specifying the correct profile for sending emails. In this article, we’ll guide you through resolving this error step by step, ensuring smooth email delivery from your SQL Server. So, let’s get started and have the system up and running.

How to Fix the Profile Name is Not Valid Error

Here, we present two possible scenarios where the SQLSTATE 42000 Error 14607 might occur, along with how to solve it. 

SQLSTATE 42000 Error 14607: When Sending Mail from SQL Server

To send email using Database Mail, you must specify a profile rather than a user account directly. Otherwise, the “profile name is not valid” error will occur.

EXEC msdb.dbo.sp_send_dbmail  

    @profile_name = ‘AdminMailProfile_New’,  

    @recipients = ‘[email protected]’,  

    @body = ‘The SQL Server Agent job has encountered an issue and failed to execute successfully. Please review.’,  

    @subject = ‘SQL Server Agent Job Failure Notification’;

In the given example, the @profile_name ‘AdminMailProfile_New’ was used, but such a profile does not exist.

Before proceeding with sending mail, follow these steps:

1. Switch to the msdb database.
2. Create a user in the msdb database.
3. Create a Database Mail account.
4. Create a Database Mail profile.
5. Add the Database Mail account to the profile.
6. Grant the user access to the Database Mail profile.

Here’s how you can accomplish these tasks:

— Switch to the msdb database

USE msdb;

— Create a user on the msdb database

CREATE USER MailUser FOR LOGIN MailUser;

— Create a Database Mail account  

EXECUTE msdb.dbo.sysmail_add_account_sp  

    @account_name = ‘AdminMailAccount_New’,  

    @description = ‘Primary email account for administrative notifications.’,  

    @email_address = ‘[email protected]’,  

    @replyto_address = ‘[email protected]’,  

    @display_name = ‘SQL Mailer’,  

    @mailserver_name = ‘mail.example.com’,

    @port = 587;  

— Create a Database Mail profile  

EXECUTE msdb.dbo.sysmail_add_profile_sp  

    @profile_name = ‘AdminMailProfile_New’,  

    @description = ‘Profile for sending administrative emails.’;  

— Add the account to the profile  

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  

    @profile_name = ‘AdminMailProfile_New’,  

    @account_name = ‘AdminMailAccount_New’,  

    @sequence_number = 1;

— Grant user access to the Database Mail profile

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = ‘AdminMailProfile_New’,

    @principal_name = ‘MailUser’,

    @is_default = 1;

Don’t forget to replace “MailUser” with the appropriate username, and ensure that your specified mail server details are accurate. These steps should resolve the “profile name is not valid” error when sending mail from SQL Server.

SQLSTATE 42000 Error 14607: When Updating a Database Mail Profile

Encountering an “invalid profile name” error while attempting to update a Database Mail profile in SQL Server often indicates a missing profile ID specification.

When utilizing the sysmail_update_profile_sp stored procedure to modify a Database Mail profile, it’s essential to include the profile ID if you aim to change the profile name.

Consider this scenario: You’re tasked with updating a Database Mail profile named “Admin Profile” to “New Admin Profile.” Initially, you query to retrieve the existing profiles:

EXEC msdb.dbo.sysmail_help_profile_sp;

The query result displays:

+————–+——————+—————————+

| profile_id   | name             | description               |

|————–+——————+—————————|

| 1            | Admin Profile    | Profile for admin emails. |

+————–+——————+—————————+

Now, let’s attempt to update the profile name without specifying the profile ID:

EXECUTE msdb.dbo.sysmail_update_profile_sp

    @profile_name = ‘New Admin Profile’,

    @description = ‘Updated profile for admin emails.’;

Unfortunately, this results in the “profile name is not valid” error message.

Msg 14607, Level 16, State 1, Procedure msdb.dbo.sysmail_verify_profile_sp, Line 42

profile name is not valid

Make sure to include the profile ID in the update operation.

EXECUTE msdb.dbo.sysmail_update_profile_sp

    @profile_id = 1,

    @profile_name = ‘New Admin Profile’,

    @description = ‘Updated profile for admin emails.’;

Executing this command successfully updates the profile name. Upon rechecking the profiles, you’ll observe the modification:

EXEC msdb.dbo.sysmail_help_profile_sp;

Resulting in:

+————–+———————-+———————————–+

| profile_id   | name                 | description                       |

|————–+———————-+———————————–|

| 1            | New Admin Profile    | Updated profile for admin emails. |

+————–+———————-+———————————–+

Remember, the profile ID is mandatory only when updating the profile name. If you’re altering the description alone, you can omit the profile ID, provided the correct profile name is supplied.

Frequently Asked Questions

What is the query to check database mail profile in SQL server?

You can use the following query to check the Database Mail profiles:

SELECT * FROM msdb.dbo.sysmail_profile;

Can non-sysadmin users send emails using the msdb.dbo.sp_send_dbmail stored procedure?

Yes, starting from SQL Server 2012, non-sysadmin users can send emails using the msdb.dbo.sp_send_dbmail stored procedure, provided they have the necessary permissions and the Database Mail profile is configured correctly.

Conclusion

By following the steps outlined in this article, you can effectively configure the Database Mail profile and empower non-sysadmin users to resolve the “profile name is not valid” error and send emails seamlessly. Should you encounter any further challenges or have additional questions, don’t hesitate to seek assistance. Thank you for reading!