send mail SQL Server with Dynamic smtp

Question

That's basically it.

i've configured the profile, mail, smtp server, username and password. I'm using sp_send_dbmail but occasionally I'm going to change the smtp, username, password and port, I have that in a table and I just need to the pass parameters to the "sp_send_dbmail" with those fields, or something that works like that.

SQL Send mail with dynamically email credentials.

Thanks a lot

Answer 1

If anyone needs it, First I delete the profile settings (Or account in this case) and then I'll create it again, using this...

EXECUTE msdb.dbo.sysmail_delete_account_sp
EXECUTE msdb.dbo.sysmail_delete_profile_sp

EXECUTE msdb.dbo.sysmail_add_account_sp
EXECUTE msdb.dbo.sysmail_add_profile_sp
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

That's how i manage to "Update" Accounts credentials before sending the Mails

Answer 2

You will have to store all the profile names that you have configured in your table.

declare @profilename varchar(50);

set @profilename = (select profilename from your_table where smpt_server = 'smtp_server_value');

EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]'
, @subject = 'Subject'
, @body = 'email_test'
, @profile_name = @profilename
, @body_format = 'HTML' ;