Set global profile for mailing in Microsoft SQL Server 2008

You can get an error message like the following:

Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.

Yes. You forgot to set up default profile in Database Mail Configuration Wizard just like me. :) There is no simple way to correct this.

I checked sysmail_ stored procedures family on subject who is able to handle this situation. The most relevant one is sysmail_update_principalprofile_sp, but I dropped the idea to use it after the first failed attempt to do. I checked sysmail_ tables set instead. And I quickly found [msdb].[dbo].[sysmail_principalprofile] with is_default field in it.

This is T-SQL to set default mailing profile:

update [msdb].[dbo].[sysmail_principalprofile]
set is_default = 1
where profile_id =
      (select max(profile_id)
      from [msdb].[dbo].[sysmail_profile]
      where [name] = 'def_profile'
      )

From now annoying offer to specify a profile name in the @profile_name parameter disappeared.