Mail without SQL Agent?
While SQL Server Express is still 100% able to send mail using msdb’s stored procedures,
most people rely on Management Studio’s GUI and the Database Mail feature to do the initial setup of profiles and accounts
to make sending mail possible. Despite this limitation, the underlying stored procedures located within the msdb database are still fully able to replication the GUI driven setup that Database Mail aids in. This leaves T-SQL as the primary alternative for enabling and configuring SQL Server Express so that
sp_send_dbmail can be used to send mail out. Adding this functionality with a replacement for SQL Agent can help mimic much of the functionality that Database Mail and SQL Agent provide on the full featured editions of SQL Server.
While a CLR can also be used to send mail, that route is far more work and increases the complexity of sending mail via SQL Server quite a bit. This requires enabling CLRs, which is disabled by default. Enabling CLRs opens the instance up to external code, which may be a security or audit concern and is often allowed in many corporate environments.
Setting up Mail on SQL Server Express
The below code sample demonstrates the entire process of setting up mail on SQL Server Express, including sending a test email at the end.
The setup will:
- Create a mail account
- Create a mail profile
- Add the account to the profile
- Add permission to use the profile for a user or role
- Enable database mail XPs
- Send a test email
The stored procedures required are: