Home > Uncategorized > Send email from #SQL server using a #CLR function

Send email from #SQL server using a #CLR function

Send Email SQL CLR

Send Email from a SQL CLR function

Although you can, and should use sp_send_dbmail to send email from SQL server, it’s often not quite as flexible as you need it to be. So here is a .NET CLR Function that you can install in your MSSQL server, in order to send an email using whatever additional configuration that you need.

You need to run these commands before installing the assembly

EXEC sp_changedbowner 'sa'
ALTER DATABASE <your-database> SET trustworthy ON


CREATE ASSEMBLY [SendEmailCLR]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF000.....
    WITH PERMISSION_SET = UNSAFE;
  
CREATE PROCEDURE [dbo].[SendEmail]
@smtpServer NVARCHAR (MAX) NULL,
@smtpUsername NVARCHAR (MAX) NULL, 
@smtpPassword NVARCHAR (MAX) NULL, 
@from NVARCHAR (MAX) NULL, 
@to NVARCHAR (MAX) NULL, 
@subject NVARCHAR (MAX) NULL, 
@body NVARCHAR (MAX) NULL
AS EXTERNAL NAME [SendEmailCLR].[SendEmailCLR].[SendEmail]

The full binary string is redacted here to save space, but you can get this from https://github.com/infiniteloopltd/SendEmailSQLCLR/blob/master/bin/Debug/SendEmailCLR_4.publish.sql

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: