Sql-server – Read receipts for emails sent via SQL Server Database Mail

One option is to "proof" your code against collation mismatch.

You can use the special collation "DATABASE_DEFAULT" to coerce without knowing what the actual collation is. You use it on char type columns in temp tables, table variables and system tables you need to use.

Example:

CREATE TABLE #Currency (CCY CHAR(3))
GO
INSERT #Currency VALUES ('GBP')
INSERT #Currency VALUES ('CHF')
INSERT #Currency VALUES ('EUR')
GO
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY --error!
GO
-- in join too
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY COLLATE DATABASE_DEFAULT --no error
GO
DROP TABLE #Currency
GO


CREATE TABLE  #Currency (CCY CHAR(3) COLLATE DATABASE_DEFAULT)
GO
INSERT #Currency VALUES ('GBP')
INSERT #Currency VALUES ('CHF')
INSERT #Currency VALUES ('EUR')
GO
SELECT Something
FROM myTable M JOIN #Currency C ON M.CCY = C.CCY --no error!
GO

DROP TABLE #Currency
GO

This also means that when your clients migrate their DB onto a new whizzy SQL Server box with yet another different collation, it works too...

Taken from Books Online: Database Mail

To minimize the impact on SQL Server, the component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.

Database Mail use Service Broker technology:

Database Mail provides background, or asynchronous, delivery. When you call sp_send_dbmail to send a message, Database Mail adds a request to a Service Broker queue. The stored procedure returns immediately. The external e-mail component receives the request and delivers the e-mail.

The actual email delivery is handled by your SMTP server and so this will bear the brunt of the workload and should be provisioned according to capacity/email traffic requirements.

See Planning for Database Mail